Import mulitiple excel files to access table

[butler808]

Hello,

I am struggling to import multiple spreadsheets into an Access table. I have "borrowed" the code from the 'net and tailored it slightly. I am using Access 2013. Code is as follows:

Function Impo_allExcel()
Dim ImportDir As String, ImportFile As String
ImportDir = "\\-server\WarehouseKPIs\DB_Linked_Forms_Folder\"
ImportFile = Dir(ImportDir & "*")
Do While ImportFile <> ""
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel18, Tbl1_Warehouse_Form_Archive, ImportDir & ImportFile, , Form
ImportFile = Dir
Loop
End Function
 

Any help would be much appreciated.

Thanks.

[zmbd]

1) You're making us guess here:
1a) Do you have multiple excel workbooks where-in there is at least one worksheet that you would like to import the data
1b) You have a single excel workbook where-in there are multiple worksheets
1c) a combination of 1a and 1b

2) Not Sure what you are doing in line 6
2a) what is that "form" doing at the end of the statement

DoCmd.TransferSpreadsheet _
    TransferType:=acImport, _
    spreadsheettype:=acSpreadsheetTypeExcel8, _
    tablename:=Tbl1_Warehouse_Form_Archive, _
    fileName:=ImportDir & ImportFile

2b) are you sure that you are using such an old version of the worksheets? acSpreadsheetTypeExcel8= excel97

3) By, simply stating that your code "doesn't work," posting code that appears to have had very little if any troubleshooting performed, and expecting someone to help doesn't usually result in much of an answer and may result in your thread being deleted. Instead, please tell us what you were expecting to happen and what actually happened.

For each error, if any: the EXACT title, error number, and descriptions that occurred and at what line in your posted code the error occurred.

These are the minimum requirements for posting a question of this nature.

[butler808]

Hi thanks for your response.

To clarify:
1) I have multiple (50+) excel files in a folder (containing subfolders), that I would like to import in one hit to Access. The excel files all have the file extension .xls. I would like to import the "Form" range from these spreadsheets into the Tbl1_Warehouse_Form_Archive table.
2. Form refers to the Excel range, I have corrected the Excel file type to 18.
3. Issues - the code is falling down at the following points:

[ImportDir = "\\server\insert_full_path_here\"]
 
[ImportFile = Dir(ImportDir & "*")]
 
[Do While ImportFile <> ""]

I can see that the ImportFile part may not work due to referring to the ImportDir, which isn't working. The same is true for the "Do While ImportFile" line, which is dependent on the ImportFile segment of the code.

I am relatively new to VBA, and have had success with several bits of code. I have "stepped-into" this code, and couldn't see what was wrong, hence my post here. I will describe my issue in more depth should I post here again.

Thanks again.

[jimatqsi]

I can't say anything about version 2013, but in 2007 and earlier I don't believe you can add that "*" do the Dir() folder. Just

ImportFile=Dir(path)
do while ImportFile <> ""
   (do stuff - including test the file type to be sure it's what you want )
    ImportFile=Dir()  ' to get the next file in the list
Loop
 

Jim

[zmbd]

Hi thanks for your response.
1) I have multiple (50+) excel files in a folder (containing subfolders), that I would like to import in one hit to Access.

That's a very tall order.
You can use the DIR() method to handle the main directory path fairly easily; however, the subfolders will be much more complicated and your code, as is, will not handle that scenario; however, let's just try to handle the main directory files first.

I would like to import the "Form" range from these spreadsheets into the Tbl1_Warehouse_Form_Archive table.

2. Form refers to the Excel range,

Form is a reserved token/name
Access 2007 reserved words and symbols
AllenBrowne- Problem names and reserved words in Access

Second the range name needs to be in quotes i.e:"NameOfImportRange"

Third I advise that very new programmers use the named parameters when first creating their code as I've shown in my last post in section (Post#2 - Sec2A). This way you do not need to worry about the comma placement.

I have corrected the Excel file type to 18. (The excel files all have the file extension .xls.)

Type 18?
If you mean "acSpreadsheetTypeExcel18" that is, TBK, an invalid type.
We need to know what version of Excel was used to create the file. With "xls" either acSpreadsheetTypeExcel8 or acSpreadsheetTypeExcel9 are the most likely types to use.

3. Issues - the code is falling down at the following points:

Yea 20Q... For each error, if any: the EXACT title, error number, and descriptions that occurred and at what line in your posted code the error occurred.

Please follow the advice and basic troubleshooting here:[*]> Before Posting (VBA or SQL) Code you will need to repeat, fixing errors as found, the compile step until no errors occur.

[butler808]

Hi, I have solved this using alternative code, I tailored it to search all of the key sub-folders. The reference to the named range "Form" worked fine, but will avoid using reserved words in future.

Thanks.

[zmbd]

most welcome.
Would you mind sharing your solution?
It can be quite helpfull to others with similuar issue, and sometimes we can help streamline the code or help to avoid a potential bottle-neck.

[butler808]

Public Function Impo_allExcel()
Dim myfile
Dim mypath
 
mypath = "\\server\insert_the_full_path_here\subfolder_1"
ChDir (mypath)
myfile = Dir(mypath)
Do While myfile <> ""
  If myfile Like "*.xls" Then
    DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel8, "Demo_Table", mypath & myfile, , "Form"
  End If
  myfile = Dir()
Loop
 
mypath = "\\server\insert_full_path_here\Weekend Shift\"
ChDir (mypath)
myfile = Dir(mypath)
Do While myfile <> ""
  If myfile Like "*.xls" Then
    DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel8, "Demo_Table", mypath & myfile, , "Form"
  End If
  myfile = Dir()
Loop
 
End Function

So I have trimmed this code down to access two folders, I have twelve folders total. It searches/imports the contents of one folder, loops until completed and moves onto the second folder. The code then ends. These folders contain submitted Excel forms for the current month - when the month is complete, I then archive these into a dedicated Month subfolder. Then the folder referred to in the code is repopulated automatically with the newly submitted sheets for the new month. The "Form" part of the DoCmd.TransferSpreadsheet acImport statement is the named range in my excel sheets, if anyone else comes to use this code.

Thanks.