Import specific sheet from Excel workbook

[Guest]

Is there a way to import a specific sheet within an Excel workbook using the
TransferSpreadsheet command in a macro or the TransferSpreadsheet method of
the DoCmd object ? I know you can put a range option onto the command, but
what if the range you want is all the data in a specific sheet and that the
number of lines in that sheet can vary from import to import (the format will
always remain the same, however) ?

Thanks !

 

[John Nurick]

Hi Eric,

With luck all you need to is pass something like this as the Range
argument:
"Sheet1$"
The $ signifies a worksheet name as opposed to a named range, and
TransferSpreadsheet will then attempt to import the UsedRange of the
worksheet.

The UsedRange is basically the rectangle from A1 to the bottom-most
rightmost cell on the worksheet that contains a value or a cell format.
If necessary, search the newsgroups for "reset usedrange" or some such.

 

[Guest]

Just put the name of the sheet in the range arguement.

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9 , _
"TableOrQueryName","C:\Spreadsheets\MyWorkbook.xls", _
True, "SheetNameHere"

 

[Guest]

Thanks for your assistance ! I have a non-Access question for you.....your
user name "klatuu"....is that a reference to the musical group from back in
the late 70's or early 80's that was rumored to be the Beatles reborn ?

 

[Guest]

thanks John !!

Hi Eric,

With luck all you need to is pass something like this as the Range
argument:
"Sheet1$"
The $ signifies a worksheet name as opposed to a named range, and
TransferSpreadsheet will then attempt to import the UsedRange of the
worksheet.

The UsedRange is basically the rectangle from A1 to the bottom-most
rightmost cell on the worksheet that contains a value or a cell format.
If necessary, search the newsgroups for "reset usedrange" or some such.

 

[Access101]

I get error 3011, even though I've created a new workbook from scratch with
"Sheet1" as the default:

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "Import",
"C:\Test.xls", True, "Sheet1"

Any suggestions are appreciated.

 

[simone]

I get error 3011, even though I've created a new workbook from scratch
with
"Sheet1" as the default:

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "Import",
"C:\Test.xls", True, "Sheet1"

Any suggestions are appreciated.