Importing Specific Worksheet from Excel Spreadsheet

  • Thread starter Thread starter Lamar
  • Start date Start date
L

Lamar

I want use the TransferSpreadsheet docmd to import a worksheet named
"March" from an Excel spreadsheet named "2007 Archive. Can you import the
specific worksheet needed from a spreadsheet. Here is may code:

DoCmd.TransferSpreadsheet acImport, , "Archive", CurrentDBDir & "2007
Archive", True

When I import I get the first worksheet named "January". However I want to
import the "March" worksheet from the same Excel spreadsheet. Any help is
appreciated.
 
The following is copied, unedited, from working code. I think the
notes address your question: use the 6th argument (Range), to specify a
range or a sheet.

DoCmd.TransferSpreadsheet _
TransferType:=acImport, _
TableName:=strTargetTable, _
FileName:=strSourceFile, _
HasFieldNames:=bolFieldNames, _
Range:=strSheetName

' 2nd argument omitted here: SpreadsheetType:= acSpreadsheetTypeExcel8
(default circa AC2000)

' Range:= use to specify a sheet and/or range on sheet.
' Per MSKB #210379 03/2004
'To import a sheet: Range:= "MySheet!"
'To import a range: Range:= "MySheet!A1:C50"
'? Sheets w/special characters: Range:="'My#Sheet!'# ?
 
In addition to Georges reply, create a named range from the march worksheet
and use that as the selection.

hth
 
Back
Top