[RESOLVED]Using Ado To Access Excel Files

[Jumpercables]

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

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

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

[RobDog888]

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

[Jumpercables]

Oh Lordy I guess that fixed one problem - Thanks

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

[RobDog888]

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

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

[Jumpercables]

I forgot the semicolon

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.

[RobDog888]

Whats the Debug.Print value for sRequest ?

[Jumpercables]

SELECT * FROM [`Sheet1$`];

[RobDog888]

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

[Jumpercables]

Running Excel 2002
Same error without semicolon

[RobDog888]

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.

[dglienna]

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

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:

[RobDog888]

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.

[Jumpercables]

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

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

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!

[RobDog888]

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

Post tomorrow

[RobDog888]

Try...

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

[Jumpercables]

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.

[RobDog888]

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

[Jumpercables]

Perhaps I will try to connect through ODBC Connection instead.

Thank you for your help thus far.

[RobDog888]

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. That sounded funny.

[Jumpercables]

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.

[RobDog888]

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

[Jumpercables]

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.

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

[RobDog888]

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

[RobDog888]

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

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

[dglienna]

Dave's conversion service.

[RobDog888]

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.

[RobDog888]

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.

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

[Jumpercables]

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.

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.

[Jumpercables]

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.

[Jumpercables]

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

[RobDog888]

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