• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1647
  • Last Modified:

Error 80040e21... rs.Open "SELECT ...

Dim cn As ADODB.Connection
dim rs As ADODB.Recordset

Set cn = New ADODB.Connection
    cn.Open "DRIVER={Microsoft Excel Driver (*.xls)};DriverId=790;ReadOnly=True;" & "DBQ=" & "c:\book1.xls" & ";"
Set rs = New ADODB.Recordset

'****   Following line errors:  ODBC driver does not support the requested properties*****

    rs.Open "SELECT * FROM [SHEET1]", cn, adOpenForwardOnly, adLockReadOnly, adCmdText
    While Not rs.EOF
        MsgBox rs(0)
    rs.MoveNext
    Wend
    rs.Close
Set rs = Nothing
    cn.Close
Set cn = Nothing

Why am I getting this error?
0
Mike Eghtebas
Asked:
Mike Eghtebas
3 Solutions
 
Mike EghtebasDatabase and Application DeveloperAuthor Commented:
Well, $ was missing from worksheet name.  I didn't think it is mandatory to have it there.

Mike
0
 
aelatikCommented:
You can find more info here on select statements for Excel sheets like selecting within CELL range's etc.

http://support.microsoft.com/kb/257819/EN-US/
0
 
EgoreCommented:
You may want to look at this newsgroup thread (particularly the last two posts):

http://groups.google.com/groups?hl=en&lr=&safe=off&th=7f28c50074fcaeb2&seekm=3d65a4c9%241%40dnews.tpgi.com.au&frame=off

I'm not certain if that URL is permanent, but hopefully it is...
0
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
Mikal613Commented:
rs.Open "SELECT * FROM [SHEET1]", cn, 3, 2

TRy this instead
0
 
Mikal613Commented:
rs.Open "SELECT * FROM [SHEET1$]", cn, 3, 2

I mean this sorry!
0
 
Mike EghtebasDatabase and Application DeveloperAuthor Commented:
0

Featured Post

The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now