How do I import a collection of Excel files each containing multip

[KramerJ]

How do I import into Access 2003 a collection of Excel files each containing
multiple work sheets.

 

[ryguy7272]

Import all files into one table (probably not a good idea). But anyway...
Map to the folder that the files are in now:
below, I called ithe folder Import, loke this:
strPath = "C:\Import\"

Sub ImportAllExcelFiles()
On Error GoTo Err_F
Dim strPathFile As String, strFile As String, strPath As String, strSpec As
String
Dim strTable As String, ynFieldName As Boolean
ynFieldName = False
strPath = "C:\Import\"
'strSpec = "NameOfImportSpecification" ' Put your name here
strTable = "tablename"
strFile = Dir(strPath & "*.xls")
Do While Len(strFile) > 0
strPathFile = strPath & strFile
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, strTable,
strPathFile, ynFieldName
' Uncomment out the next code step if you want to delete the file after it's
imported
' Kill strPathFile
strFile = Dir()
Loop
Exit_F:
Exit Sub

Err_F:
MsgBox Err.Number & " " & Err.Description
Resume Exit_F

End Sub

If you want to import to several Tables, and you name the Tables as the code
runs, try this:

Function ImportFiles()
Dim strPathFile As String, strFile As String, strPath As String
Dim strTable As String
Dim blnHasFieldNames As Boolean
blnHasFieldNames = True
strPath = "C:\Import\"
strFile = Dir(strPath & "*.xls")
Do While Len(strFile) > 0
strPathFile = strPath & strFile
'Name the table
strTable = InputBox("Enter table name for file """ & strPathFile & """")
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, _
strTable, strPathFile, blnHasFieldNames
strFile = Dir()
Loop
MsgBox "Done with Import"
End Function

Call the function this way:
Private Sub Command0_Click()
Call ImportFiles
End Sub

Regards,
Ryan---

 

[KramerJ]

Many thanks, expecially the quick reply. Reason for importing all worksheets
from each workbook is to create a master Access table to automate the
conversion process and for further database development. I have been out of
the mainstream of development awhile so I am thankful for your assisstance.
Regards, Joe

 

[ryguy7272]

Please click 'Yes', if the post was helpful to you.