Import Excel sheet

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

Guest

Hi,

I am trying to import a specific sheet in a workbook that has several
sheets. I know I need to use the transferspreadsheet method, but I am not
sure how to format the range I have tried sheet(1) and also the name
"Wal-Mart Stores" with no success. Any help would be appreciated.

Thanks,
 
Post the code and let's see if we can debug it. Using the Range argument
does work, it just needs the correct syntax.
 
This:
DoCmd.TransferSpreadsheet acImport, , "WalmartStores", stDocName, True,
Sheet(1)

And this:

DoCmd.TransferSpreadsheet acImport, , "WalmartStores", stDocName, True,
"Wal-Mart Stores"

These are the 2 ways I tried. The second one I physically copied the name
of the sheet and pasted it adding the quote marks.

Thanks
 
On the first one I am getting a sub or function not defined and highlights
sheet(1)

On the second one I am getting a runtime erro 3011 "could not find object
'Wal-Mart Stores'"

I double checked the path in the stDocnName and it is correct. I am using
Office2000 and going across a windows network. Here is the stDocName

stDocName = "G:\Accounting\AR\AR Database\walmart stores.xls"
 
Okay, I have done some extensive testing on this and found the problem.
Access does not like the space or the - in the sheet name. I tried every
variation I could think of. With no space and a - It did not like it. I
tried spaces and no - it did not like it. I am getting the same error you
are (3011).

It needs to be WalmartStores or it won't find it. I don't know why. You
will have take spaces and - out of the sheet name.

I know there is a way to addess the ordinal position of a sheet, but I can't
remember it and I can't find it. I tried a lot of ways, but none worked.
 
I'm importing a particular sheet from Excel and the sheet has both a space
and a hyphen in the name.

You may need to reference the sheet by adding ! after the sheet name:

DoCmd.TransferSpreadsheet acImport, , "WalmartStores", stDocName, True,
"Wal-Mart Stores!"

Worth a try - worked for me (but Office 2003).

R
 
This worked like a charm. Sorry it took so long to get back.

Thanks a million!!!!!!!!!!!!!!!!!!!!
 
Next issue. One of my sheets isn't importing all of the data. It seems to
be randomly skipping lines on one sheet. I am importing a total of 5. THe
other 4 work fine. Is it possible to specify a named range after the sheet
name?
 
Figured it out. I just created a macro to run that converts the first column
to numbers and then saves the file, and it imports perfectly each time.
 
Hi Robin -

I know this is a post that is quite awhile after your original note, but
this was a big help to me as well when I encountered this file.

My question: how did you figure out appending "!" to the worksheet name? I
don't see that in any documentation. I can only seem to get the
transferSpreadsheet method to import a worksheet using the "!" and only if
the worksheet has a space in it.

The ways of Microsoft are sometimes Strange - I'm afraid.

Best Regards,

Chris (ct60)
 
Back
Top