PDA

View Full Version : Runtime error



gopi09_m
01-18-2012, 09:33 AM
Hi

The following program throws below error :at step ( Set rs = db.OpenRecordset("SELECT * FROM [Sheet1$A1:A10]"))

Run-time error '3011'.The microsoft jet database engine could not find the object "Sheet1$A1:A10'.make sure the object exists and tha you spell its name and the path name correctly.

Sub GetWorksheetData()
'Dim db As DAO.Database,
Dim rs As DAO.Recordset

'MsgBox ActiveSheet.UsedRange.Rows.Count

Set db = OpenDatabase("CompareExcel", False, False, "Excel 8.0;") ' write
On Error GoTo 0
If db Is Nothing Then
MsgBox "Can't find the file!", vbExclamation, ThisWorkbook.Name
Exit Sub
End If

Set rs = db.OpenRecordset("SELECT * FROM [Sheet1$A1:A10]")
While Not (rs.EOF)
rs.MoveNext
MsgBox rs.Fields("ColA")

Wend


rs.Close
Set rs = Nothing
db.Close
Set db = Nothing
End Sub

Any help would be greatly appreciated.
Note:CompareExcel is the current workbook/excel file name.

Aflatoon
01-18-2012, 09:44 AM
Assuming you have a Sheet1, I would expect you to have to pass the full path and filename to the OpenDatabase method.

gopi09_m
01-18-2012, 09:53 AM
Thanks.It worked.
But doesn't it work if we just give the current workbook name from where we are running the program?

Aflatoon
01-18-2012, 09:56 AM
Clearly not. :)