Import excel sheets to table

[polina]

Hello,

I want to import certain sheets from the excel spreadsheet into one access table.

I know how to import one sheet:
DoCmd.TransferSpreadsheet acImport, , "Table1", path, True, "Group_Billing!"

But is there a way to import a few sheets?

Thanks.

 

[jjturner]

To my knowledge, a named range can't consist of multiple sheets, therefore you'd have to run the TransferSpreadSheet method for each 'table' in your Workbook.

Of course Excel supports 3-D references, so you may want to have a play around with that, but then you'd be talking Automation in terms of aggregating your data prior to Importing.

HTH,
John

 

[Oldsoftboss]

Will you be doing this on a regular basis or is this just a one off?
Dave

 

[polina]

I will be doing this on regular basis

Thanks

 

[jetson]

Private Function GetSheets()
Dim i As Integer
Dim strSheet(3) As String

strSheet(1) = "This Sheet!"
strSheet(2) = "That Sheet!"
strSheet(3) = "Other Sheet!"

For i = 1 To 3
DoCmd.TransferSpreadsheet acImport, , "Table" & i, "y:\testing2.xls", True, strSheet(i)
Next i

End Function