using VBA, how do I get a ! into a string

[rirus via AccessMonster.com]

Using Access VBA I am importing an Excel Spreadsheet using, DoCmd.
TransferSpreadsheet acImportDelim, 8, strAccess, strExcel, True,
strSheetRange.

There are several worksheets in the workbook and I only want to import one
specific sheet. I found a help file that stated I can specifiy the Worksheet
by using a ! to seperate the Worksheet and the Range.

Example: strSheetRange = 'DataSheet3!A1220'

The problem: In the strSheetRange string the ! comes back as a $.
The question: How can get the ! to be recognized as a !? Is there a Chr$(??)
I can use to identify this character?


Thanks,

Rirus

 

[fredg]

Using Access VBA I am importing an Excel Spreadsheet using, DoCmd.
TransferSpreadsheet acImportDelim, 8, strAccess, strExcel, True,
strSheetRange.

There are several worksheets in the workbook and I only want to import one
specific sheet. I found a help file that stated I can specifiy the Worksheet
by using a ! to seperate the Worksheet and the Range.

Example: strSheetRange = 'DataSheet3!A1220'

The problem: In the strSheetRange string the ! comes back as a $.
The question: How can get the ! to be recognized as a !? Is there a Chr$(??)
I can use to identify this character?

Thanks,

Rirus

Why are you using single quotes?
What happens if you use double quotes instead of single quotes?
strSheetRange = "DataSheet3!A1220"
or...
Did you try concatenating the string:
strSheetRange = "DataSheet3" & "!" & "A1220"
Or try...
strSheetRange = "DataSheet3" & chr(33) & "A1220"

 

[rirus via AccessMonster.com]

I figured out what I was doing wrong...
It would help if it pointed to a workbook that had the worksheet I am trying
import... gheez! I hate stumbling over mole hills.

Thanks fredg for responding

Rirus

Using Access VBA I am importing an Excel Spreadsheet using, DoCmd.
TransferSpreadsheet acImportDelim, 8, strAccess, strExcel, True,

[quoted text clipped - 13 lines]

Rirus

Why are you using single quotes?
What happens if you use double quotes instead of single quotes?
strSheetRange = "DataSheet3!A1220"
or...
Did you try concatenating the string:
strSheetRange = "DataSheet3" & "!" & "A1220"
Or try...
strSheetRange = "DataSheet3" & chr(33) & "A1220"