[RESOLVED]Using Ado To Access Excel Files

I am trying to read an Excel worksheet into an ADO recordset so that I can read the values from the Excel file. However, I cant seem to get this last part to work.

I have tried everything I know - even searching the internet - but I cant seem to figure out why when I try to execute this line in the code

VB Code:

oRs.Open sRequest, sCnn, adOpenDynamic, adLockOptimistic

I always seem to get the following error:

The Microsoft Jet database engine could not find the object 'Sheet1$'. Make sure that the object exist and that its name and path name correctly.

I know that Sheet1 exist in the Excels File Path.

Any other ideas why this error is occurring? - Thanks In Advance :afrog:

VB Code:

Private Sub Form_Load()
    
    Dim sPath As String
    Dim oRs as ADODB.Recordset
 
    sPath = App.Path & "\Las Animas Text.xls"
    
    Set oRs = GetADOExcelRecordSet(sPath)
    
End Sub
 
 
Private Function GetADOExcelRecordSet(ByVal Path As String, _
                Optional ByVal Headers As Boolean = True) As Recordset
               
    Dim sCnn As String
    Dim sWorksheet As String
    Dim sRequest As String
    
    sWorksheet = "Sheet1"
    
    Dim oCnn As ADODB.Connection
    Set oCnn = New ADODB.Connection
    Dim oRs As Recordset
    Set oRs = New ADODB.Recordset
    
    sCnn = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
            "Data Source=" & Path & ";" & _
            "Extended Properties=""Excel 8.0;HDR=No" & _
            IIf(Headers, "Yes", "No") & """"
            
    'Read a sheet:
 
    sRequest = "SELECT * FROM [" & sWorksheet & "$]"
    oRs.Open sRequest, sCnn, adOpenDynamic, adLockOptimistic
    Set GetADOExcelConnection = oRs
    
End Function

You need to inser the "`" character surrounding your sheet name. ;)

Oh Lordy I guess that fixed one problem - Thanks

Now I am getting an Syntax Error in Querey : Incomplete Querey Clause Error

Did you place those characters around the sheetname for your select statement too? Also, add the semicolon at the end of your query.

VB Code:

sRequest = "SELECT * FROM [`" & sWorksheet & "$`];"

I forgot the semicolon :D

I have exactly what you suggested above but now... I have a Syntax Error from FROM Clause

I must be too tired to program tonight.

Whats the Debug.Print value for sRequest ?

SELECT * FROM [`Sheet1$`];

Try it without the semicolon and which version of Excel are you running?.

Running Excel 2002
Same error without semicolon

Ok, I got it! You havent opened your connection object. Then you are trying to open a rs with a Cnn
that is not opened.

Office XP is 2002, and is version 10.0, not 8.0

VB Code:

sCnn = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
            "Data Source=" & Path & ";" & _
            "Extended Properties=""[COLOR=Red]Excel 8.0[/COLOR];HDR=No" & _
            IIf(Headers, "Yes", "No") & """"
                'Read a sheet:

Yes but thats not an issue right now. If your not using and connectiong to the workbook then you can have "Excel 888.0;..." and
it wont make a difference. It will work on Excel 2003 Btw. ;)

I know your not going to like this but that still doesn't work. :eek2:

You were correct about one thing I did need to open my connection string so good catch there!

VB Code:

oCnn.Open sCnn
    
'Read a sheet:
sRequest = "SELECT * FROM [`" & sWorksheet & "$`]"
oRs.Open sRequest, oCnn, adOpenDynamic, adLockOptimistic

Results in the same error...

Do Not Worry about this too much - I am going to sleep on it tonight - but thank you for your help! :afrog:

Ok, I know its all there. Just missing something minor. Getting late here too.

Post tomorrow ;)

Try...

VB Code:

oRs.Open sRequest, oCnn, adOpenStatic, adLockReadOnly, adCmdText

I don't know why I am having such difficulties with this - it doen't seem like it should be this hard to connect and open a sheet. hehe

When I try the code you suggest I am getting a

Error:Command Text was not set for the command object.

Hmm, maybe there is some code of mine that would help you in these two threads.

http://www.vbforums.com/showthread.p...ight=ADO+Excel
http://www.vbforums.com/showthread.p...ight=ADO+Excel

Perhaps I will try to connect through ODBC Connection instead.

Thank you for your help thus far.

If this is not a data sensitive workbook, could you zip it up and attach it so I can test with your actuall workbook? I'm not able
to create any errors. :( :lol: That sounded funny. ;)

Let me scan through the excel sheets to check if there is any sensitive data. I dont believe there is any however the current excel file is massive.

You could just attach a copy of the workbook but delete all sheets but sheet1. ;)

I have attached the TestFile.xls into the .rar file. Eventaully I am just trying to read all of row 5.


I am attempting to read the code with the following code.

VB Code:

Private Sub Form_Load()
    
    Dim sPath As String
    Dim oRs as ADODB.Recordset
 
    sPath = App.Path & "\TestFile.xls"
    
    Set oRs = GetADOExcelRecordSet(sPath)
    
End Sub
 
 
Private Function GetADOExcelRecordSet(ByVal Path As String, _
                Optional ByVal Headers As Boolean = True) As Recordset
               
    Dim sCnn As String
    Dim sWorksheet As String
    Dim sRequest As String
    
    sWorksheet = "Sheet1"
    
    Dim oCnn As ADODB.Connection
    Set oCnn = New ADODB.Connection
    Dim oRs As Recordset
    Set oRs = New ADODB.Recordset
    
    sCnn = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
            "Data Source=" & Path & ";" & _
            "Extended Properties=""Excel 8.0;HDR=No" & _
            IIf(Headers, "Yes", "No") & """"
            
    'Read a sheet:
 
    sRequest = "SELECT * FROM [" & sWorksheet & "$]"
    oRs.Open sRequest, sCnn, adOpenDynamic, adLockOptimistic
    Set GetADOExcelConnection = oRs
    
End Function

Can you attach a .zip instead. I dont have WinRar. :)

Your still not opening a ADODB connection and placing the reverse single quote around the sheet name. ;)

VB Code:

Private Sub Form_Load()
    
    Dim sPath As String
    Dim oRs as ADODB.Recordset
 
    sPath = App.Path & "\TestFile.xls"
    
    Set oRs = GetADOExcelRecordSet(sPath)
    
End Sub
 
 
Private Function GetADOExcelRecordSet(ByVal Path As String, _
                Optional ByVal Headers As Boolean = True) As Recordset
               
    Dim sCnn As String
    Dim sWorksheet As String
    Dim sRequest As String
    
    sWorksheet = "Sheet1"
    
    Dim oCnn As ADODB.Connection
    Set oCnn = New ADODB.Connection
    Dim oRs As Recordset
    Set oRs = New ADODB.Recordset
    
    sCnn = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
            "Data Source=" & Path & ";" & _
            "Extended Properties=""Excel 8.0;HDR=No" & _
            IIf(Headers, "Yes", "No") & """"
            
    oCnn.ConnectionString = sCnn
    oCnn.Open
 
    'Read a sheet:
 
    sRequest = "SELECT * FROM [`" & sWorksheet & "$`]"
    oRs.Open sRequest, oCnn, adOpenDynamic, adLockOptimistic
    Set GetADOExcelConnection = oRs
    
End Function

Dave's conversion service. :)

Too bad that thread was toasted. I cant remember that post you made. It would be good for your signature. ;)

Let me see what going on here now, but the code I posted whould work on this so hopefully not too long tofix.

Done! The function name was not matching to its signature.

GetADOExcelRecordSet <> GetADOExcelConnection

It didnt know where to return it to since it didnt match.

Also, no need for the reverse single quotes in this situation.

I added my code I already posted to establish the connection too. ;)

VB Code:

Option Explicit
 
Private Sub Form_Load()
    
    Dim sPath As String
    Dim oRs As ADODB.Recordset
 
    sPath = App.Path & "\TestFile.xls"
    
    Set oRs = GetADOExcelRecordSet(sPath)
    oRs.MoveFirst
    oRs.Move 5
    MsgBox oRs.Fields(1).Value '6th row, column B
    
End Sub
 
 
Private Function [b]GetADOExcelRecordSet[/b](ByVal Path As String, _
                Optional ByVal Headers As Boolean = True) As Recordset
               
    Dim sCnn As String
    Dim sWorksheet As String
    Dim sRequest As String
    
    sWorksheet = "Sheet1"
    
    Dim oCnn As ADODB.Connection
    Set oCnn = New ADODB.Connection
    Dim oRs As Recordset
    Set oRs = New ADODB.Recordset
    
    sCnn = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
            "Data Source=" & Path & ";" & _
            "Extended Properties=""Excel 8.0;HDR=No" & _
            IIf(Headers, "Yes", "No") & """"
            
    oCnn.ConnectionString = sCnn
    oCnn.Open
 
    'Read a sheet:
 
    sRequest = "SELECT * FROM [" & sWorksheet & "$]"
    oRs.Open sRequest, oCnn, adOpenDynamic, adLockOptimistic
    Set [b]GetADOExcelRecordSet[/b] = oRs
    
End Function

Opps I sent you the wrong code... I was screwing around with the code when I copied paste the wrong thing. In the orgininal code the function call and function have the correct names.

So I thought - but I will double check my code at lunch.

But even if its not the correct return signature I believe it gives me the error before I even try to return it.

VB Code:

oRs.Open sRequest, oCnn, adOpenDynamic, adLockOptimistic

On this line of code it blows up on me giving me that Error: Syntax Error in FROM Clause.

Let me regroup after lunch - I will get back to you I sent you the wrong snippet of code and that will give me time to try your suggestion.

Well I guess that was my problem - Thanks Alot your so smart :frog:

Thanks :blush: but it just takes time and experience. I have done this many times in my apps, passing ADO recordsets back and forth so I can do it blindfolded practically. :D