Error 3011

[Hal2604 via AccessMonster.com]

I am trying to use VB to import a spreadsheet the line of code is as follows:

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "WeeklyImport",
"P:\Unapplied\Unapplied.xls", True, "Detail"

I get Error 3011 MS JetDatabase Engine couldn't find object "Detail" when I
run the code off a button. Detail is the name of the worksheet. If I take
the name of the worksheet off it imports okay. The problems is it may not
always be the first worksheet so I need to specify which worksheet.

I use the line of code for another spreadsheet/worksheet combo & it works
fine. I can't figure out what I am doing wrong. Any help would be
appreciated.

Thank you
Holly

 

[davjoh123]

I just did a test of the TransferSpreadsheet method and it is
requesting a range where you are supplying a sheet name. That may be
the problem.

 

[John Nurick]

Hi Holly,

The Range argument of TransferSpreadsheet requires a range, not a
worksheet name, so if you give it
Detail
it looks for a range named "Detail".

To specify the worksheet named "Detail", give TransferSpreadsheet the
range
Detail$

You can also specify a block of cells on the worksheet, e.g.
Detail$A17
or contiguous columns
Detail$B

 

[Hal2604 via AccessMonster.com]

Thank you John for your assistance. That was exactly what I needed.

Holly