Odd TransferSpreadsheet error msg 3125; $ not a valid name

[ragtopcaddy via AccessMonster.com]

Here is my TransferSpreadsheet code:

DoCmd.TransferSpreadsheet _
TransferType:=acExport, _
SpreadSheetType:=acSpreadsheetTypeExcel9, _
TableName:="qryLeaseEventStores", _
FileName:=strRptPath, _
HasFieldNames:=True, _
Range:="'Lease Events'!A3"

I get Run-time error '3125':

''Lease Events'$A3' is not a valid name.
First of all, I didn't use the $ character. Why does it misinterpret a ! as a
$ character?

 

[Klatuu]

The problem is the ' in the name. Access is trying to use that as a string
delimiter.
The $ can be used in range names to denote SheetName$A3:Z99
It is converting the ! to $.

 

[ragtopcaddy via AccessMonster.com]

Does the space in the name, "Lease Events", not necessitate the use of the 's
?

How then should I alter my string, "'Lease Events'!A3"?

The problem is the ' in the name. Access is trying to use that as a string
delimiter.
The $ can be used in range names to denote SheetName$A3:Z99
It is converting the ! to $.

Here is my TransferSpreadsheet code:

[quoted text clipped - 11 lines]

First of all, I didn't use the $ character. Why does it misinterpret a ! as a
$ character?

 

[Klatuu]

Use Brackets
"[Lease Events]!A3"
--
Dave Hargis, Microsoft Access MVP

Does the space in the name, "Lease Events", not necessitate the use of the 's
?

How then should I alter my string, "'Lease Events'!A3"?

The problem is the ' in the name. Access is trying to use that as a string
delimiter.
The $ can be used in range names to denote SheetName$A3:Z99
It is converting the ! to $.

Here is my TransferSpreadsheet code:

[quoted text clipped - 11 lines]

First of all, I didn't use the $ character. Why does it misinterpret a ! as a
$ character?

 

[ragtopcaddy via AccessMonster.com]

Thanks for your help, Klaatu.

I used the brackets and got:

Run-time error '3126':

Invalid bracketing of name ".

I wonder what that " means in the error msg.

Bill

Use Brackets
"[Lease Events]!A3"

Does the space in the name, "Lease Events", not necessitate the use of the 's
?

[quoted text clipped - 10 lines]

 

[Klatuu]

I thought the bracketing would do it.
Have you tried with no delimiters at all?
This is a result of poor naming, that is using spaces in names. But, maybe
you don't have control over that.
--
Dave Hargis, Microsoft Access MVP

Thanks for your help, Klaatu.

I used the brackets and got:

Run-time error '3126':

Invalid bracketing of name ".

I wonder what that " means in the error msg.

Bill

Use Brackets
"[Lease Events]!A3"

Does the space in the name, "Lease Events", not necessitate the use of the 's
?

[quoted text clipped - 10 lines]

First of all, I didn't use the $ character. Why does it misinterpret a ! as a
$ character?

 

[ragtopcaddy via AccessMonster.com]

I changed the sheet name, although I've done this sort of thing with sheets
with spaces in their names before. Then I tried:

Range:="LeaseEvents!$A$3"

Now I get Run-time error '3125':

''Lease Events'$$A$3' is not a valid name.

I've been doing Excel/Access/VBA programming a long time. This is a rather
routine exercise, but I don't recollect ever seeing anything like this before.
A '!' is not a '$'.

I thought the bracketing would do it.
Have you tried with no delimiters at all?
This is a result of poor naming, that is using spaces in names. But, maybe
you don't have control over that.

Thanks for your help, Klaatu.

[quoted text clipped - 15 lines]

 

[Klatuu]

I owe you a large apology. It only dawned on me just now you are trying to
use this in an export.
Per VBA Help (2003)
Range Optional Variant. A string expression that's a valid range of cells
or the name of a range in the spreadsheet. This argument applies only to
importing. Leave this argument blank to import the entire spreadsheet. When
you export to a spreadsheet, you must leave this argument blank. If you enter
a range, the export will fail.

Well, not quite true. You can use a sheet name in an export and it will
give the worksheet the name specified. If the file already exits, it will
add a new sheet with the name. If you use a space in a name, it will replace
it with an underscore.

Specifiying rows and columns does not work with an export, But it does with
an import; however if you created the sheet with a space in the name, it will
not find it. You have to use the underscore. Also, identifiy one cell will
not work. You have to give it an explict range of columns with or without a
row identifier SoL
And you can user either ! or $, so:
''Lease_Events$A3" - Invalid
''Lease_Events$A3:A3" - returns the one cell
''Lease_Events!A3:A3" - is the same
''Lease_Events$A:L" - Returns all rows for columns A through L

It appears you are trying to start your data in cell A3 of the worksheet.
The only way I know to do that would be to use automation to create a
workbook, give the worksheet a name, use the CopyFromRecordset method to load
the data beginning in A3, then save the workbook and quit the Excel
application.

Sorry for the wild goose chance, but I, like you, have not used this in a
long time.

--
Dave Hargis, Microsoft Access MVP

I changed the sheet name, although I've done this sort of thing with sheets
with spaces in their names before. Then I tried:

Range:="LeaseEvents!$A$3"

Now I get Run-time error '3125':

''Lease Events'$$A$3' is not a valid name.

I've been doing Excel/Access/VBA programming a long time. This is a rather
routine exercise, but I don't recollect ever seeing anything like this before.
A '!' is not a '$'.

I thought the bracketing would do it.
Have you tried with no delimiters at all?
This is a result of poor naming, that is using spaces in names. But, maybe
you don't have control over that.

Thanks for your help, Klaatu.

[quoted text clipped - 15 lines]

First of all, I didn't use the $ character. Why does it misinterpret a ! as a
$ character?