Help on import

[sbcglobal]

I routinely need to import some tables into Access 2003 from a fixed Excel
file. The Excel contain several sheets, which I all wonna to import to
Access. But the repeat-import is hineous, so any expert idea on how to build
a VBA sub to do this kind of job?

Thanks a lot!

 

[Ken Snell [MVP]]

Will the Excel file contain the same number of worksheets each time? or a
varying number?

 

[sbcglobal]

same number, well, actually it's same excel file, containing a fixed number
of sheets. i usually update the excel sheets and therefore need to update
access tables. i understand that i can create a 'link' table, but that's not
something i'd like to do..any suggestion for vba code?

many thanks,

 

[Ken Snell [MVP]]

Some simple, generic code, assuming you know the number and name of sheets
in the EXCEL file (the example assumes there are 5 sheets):

Dim lngCount As Long
Dim strSheetNames(1 To 5)
strSheetNames(1) = "NameOfFirstSheet"
strSheetNames(2) = "NameOfSecondSheet"
strSheetNames(3) = "NameOfThirdSheet"
strSheetNames(4) = "NameOfFourthSheet"
strSheetNames(5) = "NameOfFifthSheet"
For lngCount = 1 To 5
If InStr(strSheetNames(lngCount), " ") > 0 Then strSheetNames(lngCount)
= _
"'" & strSheetNames(lngCount) & "'"
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, _
"TableName", "C:\MyFolder\MyFile.xls", , strSheetNames(lngCount) &
"!"
Next lngCount


--

Ken Snell
<MS ACCESS MVP>