Juzer pfpt <
[email protected]> napisa³
| I want to import all the sheets in a book into a Access DB, instead of
| the only one allowed. Any idea?
Instead of using automation you can use ISAM and open xls as DAO.Database
and later ask it for it TableDefs collection:
Sub test1()
Dim p As String
Dim f As String
Dim db As Database
Dim tb As TableDef
'Lets say we look for every xls in current directory...
p = CurrentProject.Path & "\"
f = Dir(p & "*.xls")
Do Until f = ""
Set db = OpenDatabase("", 0, 0, "Excel 8.0;HDR=YES;IMEX=2;DATABASE=" &
p & f)
For Each tb In db.TableDefs '<<< every worksheet or named range
If Not tb.Name Like "*_" Then 'skip tmp worksheets...
'imported table name schema: xlsName_sheetName
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel8, _
Replace(f, ".xls", "", , , 1) & _
"_" & _
Replace(tb.Name, "$", "", , , 0), _
p & f, _
True, _
tb.Name '<<< our worksheet or named range
End If
Next
f = Dir
Loop
End Sub
