DoCmd.TransferSpreadsheet ERROR

[jl39775]

The line of code below gives me an error. I believe I get the error because the name of the excel file has a "-" in it. Is there a way I can keep the hypen in the filename?I don't want to check the file to see if it has a hypen then remove it before the transfer. Attached is the error message.

xlsName= "C:\Documents and Settings\tvail\Desktop\subcontractactuals09-02.xls"

DoCmd.TransferSpreadsheet , acSpreadsheetTypeExcel9, "ExcelImportTemp", xlsName

Thanks,

James

 

[Drevlin]

What version of access and windows are you using?
I had no problem importing a file with a hyphen in the name. (used your filename actually)
I'm on Windows NT and Access 2002.

 

[jl39775]

I'm on Windows 2000 and Access 2002. I forgot to mention that I am getting the file name from a commonDialog box. I used xlsName= "C:\Documents and Settings\tvail\Desktop\subcontractactuals09-02.xls" as an example. Maybe the commonDialog box is causing my problems. I try without it.

James

 

[Drevlin]

Well according to the error box you posted for some reason the file name is getting a "$" attached to the end of it. It must be getting added to the string somehow but if it just comes over that way do a:
str = Left(str, Len(str)-1)
to drop it off.

 

[jl39775]

I've already try that. The variable does not have a "$" at the end of it. I don't know why the error has it. When i take the last character off it removes "s" form "xls".

James

 

[DALeffler]

Are you sure that path is right?

To get to the desktop on W2000 for me, I use (or thought I used):

"C:\WINDOWS\Desktop\Access_Import.xls"

HTH,

Doug.