Importing From Multiple Excel Files Problem

[shaka]

Hi Folks

I am trying to import data from multiple Excel files saved in the one folder to a single table in Access.

I found this macro on the web and I think I am nearly there but when I run the macro it returns a fault in the code here

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, strTable, strPathFile, blnHasFieldNames

full code I am using

Option Compare Database
Option Explicit

Public Function ImportExcelMultipleFiles()

Dim strPathFile As String, strFile As String, strPath As String
Dim strTable As String
Dim blnHasFieldNames As Boolean

' Change this next line to True if the first row in EXCEL worksheet
' has Field names
blnHasFieldNames = False

' Replace C:\Documents\ with the real path to the folder that
' contains the EXCEL files
strPath = "C:\Users\Kevin\Google Drive\ATOS\ATOS Reports\"

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

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

End Function

[alansidman]

What type of Excel file are your trying to import. Your VBA code indicates that the file type is Excel 97-2003 with an .xls extension. Is this correct?

[shaka]

Yes still using 2003 at the moment till we get the long awaited upgrade in work

[alansidman]

Also, you have indicated that you have no field names (column headers) in your excel file. Is this also true? Can't see anything else in this line of code. What error message do you get?

Just had a thought, delete this section of the code. acSpreadsheetTypeExcel9

[shaka]

I do have headers and have now changed that to True

Deleted that section but when I run the macro it still pops up my code with the following line highlighted in yellow

DoCmd.TransferSpreadsheet acImport, strTable, strPathFile, blnHasFieldNames

[alansidman]

Change that line of code to

DoCmd.Transferspreadsheet acImport, , strTable, strPathFile, blnHasFieldNames

Where you had the File type, you need for the code to know that it is a wildcard, by leaving it blank and not ignoring it completely. With what you did, VBA thought the file type was the next string (strTable) and therefore bombed. If you don't use an option section of the code, you must supply the comma so that it is instructed to move to the next item.

Here is a link to DoCmd.Transferspreadsheet syntax.

http://msdn.microsoft.com/en-us/libr...ffice.12).aspx

Alan

[shaka]

All working now thank you Alan for taking the time to help with this

[alansidman]

Glad to have helped.

Alan