Selecting a worksheet in TransferSpreadsheet

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

Guest

Hi -

Is there a way to specify a particular worksheet when using the VBA
"Transferspreadsheet" command? I tried simply placing the worksheet name in
quotes in the Range parameter, but only got the first worksheet tab in each
instance.

If I have to use a named range, it will be a bit awkward, as I'll have to
use automation to go in and create ranges for each worksheet. My main
roadblock there is figuring out how to define new range names.

My apologies if these are faqs. I scanned about a dozen related posts, and
none had this specific question.

I'm running Access 2002.

Thanks,

Phil Freihofner
 
Phil,

Not sure what I have to say applies in A2K2, I run A2K, but here's what
I've found anyway: putting the worksheet name in the Range argument does
work, and replaces/overwrites the target worksheet, as long as the sheet
name includes just alphanumeric characters without any other printable
characters; the latter get accross as underscores, and create a new
sheet with the underscore instead of the character. Are you using any of
those?
I suppose it's no accident why this is undocumented in Access help,
where it says that the Range argument is for Import only!

Nikos
 
Nikos -

I thank you for the reply. Unfortunately, I forgot to mention I am
importing, not exporting.

The code I am using follows:

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, _
"system sales", filenm, True, "system sales"
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, _
"system obj", filenm, True, "system_obj"
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, _
"lso sales", filenm, True, "lso sales"
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, _
"lsoobj", filenm, True, "lsoobj"

Note: for the worksheet "system obj", I tried using a worksheet name that
used an underscore for a blank. On "lsoobj" I changed the worksheet name to
one without a space in it.
 
Delimit worksheet names containing spaces with ' characters. End any
spreadsheet name with !.

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, _
"system sales", filenm, True, "'system sales'!"

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, _
"lsoobj", filenm, True, "lsoobj!"
 
Back
Top