Import Excel sheet

[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,

 

[Guest]

Post the code and let's see if we can debug it. Using the Range argument
does work, it just needs the correct syntax.

 

[Guest]

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

 

[Guest]

Your second example appears to be correct. What errors or results are you
getting?

 

[Guest]

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"

 

[Guest]

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.

 

[Guest]

Thanks for your help

 

[Robin]

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

 

[Guest]

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

Thanks a million!!!!!!!!!!!!!!!!!!!!

 

[Robin]

No problem. Glad it helped.

 

[Guest]

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?

 

[Guest]

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.

 

[Guest]

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)