Excel sheet order and ADO

I have the following function that moves through Excel sheets until it gets to the one I want to use in a sub with another source string.  It works good except for 1 problem.  It reads the sheets in the order that they were created, not the order that they are actually in according to where the tabs are at the bottom of the sheet.
For example:
I create a new Excel file.  It creates 3 sheets as always.  The function reads in the proper order (Sheet1, Sheet2, then Sheet3)

So...say I move sheet3 to the beginning so that it is first and save the file.  It still reads in the original order (Sheet1, Sheet2, then Sheet3)

So apparently Excel stores it's sheets as ADO ...would read them in the same order they are created.  I don't understand how that works, and I don't really need to, as long as I can get this function to read the sheets in the order that they are in visually...

Can anyone help with that?

Thanks much!

:-)
Albert

Function GetSheetsName(strFilename As String, intSheetNum As Integer) As String
 'Assume that intSheetNum is 3 in the example above
        Dim rs As ADODB.Recordset
        Dim cn As ADODB.Connection
        Dim Temp As String
        
        
        GetSheetsName = ""
        
        Set cn = New ADODB.Connection
 
        
        With cn
            .Provider = "MSDASQL"
            .ConnectionString = "Driver={Microsoft Excel Driver (*.xls)};" & _
            "DBQ=" & strFilename & "; ReadOnly=False;"
            .Open
        End With
        
        Set rs = cn.OpenSchema(adSchemaTables)
        
        For i = 1 To intSheetNum
             If rs.EOF Then Exit For
             GetSheetsName = Mid(rs.Fields("TABLE_NAME").Value, 1, Len(rs.Fields("TABLE_NAME")))
             Debug.Print GetSheetsName
             rs.MoveNext
        Next i
        
        rs.Close
        cn.Close
 
End Function

Open in new window

LVL 23
ahammarAsked:
Who is Participating?
 
GrahamSkanConnect With a Mentor RetiredCommented:
Tables in a database have no order of precedence, so ADO will not have a procedure to recognise one.
0
 
Chris BottomleyCommented:
Use the following syntax:

Dim ix As Integer
For ix = 1 To ThisWorkbook.Worksheets.Count
    Debug.Print Worksheets(ix).Name
Next

This will cycle throuh the first to last sheet, (visual order)) however they are organised.

Chris
0
 
Chris BottomleyCommented:
Sorry Al, didn't see the code snippet till i'd posted.  Need to look a bit harder!

Chris
0
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
ahammarAuthor Commented:
you're forgiven...this time....grins...


:-)
Albert
0
 
ahammarAuthor Commented:
For anyone who can still help here...I was wrong about the way it currently reads the sheets...it reads them in alphebitical order, not in the order they were created.  I still need it to read them in the order they are in visually...

:-)
Albert
0
 
mwolfe02Commented:
Albert,

Try the attached code.  If I understand what you are trying to accomplish, this should work for you.

HTH
-mike
Function GetSheetsName(strFilename As String, intSheetNum As Integer) As String
'Assume that intSheetNum is 3 in the example above'
    Dim rs As ADODB.Recordset
    Dim cn As ADODB.Connection
    Dim Temp As String
 
    Dim i As Integer, Found As Boolean
    Dim xl As Object, wb As Object, sh As Object
 
    Set xl = CreateObject("Excel.Application")
    Set wb = xl.Workbooks.Open(strFilename)
 
    GetSheetsName = ""
 
    Set cn = New ADODB.Connection
 
    With cn
        .Provider = "MSDASQL"
        .ConnectionString = "Driver={Microsoft Excel Driver (*.xls)};" & _
                            "DBQ=" & strFilename & "; ReadOnly=False;"
        .Open
    End With
 
    Set rs = cn.OpenSchema(adSchemaTables)
 
    Found = False
    Do Until rs.EOF Or Found
        For Each sh In wb.worksheets
            If Replace(rs!table_name, "$", "") = sh.codename Then
                If sh.Index = intSheetNum Then
                    Found = True
                    'I don't know which one of these you actually need to use '
                    GetSheetsName = rs!table_name & vbCrLf & _
                                    sh.codename & vbCrLf & _
                                    sh.Name
                    Exit For
                End If
            End If
        Next sh
 
        rs.MoveNext
    Loop
 
    rs.Close
    cn.Close
    wb.Close
    xl.Quit
    Set xl = Nothing
    Set wb = Nothing
End Function

Open in new window

0
 
ahammarAuthor Commented:
Hi Mike
Thanks for that.  That would probably work in most cases, but the sad part is I don't think I can use the Excel object because Excel is not installed.  I will try it later when I get back to the computer I'm doing this on, but I'm pretty sure it won't work without Excel installed.  That's why I have to read cell values the way I am...I can read cell values in fine, but I can't figure out how to loop through the sheets in the order that they are in visually.  The function I posted loops through them, but it does it alphabetically which does me no good.

Thanks again...

:-)
Albert
0
 
ahammarAuthor Commented:
GrahamSkan,
Thanks...I'm afraid you are probably right because I have tried everything I can think of.  Let me leave this open for a bit longer just in case, but like I said, I'm pretty sure you are right about that.  I'll be back...thanks again...atleast I can quit trying anyway now that you have confirmed what I was already thinking...

:-)
Albert
0
 
ahammarAuthor Commented:
Thanks for giving me that info...now I know why I can't get it to work...
0
All Courses

From novice to tech pro — start learning today.