transfer spreadsheet with spaces in the sheetname

  • Thread starter Thread starter Coby
  • Start date Start date
C

Coby

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?

Thank for all of your help.
Coby.
 
This may be simplistic, but may be worth a try. What happens if you replace
every space in the file name with %20? In other words, parse for spaces and
replace with %20. I know the gurus here would probably have a better
approach.

Patk
 
not tested
"[Pricing 2008]!A2:U300"
hth



Coby said:
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.
Click to expand...
For instance:
DoCmd.TransferSpreadsheet acImport, 8, "Temp_SB_Pricing", strUsFile,
False, "Pricing!A2:U300"  - This Works Fine

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.
Click to expand...
Since I cannot dictate the sheet name from the source I get them from,
does anyone know how to deal with this?
Click to expand...
Thank for all of your help.
Coby.- Hide quoted text -
Click to expand...

- Show quoted text -
Click to expand...

I have now tried:
"[Pricing 2008]!A2:U300"
Chr(33) & "Pricing 2008" & Chr(33)& "!A2:U300"
"Pricing%202008!A2:U300"
"Pricing"& %20 & "2008!A2:U300"
"Pricing"& Chr(32) & "2008!A2:U300"
And none of those work
. . . maybe I am doing something else wrong because it sure seems like
the suggestions you all have proposed should logically work??

I appreciate the help. I will keep trying.
Coby.
 
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?

Thank for all of your help.
Coby.
Click to expand...

Using my testing worksheet name of
"Pricing 2008!A1:I10"

your "Pricing 2008!A2:U300" should work.

I'll assume that your strUsFile is the same in both instances.
Are you sure the spreadsheet type is 8?

What error message do you get?
Why do you assume it's the space in the worksheet name causing the
failure?
Is "Temp_SB_Pricing" an existing table? Perhaps there is a problem
with the existing field names not matching the imported spreadsheet
names.
Change your table name code to "tblImportTest" and see if the data is
imported into this new table.
 
Using my testing worksheet name of
"Pricing 2008!A1:I10"

your "Pricing 2008!A2:U300" should work.

I'll assume that your strUsFile is the same in both instances.
Are you sure the spreadsheet type is  8?

What error message do you get?
Why do you assume it's the space in the worksheet name causing the
failure?
Is "Temp_SB_Pricing" an existing table? Perhaps there is a problem
with the existing field names not matching the imported spreadsheet
names.
Change your table name code to "tblImportTest" and see if the data is
imported into this new table.

--
Fred
Please respond only to this newsgroup.
I do not reply to personal e-mail- Hide quoted text -

- Show quoted text -
Click to expand...

Thanks for the help, Fred!

The error I am getting is "Syntax error (missing operator0 in query
expression 'Pricing 2008$A2:U300'.
The message shows a $ in place of the ! that I actually used.

I am not sure if that means anything.

Also, I am not importing into an existing table. And, I am just going
with the field names that are automatically assinged.
I am using spreadsheet type 8

The strUsFile is the same in both instances: that is the directory
path and file name.

The only reason I think it has to do with the spaces is because when I
rename the worksheet name without spaces, then try it referring to the
name without spaces. . . it works. Also, the problem only exists when
I am dealing with spreadsheets with spaces in the names.

Perhaps, there is some other syntax that needs to be used ie,
brackets, apostrophe, etc. that the worksheet object requires to refer
to it whenever it has spaces.

I don't know . . . just thinking outloud to bounce that off of what
you think.

Thanks,

Coby.
 
It is my belief that these two things are true, when it comes to importing
spreadsheets:

- The "range" parameter must be just that, a Range. A range cannot include
the worksheet name, because, aside from my next bullet, only the first tab in
the worksheet can ever be imported. Thus, having page "prefix" would be moot

- You can define a named region, using the Define Name function in excel,
and have it map to the cells in any worksheet/range. For example, defining a
name ranged called "Pricing2008", and point that name at cells A1:Z999
(assuming this is your range of data to import). A defined name cannot have
a space, I believe, so the same problem would even exist, there. But that
may also be saying something.

I just tested the above, and these two statements work:

DoCmd.TransferSpreadsheet acImport, , "Price 2008", "C:\myfile.xls", True,
"test"

where TEST is a defined named range in the excel file. This also works:


DoCmd.TransferSpreadsheet acImport, , "Price 2008", "myfile.xls", True, _
"A1:X999"

Deduction tells me (and I am likely wrong, so I am open to calibration, as
this would solve a problem I also have been messing with), but you must
either have a range (without a worksheet prefix), ie CRx:CRx (CR=your
column/row, x=number range), or a defined name. And I think that is it.

If anyone finds out anything different, let me know!

Patk
 
Just to clarify my second belief: You can have a filename prefix..just not
one with a space. I believe this is the only way it will work because, for
example "Price2008!A2:U300" is, in reality, a defined named range. Again,
just my guess and I am open to calibration.

Patk
 
Thanks for the help, Fred!

The error I am getting is "Syntax error (missing operator0 in query
expression 'Pricing 2008$A2:U300'.
The message shows a $ in place of the ! that I actually used.

I am not sure if that means anything.

Also, I am not importing into an existing table. And, I am just going
with the field names that are automatically assinged.
I am using spreadsheet type 8

The strUsFile is the same in both instances: that is the directory
path and file name.

The only reason I think it has to do with the spaces is because when I
rename the worksheet name without spaces, then try it referring to the
name without spaces. . . it works. Also, the problem only exists when
I am dealing with spreadsheets with spaces in the names.

Perhaps, there is some other syntax that needs to be used ie,
brackets, apostrophe, etc. that the worksheet object requires to refer
to it whenever it has spaces.

I don't know . . . just thinking outloud to bounce that off of what
you think.

Thanks,

Coby.
Click to expand...

Some time ago I had to import data from a spreadsheet.
Some of the column names had trailing spaces in them, something like
"MySheet ". The spaces were not chr(32).
I don't remember now what the ascii was and I have no idea how they
got there, but it created a problem form me.
Perhaps the space in your worksheet name is not a chr(32) space as it
would be in your access module code.

Try this.
Open the workbook. Right-click on the worksheet name and select
Rename.
Copy the name to the clipboard using Ctrl + C.
Open the Access module and paste the name into the code using Ctrl+V.
Add the ! and the column and row numbers and place the entire Range
text within double quotes (not single quotes).
Does it work now?
This is about all I can think of without actually seeing and
experimenting with your spreadsheet.
 
Just to clarify my second belief: You can have a filename prefix..just not
one with a space. I believe this is the only way it will work because, for
example "Price2008!A2:U300" is, in reality, a defined named range. Again,
just my guess and I am open to calibration.

Patk
Click to expand...

Pat,
See my replies to Colby.
You can include a space in the worksheet name. Colby has a problem
with it in his import. I did not have a problem with it in my test
import.
I just checked the Ascii chart and chr(160) is also space.
Access uses chr(32), so that may be why the Access import doesn't find
the worksheet.

Perhaps if Colby see this message he can try:

"Pricing" & chr(160) & "2008!A2:U300"
 
This exact statement worked for me (note first is a link, second is an import):

DoCmd.TransferSpreadsheet acLink, , "Price 2008a", "C:\Documents and
Settings\pklocke\My Documents\2.5 New Ticket Mgmt\Data\2008-01.xls", True,
"Closed 200801!A1:B20"

Sorry for long file name...I did not want to tweek anything. Now, I am
using Access 2007, but not sure if it would make a difference. Here is the
import version:

DoCmd.TransferSpreadsheet acImport, , "Price 2008a", "C:\Documents and
Settings\pklocke\My Documents\2.5 New Ticket Mgmt\Data\2008-01.xls", True,
"Closed 200801!A1:B20"

Another anomoly, as I had been working on similar problem, but in my case, I
did not want a range...I wanted the whole worksheet:

If you want the whole worksheet, regardless of range, it appears you must
insert a bang (!) sign at the end of the worksheet name, like "Worksheet!"

I share that due to the fact it cost me two days work trying to figure out!
Near as I can tell, it is not documented anywhere.

Patk
 
Haven't tested this recently but some notes in old code of mine say that if
special characters are involved, the syntax would be:
" 'My Sheet!' "

*The spaces between single and double quotes in the above are only for
syntax clarity and should be removed (i.e, s/b "' and '").*

I'm sure that the !' sequence is the key reason for me leaving that note to
myself, so maybe:
"'Pricing 2008!'A2:U300"

If I remember correctly, using this syntax when there are no special
characters in the name will cause an error (after all, why should anything
like referencing an Excel sheet be simple?). I didn't think that spaces
qualified as special characters, but since you are having problems, I
thought I'd toss this out in case its of any help. (It might be that the
worksheet has what *appears* to be a space, but is actually a special
character in disguise, in which case this might do the trick).

--
HTH,
George


Using my testing worksheet name of
"Pricing 2008!A1:I10"

your "Pricing 2008!A2:U300" should work.

I'll assume that your strUsFile is the same in both instances.
Are you sure the spreadsheet type is 8?

What error message do you get?
Why do you assume it's the space in the worksheet name causing the
failure?
Is "Temp_SB_Pricing" an existing table? Perhaps there is a problem
with the existing field names not matching the imported spreadsheet
names.
Change your table name code to "tblImportTest" and see if the data is
imported into this new table.

--
Fred
Please respond only to this newsgroup.
I do not reply to personal e-mail- Hide quoted text -

- Show quoted text -
Click to expand...

Thanks for the help, Fred!

The error I am getting is "Syntax error (missing operator0 in query
expression 'Pricing 2008$A2:U300'.
The message shows a $ in place of the ! that I actually used.

I am not sure if that means anything.

Also, I am not importing into an existing table. And, I am just going
with the field names that are automatically assinged.
I am using spreadsheet type 8

The strUsFile is the same in both instances: that is the directory
path and file name.

The only reason I think it has to do with the spaces is because when I
rename the worksheet name without spaces, then try it referring to the
name without spaces. . . it works. Also, the problem only exists when
I am dealing with spreadsheets with spaces in the names.

Perhaps, there is some other syntax that needs to be used ie,
brackets, apostrophe, etc. that the worksheet object requires to refer
to it whenever it has spaces.

I don't know . . . just thinking outloud to bounce that off of what
you think.

Thanks,

Coby.
 
I realise this is a very old thread, but I ran across it while spending several hours trying to solve the same problem, and thought anybody looking for a solution here in the future might appreciate an answer.

The solution is simple, but if it is documented anywhere I cannot find it.

The TransferSpreadsheet command doesn't take the same format for specifying a sheet + range as you're used to working in Excel. You don't need single quotes for sheet names with spaces, and you use $ instead of ! to demarcate the sheet name from the range address.

For example, instead of "'Pricing 2008'!A2:U300", you instead need "Pricing 2008$A2:U300"

Of course, then you still have the problem of making sure the space in your code is the same character as the space in your sheet name. i got around this by opening the workbook first and loading the name of the sheet directly into a variable.
 
Back
Top