Import One Worksheet from Multiple Excel Files into Multiple Access tables

Question

I have about 200 Excel files that I would like to import into a single Access database and have a table for each file. Each Excel file has multiple worksheets, but the one that I would like to import is consistently named.

I have found some code for this, see: http://www.accessmvp.com/KDSnell/EXCEL_Import.htm#ImpBrsFldFiles, http://social.msdn.microsoft.com/Forums/en-US/dfea25ab-cd49-495c-8096-e3a7a1484f65/importing-multiple-excel-files-with-different-file-name-into-access-using-vba

Here is one of the pieces of code which I tried:

Option Compare Database

Sub ImportFromExcel()

End Sub

Dim strPathFile As String, strFile As String, strPath As String
Dim strTable As String, strBrowseMsg As String
Dim blnHasFieldNames As Boolean
    ' Change this next line to True if the first row in EXCEL worksheet
    ' has field names
blnHasFieldNames = True

strBrowseMsg = "C:\Users\fratep\Desktop\Long-term EWM Study Data Files\"
strPath = BrowseFolder(strBrowseMsg)
If strPath = "" Then
   MsgBox "No folder was selected.", vbOK, "No Selection"
   Exit Sub
End If

   ' Replace tablename with the real name of the table into which
   ' the data are to be imported
strTable = "tablename"

strFile = Dir(strPath & "\*.xls")
  Do While Len(strFile) > 0
    strPathFile = strPath & "\" & strFile
    DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, _
        strTable, strPathFile, blnHasFieldNames

 ' Uncomment out the next code step if you want to delete the
 ' EXCEL file after it's been imported
 '       Kill strPathFile

   strFile = Dir()
 Loop



Sub ImportMultiExcels()

End Sub

from the 1st link above, but I can't seem to get them to do what I am looking for. Can anyone help me?

I am new to VBA, so am a little uncertain about editing the code.

What is the specific problem? Can you import a single sheet into Access but not extend that to 200 sheets? Or what? — HansUp, Nov 04 '13 at 18:05
Hi Hans, Yes, as far as I can tell the Import Wizard only does one sheet at a time. Like I said I've found VBA scripts to perform the function, but for some reason I can't get them to work. — logicForPresident, Nov 04 '13 at 18:28
OK then show us your VBA which successfully imports a single sheet and describe the problems you're facing when trying to extend that to 200 sheets. — HansUp, Nov 04 '13 at 18:30
Also, I should add that I'm pretty new to VBA, so I do have a bit of trouble reading and customizing it to my situation. I did change the path names in the files, so that's not it. — logicForPresident, Nov 04 '13 at 18:31
Sorry, not using VBA to import single sheet, I'm using the Import Wizard. Is that my problem? — logicForPresident, Nov 04 '13 at 18:31
I'm unsure, Paul. New to VBA is not a deal-killer. But we need to see where you're having problems so we can figure out how to help. Without seeing any code, I have no idea where to start. :-( — HansUp, Nov 04 '13 at 18:33

HansUp · Answer 1 · 2013-11-04T19:59:14.487

1

It seems you're able to use the Import Wizard to successfully import a worksheet into Access. In that case, you should be able to use the DoCmd.TransferSpreadsheet Method to do the same thing from VBA code in your Access database.

The procedure below imports a single sheet named XYZ Priority as an Access table named Import1.

I used a constant for the sheet name because you said the target sheet has the same name in all the source workbook files.

I constructed the table name as "Import" plus i. When you extend this to multiple workbooks, you can increment i after each import. Or perhaps you have a different strategy for the table names; you didn't say.

I split the TransferSpreadsheet statement across several lines, and included the option names to (hopefully) make it easier to understand.

My worksheet includes column names, so I have HasFieldNames:=True

And my workbook was created with an older version of Excel. SpreadsheetType:=acSpreadsheetTypeExcel9 works with this; you may need a different value for SpreadsheetType

Public Sub Demo_TransferSpreadsheet()
    Const cstrSheetName As String = "XYZ Priority"
    Dim i As Long
    Dim strFileName As String
    Dim strTableName As String

    ' my workbook is located in the same folder as the Access db file
    strFileName = CurrentProject.Path & Chr(92) & "temp.xls"
    i = 1
    strTableName = "Import" & CStr(i)

    DoCmd.TransferSpreadsheet _
        TransferType:=acImport, _
        SpreadsheetType:=acSpreadsheetTypeExcel9, _
        Tablename:=strTableName, _
        FileName:=strFileName, _
        HasFieldNames:=True, _
        range:=cstrSheetName & "$"
End Sub

edited Nov 04 '13 at 19:59

answered Nov 04 '13 at 19:24

HansUp

95,961
11
77
135

So how can I modify this code to do this for every excel file in a specified folder? – logicForPresident Nov 04 '13 at 19:27
Please be patient with me. How do I specify the path leading to the CurrentProject? Sorry for such a dumb question, like I said, I'm a brand newb. – logicForPresident Nov 04 '13 at 19:44
My workbook is located in the same folder as the Access db file which holds that VBA procedure. So I used `CurrentProject.Path & Chr(92)` as a substitute for the actual folder path: C:\share\Access\ In your version of the code, use whatever method you're comfortable with to specify the folder piece. – HansUp Nov 04 '13 at 19:48
Okay, sorry again, when I try to run the code it tells me that the Macros in this project are disabled. I tried to enable them and I keep getting the error message. I also closed and reopened the package and still the same thing happens. Any thoughts? – logicForPresident Nov 04 '13 at 19:59
I just updated the answer ... *"VBA code in your Access database"*. Are you trying to use VBA from an Excel workbook instead? – HansUp Nov 04 '13 at 20:01
let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/40530/discussion-between-paul-and-hansup) – logicForPresident Nov 04 '13 at 20:08

Import One Worksheet from Multiple Excel Files into Multiple Access tables

1 Answers1