Import specific sheet from Excel workbook

  • Thread starter Thread starter Guest
  • Start date Start date
G

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 !
 
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.
 
Just put the name of the sheet in the range arguement.

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9 , _
"TableOrQueryName","C:\Spreadsheets\MyWorkbook.xls", _
True, "SheetNameHere"
 
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 ?
 
thanks John !!

John Nurick said:
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.
Click to expand...
 
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.
 
Access101 said:
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.
Click to expand...
 
Back
Top