I am using the transfer spreadsheet command, but whenever the Excel
spreadsheet has spaces in the name, I don't know how to deal with that
therefore is doesn't work.

For instance:
DoCmd.TransferSpreadsheet acImport, 8, "Temp_SB_Pricing", strUsFile,
False, "Pricing!A2:U300" - This Works Fine


BUT


For instance:
DoCmd.TransferSpreadsheet acImport, 8, "Temp_SB_Pricing", strUsFile,
False, "Pricing 2008!A2:U300" - DOES NOT WORK - and I am pretty sure
it is because of the space between pricing and 2008 in the sheet name.


Since I cannot dictate the sheet name from the source I get them from,
does anyone know how to deal with this?

Thanks your all of your help,
Coby.

Perhaps you can use Chr(32) in the string, like:

DoCmd.TransferSpreadsheet acImport, 8, "Temp_SB_Pricing", strUsFile,
False, "Pricing" & Chr(32) & "2008!A2:U300"

Did that help?

mmcdonal

Look it up at: http://wrox.books24x7.com

I tried it and got "Syntax error (Missing Operator) in query expression"
So, I double-checked to make sure I didn't make a Typo and still get the same message.

Maybe I am doing something else wrong.
I will keep trying.

Thanks,
Coby.

How about this then:

Dim sString As String

sString = "Pricing" & Chr(32) & "2008!A2:U300"

DoCmd.TransferSpreadsheet acImport, 8, "Temp_SB_Pricing", strUsFile,
False, sString



mmcdonal

Look it up at: http://wrox.books24x7.com

For some reason that doen't work either?
It seams like either:
1.The SheetName from the Excel spreadsheet is representing the spaces differently.
Or
2.The is some other bracketing or nesting characters that may need to be used when referring to the worksheet object?

I am guessing at this point hoping that we can come up with something here.
It sure seems like what you have suggested should surely work.

I really appreciate the time.
Coby.