none
[ADO Excel] How to use ADO retrieve Excel Worksheet name and some information RRS feed

All replies

  • To retrieve Excel worksheet name and total count of sheets is by Excel.Application, but not by AdoDb:

    Dim mExcel as new Excel.Application, wb as Workbook, ws as worksheet, YrXlsFilNam as string
    YrXlsFilNam = "...."
    set mExcel.workbooks.open(YrXlsFilNam)
    debug.print wb.worksheets.count
    for each ws in wb.worksheets
    debug.print ws.name
    next

    Friday, September 8, 2006 10:00 AM
  • Mr Choy,

    Please have a look at my blog. I have written a post on how to retrieve the worksheet names and count using ADOX.

    Sunday, September 10, 2006 1:52 PM
  • Hello,

    Well, I managed to solve it by using an ADODB object
    thanks to "Desertphile" in newsgroup 'comp.programming'
    He posted the code below.

    Just copy the program below in a VB6 project and place the missing objects on your form and then it will show itself.

    Regards,

    Henk



    Option Explicit

    Private Sub btnExample_Click()
        Dim cn As ADODB.Connection, c As Integer, d As Integer
        Dim rsT As ADODB.Recordset

        MousePointer = 11
        DoEvents

        Set cn = New ADODB.Connection
        With cn
            .Provider = "Microsoft.Jet.OLEDB.4.0"
            .ConnectionString = "Data Source=C:\My
    Documents\productmaster.xls;" & "Extended Properties=Excel 8.0;"
            .CursorLocation = adUseClient
            .Open
        End With

        Set rsT = New ADODB.Recordset

        rsT.CursorLocation = adUseClient
        rsT.Open "SELECT * from [Sheet1$A3:U5861]", cn, adOpenStatic

        c = rsT.RecordCount

        If c > 0 Then rsT.MoveFirst
        Open "C:\smp\pm.txt" For Output As #1

        For d = 1 To c
            If Not IsNull(rsT.Fields(0).Value) Then
                List2.AddItem rsT.Fields(0).Value
            Else
                List2.AddItem ""
            End If
            rsT.MoveNext
        Next
        rsT.Close
        cn.Close
        Close

        MousePointer = 0
        DoEvents
    End Sub

    Private Sub Command1_Click()
        Dim cn As ADODB.Connection, c As Integer, d As Integer
        Dim rsT As ADODB.Recordset

        Set cn = New ADODB.Connection
        With cn
            .Provider = "Microsoft.Jet.OLEDB.4.0"
            '.ConnectionString = "Data Source=C:\desertphile\sundata.xls;"
    & "Extended Properties=Excel 8.0;"
            .ConnectionString = "Data Source=C:\My
    Documents\productmaster.xls;" & "Extended Properties=Excel 8.0;"
            .CursorLocation = adUseClient
            .Open
        End With

        Set rsT = New ADODB.Recordset
        'rsT.Open "SELECT * from [Print-This$A1:A7]", cn, adOpenStatic
        'rsT.Open "SELECT * from [Print-This$]", cn, adOpenStatic

        rsT.CursorLocation = adUseClient
        rsT.Open "SELECT * from [Sheet1$A3:E373]", cn, adOpenStatic

        c = rsT.RecordCount

        If c > 0 Then rsT.MoveFirst

        For d = 1 To c
            If Not IsNull(rsT.Fields(0).Value) Then
                List2.AddItem rsT.Fields(0).Value
            Else
                List2.AddItem ""
            End If
            rsT.MoveNext
        Next
        rsT.Close
        cn.Close

    End Sub

    Private Sub Form_Load()

        Dim cn As ADODB.Connection
        Dim rsT As ADODB.Recordset
        Dim intTblCnt As Integer, intTblFlds As Integer
        Dim strTbl As String
        Dim rsC As ADODB.Recordset
        Dim intColCnt As Integer, intColFlds As Integer
        Dim strCol As String
        Dim t As Integer, c As Integer, f As Integer

        Set cn = New ADODB.Connection
        With cn
            .Provider = "Microsoft.Jet.OLEDB.4.0"
            '.ConnectionString = "Data Source=C:\job\rancho.xls;" &
    "Extended Properties=Excel 8.0;"
            '.ConnectionString = "Data Source=C:\desertphile\sundata.xls;"
    & "Extended Properties=Excel 8.0;"
            '.ConnectionString = "Data Source=C:\My
    Documents\productmaster.xls;" & "Extended Properties=Excel 8.0;"
            .ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data
    Source=c:\test\db1.mdb;"
            .CursorLocation = adUseClient
            .Open
        End With

        Set rsT = cn.OpenSchema(adSchemaTables)
        intTblCnt = rsT.RecordCount
        intTblFlds = rsT.Fields.Count
        List1.AddItem "Tables:  " & intTblCnt
        List1.AddItem "--------------------"
        For t = 1 To intTblCnt
            strTbl = rsT.Fields("TABLE_NAME").Value
            List1.AddItem vbTab & "Table #" & t & ":    " & strTbl
            List1.AddItem vbTab & "--------------------"
            For f = 0 To intTblFlds - 1
                List1.AddItem vbTab & rsT.Fields(f).Name & _
                vbTab & rsT.Fields(f).Value
            Next
            List1.AddItem "--------------------"
            Set rsC = cn.OpenSchema(adSchemaColumns, Array(Empty, Empty,
    strTbl, Empty))
            intColCnt = rsC.RecordCount
            intColFlds = rsC.Fields.Count
            For c = 1 To intColCnt
                strCol = rsC.Fields("COLUMN_NAME").Value
                List1.AddItem vbTab & vbTab & "Column #" & c & ": " &
    strCol
                List1.AddItem vbTab & vbTab & "--------------------"
                For f = 0 To intColFlds - 1
                    List1.AddItem vbTab & vbTab & rsC.Fields(f).Name & _
                    vbTab & rsC.Fields(f).Value & Str$(f)
                Next
                List1.AddItem vbTab & vbTab & "--------------------"
                rsC.MoveNext
            Next
            rsC.Close
            List1.AddItem "--------------------"
            rsT.MoveNext
        Next
        rsT.Close
        cn.Close
    End Sub
    Tuesday, September 19, 2006 11:01 AM
  • Dear Johnson,

    I have used the above in my excel file by copying this into visual basic editor.

    Actually I want the all woksheets name of my file in a sheet.

    Suppose if i have one file named c:\a.xls having four different sheets. Can you please tell me how to apply this code into the file.

    I am new to this thing please advice me in full, means i have to just copy the thing which you will give me.

    Please write sub () and endsub in this code also so that i can understand in full. Because I have already tried the above but error is coming

    Sub a()

    Dim mExcel As New Excel.Application, wb As Workbook, ws As Worksheet, YrXlsFilNam As String
    YrXlsFilNam = "c:\a.xls"
    set mExcel.workbooks.open(YrXlsFilNam)  (Error is coming in this line)
    Debug.Print wb.Worksheets.Count
    For Each ws In wb.Worksheets
    Debug.Print ws.Name
    Next

    End Sub

    Regards,

     

    Kamal

     

     

     

     

     

    • Proposed as answer by swzine Tuesday, March 2, 2010 3:27 PM
    Wednesday, September 20, 2006 1:04 PM
  • Dear Johnson,

    I have used the above in my excel file by copying this into visual basic editor.

    Actually I want the all woksheets name of my file in a sheet.

    Suppose if i have one file named c:\a.xls having four different sheets. Can you please tell me how to apply this code into the file.

    I am new to this thing please advice me in full, means i have to just copy the thing which you will give me.

    Please write sub () and endsub in this code also so that i can understand in full. Because I have already tried the above but error is coming

    Sub a()

    Dim mExcel As New Excel.Application, wb As Workbook, ws As Worksheet, YrXlsFilNam As String
    YrXlsFilNam = "c:\a.xls"
    set mExcel.workbooks.open(YrXlsFilNam)  (Error is coming in this line)
    Debug.Print wb.Worksheets.Count
    For Each ws In wb.Worksheets
    Debug.Print ws.Name
    Next

    End Sub

    Regards,

     

    Kamal

     

     

     

     

     

    I know this is a very old post, but I also got the same error, and the code below will work in Access.

    Dim mExcel As New Excel.Application, wb As Workbook, ws As Worksheet, YrXlsFilNam As String
    YrXlsFilNam = "c:\a.xls"
    Set wb = mExcel.Workbooks.Open(YrXlsFilNam)
    Debug.Print wb.Worksheets.Count
    For Each ws In wb.Worksheets
    Debug.Print ws.Name
    Next
    • Proposed as answer by swzine Tuesday, March 2, 2010 3:29 PM
    Tuesday, March 2, 2010 3:29 PM
  • Hello Derek,

    Please, where am I to find your blog ?

    Regards.

    Daniel
    Tuesday, March 2, 2010 6:38 PM
  • Daniel,

    try looking here  www.dsmyth.net/wiki 
    FREE
    DEVELOPER TOOLS     CODE     PROJECTS

    DATABASE CODE GENERATOR
    DATABASE / GENERAL  APPLICATION TUTORIAL
    Upload Projects to share or get help on and post the generated links here in the forum
    www.srsoft.us
    Tuesday, March 2, 2010 11:56 PM
  • Thanks Derek,

    It's down by now. I'll try later on.

    Regards.

    Daniel
    Wednesday, March 3, 2010 6:32 PM