Solved Importing Multiple Excel Files

[Drand]

Hi

I need to import over 20 excel files into one table in a new project.

I have found the following code to do this:

Public Function Impo_allExcel()

Dim myfile
Dim mypath


mypath = "C:\KPMG\Country_Data"
ChDir (mypath)

myfile = Dir()
Do While myfile <> ""
  If myfile Like "*.xlsx" Then
     'this will import ALL the excel files
     '(one at a time, but automatically) in this folder.
     ' Make sure that's what you want.
    DoCmd.TransferSpreadsheet acImport, 8, "tblConsolRawData", mypath & myfile
  End If
  myfile = Dir()
Wend

End Function

The code is producing a "wend without while error".

Could someone please have a look at this and let me know what is wrong.

Many thanks

 

[bob fitz]

Replace "Wend" with "Loop" and it will compile.

 

[Drand]

Thanks for that.

Now I am getting an "invalid procedure call or argument error" at

myfile = Dir()

Thanks

 

[ebs17]

Dim myfile AS String
Dim mypath As String

mypath = "C:\KPMG\Country_Data\"
myfile = Dir(mypath & "*.xlsx")
Do While myfile > vbNullstring
    ' Debug.Print myfile
    DoCmd.TransferSpreadsheet acImport, 10, "tblConsolRawData", mypath & myfile      ' acSpreadsheetTypeExcel12Xml
    myfile = Dir
Loop

SpreadsheetType = 8 refers to Excel 97. This does not match XLSX.

 

[Drand]

Thanks for this. Much appreciated.

When I run the code, I encounter the error "Field 'F 1' does not exist in destination table "tblConsolRawData".

There are 19 fields in each of the spreadsheets and my field names in my table correspond to these.

I have tried a couple of things to test this.

If I rename the fields in my table to F1, F2............F19 then the import works fine but this is not really practical.

If I import one file into a new table using the wizard and click "First Row Contains Column Headings", this performs perfectly.

Would appreciate any advice on this.

Thank you

 

[Gasman]

Do all the other sheets contain headings?
Walk through your code, line by line with a breakpoint on the docmd line

 

[Drand]

Yes, all sheets contain the same headings. The code runs to the breakpoint.

 

[Gasman]

Yes, all sheets contain the same headings. The code runs to the breakpoint.

So what file does it complain about?

 

[Drand]

The first file in the directory, Argentina Data.xlsx

 

[CJ_London]

Not at my computer to check but you are not specifying headers in transfer spreadsheet - think it defaults to false

 

[ebs17]

DoCmd.TransferSpreadsheet(TransferType, SpreadsheetType, TableName, FileName, HasFieldNames, Range, UseOA)

Use the HasFieldNames argument => True
F1, F2, ... are substitute names for the columns if you don't use the existing field names.

 

[Gasman]

So, looking at the syntax, why are you not specifying you have fieldnames ?

DoCmd.TransferSpreadsheet method (Access)

Office VBA reference topic

learn.microsoft.com

 

[Drand]

Thank you all. When I included the has field names as true it worked perfectly.

Really appreciate the assistance from everyone!

 

[Gasman]

Look up the actual syntax next time.
You cannot just make stuff up with computers. They are very picky.

 

[Drand]

Sorry. Still have the learner plates on!

 

[Gasman]

Sorry. Still have the learner plates on!

All the more reason to lookup the syntax.

 

[Lightwave]

Late to the party but for anyone else looking to do this (in this case with CSV files) I have similar alternate code which I worked out one time

MS Access function to import all files from a directory into a single table