Transferspreadsheet runtime error

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Kevin Wickersheim - 11 Jun 2004 22:35 GMT

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?

Reply to this Message

Ken Snell - 12 Jun 2004 01:58 GMT

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?

Signature

Ken Snell
<MS ACCESS MVP>

> I have been using the following Docmd in access 97.
>
[quoted text clipped - 18 lines]
>
> any ideas?

Reply to this Message

- 12 Jun 2004 14:49 GMT

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?

>-----Original Message-----
>If I recall correctly, TransferSpreadsheet does not like ' characters around
[quoted text clipped - 29 lines]
>
>.

Reply to this Message

Ken Snell - 12 Jun 2004 20:48 GMT

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 & "!"

Signature

Ken Snell
<MS ACCESS MVP>

> 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
[quoted text clipped - 57 lines]
> >
> >.

Reply to this Message

Ken Snell - 12 Jun 2004 21:20 GMT

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 & "!"

Signature

Ken Snell
<MS ACCESS MVP>

> 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
[quoted text clipped - 75 lines]
> > >
> > >.

Reply to this Message