How to combine several excel files into one access table?

[Dawn]

Dear all,
The directory for saving files is determined while the number of files
in the directory every month is not certain, and the format of each file is
the same, the difference lies in the file names and the records in each files.
I want to write a Sub to read all records in each file in the directory
into an access table. How to achieve this? Thx for your support in advance.

 

[Tom van Stiphout]

On Sun, 21 Mar 2010 20:13:01 -0700, Dawn

You can iterate over the files in a folder using the Dir function.
Then I would attach the file, run an append query to copy the data
from Excel to Access, remove the attachment, and loop.

-Tom.
Microsoft Access MVP

 

[Dawn]

Thx,Tom,it's a new way out of my box. But since I'm a green hand, can you put
out some coding for reference? Very thx

 

[pietlinden]

Thx,Tom,it's a new way out of my box. But since I'm a green hand, can youput
out some coding for reference? Very thx

Private Sub cmdPopulateCombo_Click()
Dim strDirectory As String
Dim strFile As String

' Requires the BrowseFolder API from Access web... Here:
strDirectory = BrowseFolder("Which folder contains the files you
want?")
strFile = Dir(strDirectory & "\*.XLS")

Do While strFile <> ""
'NOTE: xlsEmpInfo is the name of my linked Excel spreadsheet

'---Set link to the strFile (the filename returned by the
Dir() function
currentdb.TableDefs("xlsEmpInfo").Connect = "Excel
5.0;HDR=YES;IMEX=2;DATABASE=" & strFile

'---run the append query to write the contents of the excel
file to your table. (build the query first... manually attach an excel
file)
CurrentDb.Execute "qappMyAppendQuery", dbFailOnError

'--- get the next file
strFile = Dir
Loop

End Sub


You will need the top two API calls, most likely... at least the
BrowseFolder API (so you can choose the folder the Excel files are in)


Private Sub ImportXLFiles()
Dim strDirectory As String
Dim strFile As String
Dim i As Integer

strDirectory = BrowseFolder("Which folder contains the files you
want?")
strFile = Dir(strDirectory & "\*.XLS")

Do While strFile <> ""
DBEngine(0)(0).TableDefs("MyAttachedExcelFile").Connect =
"Excel 5.0;HDR=YES;IMEX=2;DATABASE=" & strFile
Loop
End Sub

This code is probably buggy as all get out... I haven't tested it...
Will fix it further later today if you need it.

Pieter

 

[Dawn]

Pieter，Tom，
Thany you very much for your selfless help. I 'll try first, may come
here again when encounter problems. thx.