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
"mwaller" wrote in message
...
|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" wrote ...

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

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


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

"Jim Rech" wrote:

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
"mwaller" wrote in message
...
|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