How can I copy 10 worksheets from a single workbook to a single ac

[Guest]

Dear All,

Please how can I copy 10 worksheets from a work book in excel to one access
table. The worksheets have identical columns.

I have used Transferspreadsheet method in DoCmd, but it only copies the
first worksheet. It does not loop from 1 to 10. Please see the code below:

Sub xlsSheetLoop()
Dim xlApp As Excel.Application
Dim xlWS As Excel.Worksheet
Dim i As Integer
Dim strFileName As String
Dim wkShName As String

strFileName = "C:\Documents and Settings\a99858\My Documents\P2001.xls"
Set xlApp = New Excel.Application
On Error Resume Next
For i = 0 To xlApp.Worksheets.count
Set xlWS = xlApp.ActiveWorkbook.Worksheets(i)
sheetName = xlWS.Name
DoCmd.TransferSpreadsheet acImport, , "MultiSheet_Example", strFileName, -1,
wkShName & "!A1:E8"

Next i

End Sub

 

[Bill Manville]

sheetName = xlWS.Name

Did you mean
wkShName = xlWS.Name
?

Maybe you don't use Option Explicit at the top of your modules.
It will tell you about undeclared variable names and avoid such
problems.

(VBE > Tools > Options > Editor > Require variable declaration will add
it for future modules you create)

Bill Manville
MVP - Microsoft Excel, Oxford, England

 

[Guest]

I would suggest a different way. Just creat an ADODB recordset and then
update the recordset as you loop through the spreadsheet and cells.
Recordset gives you much more power and flexiblities. For example, the
spreadsheet does not have to have identicle layout.

chun