DoCmd.TransferSpreadsheet where there is a space in the sheet name

[bluegnu]

Hi, I'm having a bit of trouble importing an excel spreadsheet because the sheet name has a space in it.

Simplistically this is my code:

  DoCmd.TransferSpreadsheet acImport, 8, "ID_Latest_Temp_i", "INFORM - Increments Document.xls", True, "All Features!i:i"

Now that doesn't work because the jet engine can't find it.

If, however, I rename the sheet to "AllFeatures" (remove the space) and use the same code:

 DoCmd.TransferSpreadsheet acImport, 8, "ID_Latest_Temp_i", "INFORM - Increments Document.xls", True, "AllFeatures!i:i"

It works fine.

Problem is, I can't rename that as so much else hangs off it and I don't own it. So is there anything I can to do get around this?

 

[PHV]

Have you tried this ?
DoCmd.TransferSpreadsheet acImport, 8, "ID_Latest_Temp_i", "INFORM - Increments Document.xls", True, "[All Features]!i:i"

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[bluegnu]

Thanks for the response.

Yes I tried that and "[All Features!i:i]". When I do that I get an error "'[All Features]$i:i' is not a valid name"

Any other ideas?

 

[PHV]

And this ?

DoCmd.TransferSpreadsheet acImport, 8, "ID_Latest_Temp_i", "INFORM - Increments Document.xls", True, "'All Features'!i:i"

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[bluegnu]

Thanks for the response. I'm afraid that hasn't worked either. I've hunted high and low for a solution to this and I'm not sure it exists!

 

[bluegnu]

Well I have finally found a solution, and it's fairly really simple (as they often are). Simply putting the full path of the xls into the DoCmd works!:

DoCmd.TransferSpreadsheet acImport, 8, "ID_Latest_Temp", "c:\INFORM - Increments Document.xls", True, "All Features!i:i"