Hi Martin
The following code uses append queries to import spreadsheets to a table
NOTE:
1. All spreadsheets must be exactly the same structure in order for this to
work
2. There is no error checking
*** Code starts ****
Public Sub ImportTest()
Dim db As DAO.Database
Dim strInputDir, strImportFile As String, strTableName As String
Dim strFileExt As String
Dim strSQL As String
' the pathname of the folder that contains the files you want to import."
strInputDir = "C:\PathToFiles"
' the file extension for the type of file you want to import.
strFileExt = ".xls"
' the destination table in the database
strTableName = "xlsTableName"
strImportFile = Dir(strInputDir & "\*" & strFileExt)
Set db = CurrentDb
Do While Len(strImportFile) > 0
strSQL = "INSERT INTO " & strTableName & " SELECT * FROM [Query$] IN '"
_
& strImportFile & " '[Excel 5.0;];"
db.Execute strSQL
strImportFile = Dir
Loop
End Sub
*** Code ends ****
cheers
Garth
Thanks chas!
you where helpfull since resding articule (and trying
it ) I learn few new trics, but it doesnt work.
in access xls are not considered DB's and code is not
ustable for excel.
thanks any way
-----Original Message-----
Hi Martin,
the following Microsoft article should put you on the
right track. I know it's for Access 2000 but the
principle should be the same. You will have to modify it
to work with Excel files.
http://support.microsoft.com/?id=198467
(ACC2000: How to Automate Importing All Files in a
Specific Folder)
hth
chas
.
..
