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

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.

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).

On Feb 15, 2:12 pm, fredg <fgutk...@example.invalid> wrote:

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.

Coby,

Did you ever resolve this issue?  I am having the same problem and it is
certainly related to the space.  Remove the space in the worksheet name and
it works.  Leave the space and pull the whole sheet (ie, 'Sheet 1!') and it
works.  I just can't get it to pick a range within a sheet that has a space.
Same errors you have including the strange replacement of the ! with a $.

Thanks