ADO and sheet name with spaces

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am having problems with retreiving data from a worksheet where its name
contains spaces.

SELECT * FROM [<SourceSheet>$A1:H100];

When <SourceSheet> is "My Sheet", I get the message "The Microsoft Jet
database engine could not find the object...". I modified source sheet to
look like 'My Sheet', but that didn't work either.

What is the proper syntax for a source sheet containing spaces?

M
 
This works for me:

szConnect = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\Sales.xls;" & _
"Extended Properties=Excel 8.0;"
szSQL = "SELECT * FROM [My Sheet$A1:E10]"

Set rsData = New ADODB.Recordset
rsData.Open szSQL, szConnect, adOpenForwardOnly, _
adLockReadOnly, adCmdText

--
Jim Rech
Excel MVP
|I am having problems with retreiving data from a worksheet where its name
| contains spaces.
|
| SELECT * FROM [<SourceSheet>$A1:H100];
|
| When <SourceSheet> is "My Sheet", I get the message "The Microsoft Jet
| database engine could not find the object...". I modified source sheet to
| look like 'My Sheet', but that didn't work either.
|
| What is the proper syntax for a source sheet containing spaces?
|
| M
 
Jim Rech said:
I am having problems with retreiving data from a worksheet where its name
contains spaces.

SELECT * FROM [<SourceSheet>$A1:H100];

When <SourceSheet> is "My Sheet", I get the message "The Microsoft Jet
database engine could not find the object..."
Click to expand...

This works for me:

szConnect = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\Sales.xls;" & _
"Extended Properties=Excel 8.0;"
szSQL = "SELECT * FROM [My Sheet$A1:E10]"

Set rsData = New ADODB.Recordset
rsData.Open szSQL, szConnect, adOpenForwardOnly, _
adLockReadOnly, adCmdText
Click to expand...

I'm wondering if the OP has a Chr$(36) or a Chr$(39) character in the
sheet name. They really make things tricky <g>.

Jamie.

--
 
The only difference that I can see is that my connect string has the
following in the extended properties: ;HDR=NO;IMEX=1'. The sql looks just
like yours.

Looks like I'll have to keep digging.

Mike
 
Back
Top