Transferspreadsheet runtime error

[Kevin Wickersheim]

I have been using the following Docmd in access 97.

DoCmd.TransferSpreadsheet acImport, ,
strTableName, "c:\data\resources\" & strFileName, ,
strTabName

The variables are strings that look something like this.
strFileName = "exclefilename.xls"
strTabname = "'Sheetname'!"

I am trying to convert the DB to 2002 and I am getting an
error

Runtime error '3125':

''Sheetname'$' is not a valid name. Make sure that is
does not include invalid characters or punctuation and
that it is not too long.

I have tried taking out the inner ' and ! with no luck.

any ideas?

 

[Ken Snell]

If I recall correctly, TransferSpreadsheet does not like ' characters around
a tab name unless the tab name has spaces in it. Thus, you should not use
them in this case if Sheetname is the name of the tab. It should be
"Sheetname!"

Which leads me to my next question: is Sheetname the actual name of a tab
in the file?

 

[Guest]

Yes sheetname is the name of a tab. The variable sheetname
is a name of a tab in the file, and in some cases it may
have spaces.

In the file(s) that are being imported there maybe 2 or
more tabs. There will be a tab in every file named "PAR ID
& Tablist" which is a list of all the tabs to be imported.
The code then uses this list to set the
variable "Sheetname".

I am getting the runtime error when importing the "PAR ID
& Tablist" tab. I have tried changing the name of the tab,
removing all spaces and the "&", with no luck.

I changed the name of the "PAR ID & Tablist" to "PARIDS"
and set strTabName to PARIDS!. But I still can't get the
import to work if there are spaces in the tabname.

Anyway to check the tabname, from the list imported, for
spaces?

 

[Ken Snell]

I think you've mistyped. "Sheetname" literally isn't the name of a tab in
the file (you're using it as a generic name for this example?) -- what
you're saying is that strTabName is the name of a variable and the variable
holds the name of the tab. Right? Big difference between these two
situations.

To use a variable as the sheet name argument, you don't put it in quotes;
just put the variable name and then concatenate a ! character at the end. To
check for spaces, use something like this to combine both actions:

Dim intSpace As Integer
Dim strQuote As String
intSpace = Abs(InStr(strTabName, " ") = 0) + 1
strQuote = Choose(intSpace, "", "'")
DoCmd.TransferSpreadsheet acImport, , strTableName, _
"c:\data\resources\" & strFileName, , _
strQuote & strTabName & strQuote & "!"

 

[Ken Snell]

My apology....typo in my posted code. Changed the Choose arguments' order.

Dim intSpace As Integer
Dim strQuote As String
intSpace = Abs(InStr(strTabName, " ") = 0) + 1
strQuote = Choose(intSpace, "'", "")
DoCmd.TransferSpreadsheet acImport, , strTableName, _
"c:\data\resources\" & strFileName, , _
strQuote & strTabName & strQuote & "!"