How do I connect to a excel workbook. The entire workbook not just one worksheet

[Michael Ziegler]

How do I connect to a excel workbook. The entire workbook not just one worksheet I need to connect to the workbook
and import information from the different worksheets into a corresponding shape in my drawing. I can successfully connect to
a worksheet with the following sub

Public Sub AddDataRecordset_Example()

    Dim strConnection As String
    Dim strCommand As String
    Dim strOfficePath As String
    Dim vsoDataRecordset As Visio.DataRecordset

    strOfficePath = Visio.Application.Path     
    strConnection = "Provider=Microsoft.ACE.OLEDB.12.0;" _
                       & "User ID=Admin;" _
                       & "Data Source=" + strOfficePath + "SAMPLES\1033\ORGDATA.XLS;" _
                       & "Mode=Read;" _
                       & "Extended Properties=""HDR=YES;IMEX=1;MaxScanRows=0;Excel 12.0;"";" _
                       & "Jet OLEDB:Engine Type=34;"

    strCommand = "SELECT * FROM [Sheet1$]"

    Set vsoDataRecordset = ActiveDocument.DataRecordsets.Add(strConnection, strCommand, 0, "Org Data")

End Sub

But this only allows me to connect to one worksheet. I like to be able to connect to the workbook and then
use a loop like this to execute commands or import data from the different worksheets

Sub WorksheetLoop()

         Dim WS_Count As Integer
         Dim I As Integer

         ' Set WS_Count equal to the number of worksheets in the active
         ' workbook.
         WS_Count = ActiveWorkbook.Worksheets.Count

         ' Begin the loop.
         For I = 1 To WS_Count

            ' Insert your code here.
            ' The following line shows how to reference a sheet within
            ' the loop by displaying the worksheet name in a dialog box.
            MsgBox ActiveWorkbook.Worksheets(I).Name

         Next I

      End Sub
Thanks

[Nikolay]

You can just repeat it several times
Means, first you add the connection to sheet1 (already done), then for sheet2 of the same workbook, then sheet3, etc.
Also, you don't really need code to do that, you can use the "Connect to data" button.

[Michael Ziegler]

Thank you I was able to do it with a lot less code just creating a variable for excel as an object and then using GetObject. Thanks

[OldSchool1948]

Here's a framework for how I open multiple worksheets

Code Select Expand

Public Sub openWorkbook()

On Error GoTo ErrorHandler

    Dim strWbName as string
    strWbName = "ProjectWorkbook.xlsx"
   
    Dim pathExcel As String
    pathExcel = Visio.ActiveDocument.Path & strWbName
           
    Dim xlApp As excel.Application
    Set xlApp = CreateObject("excel.application")
                           
    Dim xlWrkbook As excel.Workbook
    Set xlWrkbook = xlApp.Workbooks.Open(FileName:=pathExcel)
    xlApp.Visible = True
           
    Dim xlWrkSheet As excel.Worksheet
    Set xlWrkSheet = xlWrkbook.Sheets.Item(C_SHEETNAME_LISTS)

    With xlWrkSheet
   
         '  DO Stuff

    End With
           
    Set xlWrkSheet = xlWrkbook.Sheets.Item(C_SHEETNAME_CLOUD)

    With xlWrkSheet

        ' DO Stuff

    End With
   
exitHere:
   
    On Error GoTo 0
   
    Exit Sub

ErrorHandler:  ' Error-handling routine.

    ' Add error message

    GoTo exitHere

End Sub