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

Read Excel spreadsheet from VB6

Hi,
I am trying to read an Excel spreadsheet from VB6 (not .NET !!) Is it possible to work with Excel in the same way as I would work with an Access database ? like below...

Some links would be fantasic.

    Set objConn = CreateObject("ADODB.Connection")
    objConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
               "Data Source=" & "C:\stats.xls" & ";" & _
               "Extended Properties=""Excel 8.0;"""
       
        SQLString = "SELECT * FROM sheet1 blah, blah, blah"
        Set objRS = Server.CreateObject("ADODB.Recordset")
        objRS.Open SQLString, objConn
               
        While Not objRS.EOF
            Col1 = objRS("Column1")
            Col2 = objRS("Column2")
            objRS.Movenext
        Wend
        objRS.Close
        Set objRS = Nothing
    objConn.Close
    Set objConn = Nothing
0
gjok
Asked:
gjok
  • 2
1 Solution
 
vinnyd79Commented:
Yes,you can use ADO to read an excel file:

Private Sub Command1_Click()
Set objConn = CreateObject("ADODB.Connection")
objConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
               "Data Source=" & "C:\stats.xls" & ";" & _
               "Extended Properties=""Excel 8.0;"""
                 
SQLString = "Select * from [Sheet1$]"
Set objRS = CreateObject("ADODB.Recordset")
objRS.Open SQLString, objConn
               
While Not objRS.EOF
    Col1 = objRS(0) ' col A
    Col2 = objRS(1) ' col B
    objRS.MoveNext
Wend
objRS.Close
Set objRS = Nothing
objConn.Close
Set objConn = Nothing

End Sub
0
 
vinnyd79Commented:
If your excel file does not have headers in row 1 Then use this connection string  instead:

objConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
               "Data Source=" & "C:\stats.xls" & ";" & _
               "Extended Properties=" & """Excel 8.0;HDR=No"""
0
 
cquinnCommented:
How To Use ADO with Excel Data from Visual Basic or VBA

http://support.microsoft.com/default.aspx?scid=kb;EN-US;257819
0
 
gjokAuthor Commented:
Thanks - great link.
0

Featured Post

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

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