Linking multiple Excel sheets

  • Thread starter Thread starter Jazzman
  • Start date Start date
J

Jazzman

Having a problem with the TransferSpreadsheet function in identifying the
sheet (name) in the Range argument. The syntax, according to every source I
have found, indicates to follow the T/F argument (for column/field names)
with the Range - which can be the sheet NAME (with or without an actual cell:
cell range), followed by the exclamation mark.

I've done this (using a variable or the text string), but I still get an
error message (#3011) saying it can't find the range object - but the range
name it displays REPLACES my ! with a $. And of course, the sheet name I want
does not have a $ anyplace in it.

This is frustrating as hell (as Access can sometimes be). Any ideas are most
appreciated. TIA.

Art
 
Hi Art,

Make sure that the sheet name doesn't contain any character that Access
doesn't allow in a table name. (I only use alphanumeric characters and
underscores.)

Also, put a $ after the sheet name, not a !.

If that fails, create a named range in the workbook that includes all
and only the cells you want to import. Then pass this - without $ or ! -
as the Range argument.
 
Hi John,

Thanks for your reply, but -
(1) using the $ after the sheet name instead of the ! also did not work, and
gave me a run time error #3125 ("Sheet1$ is not a valid name................")
 
This has just worked for me:

DoCmd.TransferSpreadsheet acLink, acSpreadsheetTypeExcel9, "XLLinkTest",
"C:\Temp\aa-3.xls", -1, "Sheet1$"

Does your workbook actually include a sheet named "Sheet1"?

Can you link it using the wizard (File|Get External Data|Link)?

What happened when you tried using a named range as I suggested?
 
John,

Yes, my workbook contains 3 sheets, "Sheet1", "Sheet2", and "Sheet3".

This is your code (modified with my variables for table and file names), and
it fails with error #3125:

DoCmd.TransferSpreadsheet acLink, acSpreadsheetTypeExcel9,
"XLLinkTest", _
strPathName & strCurrGroupName & ".xls", True, "Sheet1$"

I can't bother with creating range names. That requires opening and
modifying every spreadsheet, and as I explained, I'm trying to remove as much
of the manual file manipulation as possible by using Access to make this a
"pushbutton" procedure for the end user. I haven't tried using the wizard
for basically the same reason.

BTW - This is Access2k
Art
 
I think the most likely cause is that
strPathName & strCurrGroupName & ".xls"
does not evaluate to what you think it does.
 
John,

Well, if your assertion is true, how do you explain the following code -
which works perfectly??
And as I indicated yesterday, using a $ instead of a ! produced a different
error code, but nevertheless an equivalent "dead end". (Please note that the
only difference in the code below is the exclusion of the Range argument in
the "Sheet1!" condition).

'Retrieve the MedGrpID / TabName from the table as key to the
spreadsheet tab name
strMedGrpID = rsMedGrps!MedGrpID
strTabName = rsMedGrps!TabName & Chr(33)

If strTabName = "Sheet1!" Then
DoCmd.TransferSpreadsheet acLink, acSpreadsheetTypeExcel9, _
"tbl" & strCurrGroupName & "_" & strMedGrpID, _
strPathName & strCurrGroupName & ".xls", True
Else
DoCmd.TransferSpreadsheet acLink, acSpreadsheetTypeExcel9, _
"tbl" & strCurrGroupName & "_" & strMedGrpID, _
strPathName & strCurrGroupName & ".xls", True, strTabName
End If

Art

John said:
I think the most likely cause is that
strPathName & strCurrGroupName & ".xls"
does not evaluate to what you think it does.
John,
Click to expand...
[quoted text clipped - 37 lines]
Click to expand...
 
Back
Top