Link Multiple Excel Worksheets to Seperate Access Tables

[PatK]

I believe this is not possible (at least with this method), but thought I
would ask the gurus. This code:

Sub testlink()
DoCmd.TransferSpreadsheet acLink, 8, "WS1", _
"C:\Documents and Settings\myuser\myfilename.xls", True
End Sub

will always link JUST the first worksheet to Access (and works fine for me).
Any way to link to the second worksheet, third, and so on?

You can do this thru the GUI (AC2007) but no, apparently, in VB. Ideas?

Thanks!

Patk

 

[Klatuu]

Look at the Range argument for TransferSpreadsheet. You can use the name of
the worksheet in this range.

 

[PatK]

Hi, Dave....

I don't believe that works (at least, it never has for me). I am in Access
2007, if that makes any difference, but I am pretty sure I had same problem
in 2003, as well. HEre is my exact subroutine (it is just for testing, as
later, I will pass values to all the paremeters, if I can get it to work):

Sub testlink()

DoCmd.TransferSpreadsheet acLink, , "Price 2008", _
"C:\Documents and Settings\pklocke\My Documents\2.5 New Ticket
Mgmt\Data\2008-01.xls", True, "Open200801"

End Sub

Sorry for long file name, but I don't want to leave anything out, or make
any assumptions. The file reference by fully qualified name, absolutely
exists, as does the worksheet nameed Open200801 (and I have tried various
names). If I leave off the worksheet parameter, it links to the first
worksheet tab, fine (except, the sheet I want to link to will not, typically,
be the first in the file). If I specificy a range, it also works fine, or
even a defined named Table. But not with just the worksheet name.

You see anything I am missing? I admit I may have gone bug-eyed trying to
figure this out....thanks!

Patk

 

[Klatuu]

I have used that technique in 2003, but I don't have 2007, yet, so I don't
know if there is a difference. The only other thing I can suggest is
enclosing the worksheet name in brackets:

"C:\Documents and Settings\pklocke\My Documents\2.5 New Ticket
Mgmt\Data\2008-01.xls", True, "[Open200801]"

 

[PatK]

I will tried that, but no joy. However, I think I just had a breakthru. I
put, for giggles, a bang (!) sign at the end of the file name, and behold, it
worked! I also inserted a space into the worksheet name (there is another
thread in the newsgroup discussing a problem with a space in the worksheet
name), and it also worked!

That ! would be a GREAT thing to have been documented somewhere!!! LOL

PatK

I have used that technique in 2003, but I don't have 2007, yet, so I don't
know if there is a difference. The only other thing I can suggest is
enclosing the worksheet name in brackets:

"C:\Documents and Settings\pklocke\My Documents\2.5 New Ticket
Mgmt\Data\2008-01.xls", True, "[Open200801]"

--
Dave Hargis, Microsoft Access MVP

Hi, Dave....

I don't believe that works (at least, it never has for me). I am in Access
2007, if that makes any difference, but I am pretty sure I had same problem
in 2003, as well. HEre is my exact subroutine (it is just for testing, as
later, I will pass values to all the paremeters, if I can get it to work):

Sub testlink()

DoCmd.TransferSpreadsheet acLink, , "Price 2008", _
"C:\Documents and Settings\pklocke\My Documents\2.5 New Ticket
Mgmt\Data\2008-01.xls", True, "Open200801"

End Sub

Sorry for long file name, but I don't want to leave anything out, or make
any assumptions. The file reference by fully qualified name, absolutely
exists, as does the worksheet nameed Open200801 (and I have tried various
names). If I leave off the worksheet parameter, it links to the first
worksheet tab, fine (except, the sheet I want to link to will not, typically,
be the first in the file). If I specificy a range, it also works fine, or
even a defined named Table. But not with just the worksheet name.

You see anything I am missing? I admit I may have gone bug-eyed trying to
figure this out....thanks!

Patk