Recommended method for addressing "Too many line continuations" error

[WCTICK]

I am attempting to create a macro that uses the Visio Org Chart wizard command line interface to create a chart from an Excel spreadsheet.

When running the wizard, I want to use parameters to specify the manager that should be at the top of each page and also specify the pagename, based on the manager name.

Our organization has multiple sites and the Org Charts can sometimes consist of 80 or more pages.

I thought I would copy data for the Org Chart Wizard /Pages parameter that I had created in the Excel spreadsheet into the macro as shown below.
The problem I encountered was the error, "Too many line continuations", since there were over 50 page entries and I made each one a separate line.

Does anyone have a recommended approach or best practice to get around this issue?  Is it possible to have the /page data lines contained in an external file that is read?  Or, is there a simpler method?

I appreciate any input as I am not very proficient with VBA coding and methods.  Thank you.


    'Add org chart arguments, including Excel filename
    sOrgWizArgs = _
          "/FILENAME=" & sFullPath _
        & "/NAME-FIELD=""Employee Name"" " _
        & "/MANAGER-FIELD=""Reports to - Position"" " _
        & "/UNIQUE_ID=""Position Number"" " _
        & "/DISPLAY-FIELDS=""Employee Name"",""Job Code Title - Position"",""Position Number"" " _
     ' COPY EXCEL TEMPLATE DATE STARTING IN LINE BELOW
&"/PAGES=""SMITH,ANNETTE"" ""1"" PAGENAME=""Director Smith     ""," _
&" ""BANKS,RICHARD"" ""2"" PAGENAME=""Asst Director Banks         ""," _
&" ""SANDERS, SALLY M"" ""2"" PAGENAME=""DD Sanders    ""," _
&" ""LAMBERT,ANNE"" ""2"" PAGENAME=""DD CIO Lambert              ""," _
&" ""LEE,THOMAS"" ""2"" PAGENAME=""CIO 2         ""," _
&" ""JENKINS, ROBERT"" ""2"" PAGENAME=""CIO - Compliance            ""," _
&" ""GRAY,ARNOLD."" ""2"" PAGENAME=""DD Legal Gray ""," _
&" ""MOORE, MALLORY"" ""2"" PAGENAME=""Legal -2      ""," _
&" ""PIERCE,ALICE"" ""2"" PAGENAME=""Legal-3       ""," _
&" ""ADAMS,ALLEN"" ""2"" PAGENAME=""Adult Detention             ""," _
&" ""WITT,KEVIN"" ""2"" PAGENAME=""PM - Wittrup  ""," _
&" ""WILLIAMS,WESLEY"" ""2"" PAGENAME=""IT Administrator Williams   ""," _
.
.
.
&" ""LONG,LARRY"" ""2"" PAGENAME=""OCSS - 2      "" " _
& "/SYNC-ACROSS-PAGES " _
& "/HYPERLINK-ACROSS-PAGES "

[Thomas Winkel]

I have no idea about Org Chart wizard, but try this:

Code Select Expand


sOrgWizArgs = "A"
sOrgWizArgs = sOrgWizArgs & "B"
sOrgWizArgs = sOrgWizArgs & "C"


[wapperdude]

Not sure this addresses your case, but it may have some "inspirational" "Aha!".  From David Parker:  https://bvisual.net/2009/02/24/creating-an-org-chart-without-the-org-chart-wizard/

[wapperdude]

Played around a bit.  Came across some code, see below.  Basically, the code loops thru the Excell file, thus, no need to have excessively long code lines.

Excel test file attached.  This will create a 4 page Org Chart.  Here's the code to run from within Visio...

Code Select Expand


Sub CreateOrgChartFromVBA()
    Dim objAddOn As Object
    Dim orgWizArgs As String
    Dim visExcelFile As String
    Dim topLevel As String  ' Specify the top-level manager's name or ID

    ' Initialize Visio application
    Set objAddOn = Visio.Addons.ItemU("OrgCWiz")

    ' Specify your Excel file path
    visExcelFile = "C:\Users\wappe\OneDrive\Documents\Charlie Obs6 OrgCht R1.xlsx"

    ' Customize the wizard arguments
    orgWizArgs = "/FILENAME=" & visExcelFile & _
                 " /NAME-FIELD=Name /MANAGER-FIELD=Reports_To " & _
                 "/PAGES=" & topLevel & _
                 "/DISPLAY-FIELDS=Name,Title /SYNC-ACROSS-PAGES /HYPERLINK-ACROSS-PAGES"

    ' Run the Org Chart Wizard
    objAddOn.Run ("/S-ARGSTR " & orgWizArgs)
    objAddOn.Run ("/S-RUN")
End Sub


[wapperdude]

Ooops.  Forgot attachment.

Plus, here's code modification.  Previous version hardcoded the path/filename.  This version opens window to search for file.  Identify/select the file, and hit OK to close window.

Code Select Expand


Sub CreateOrgChartFromVBA()
    Dim objAddOn As Object
    Dim orgWizArgs As String
    Dim visExcelFile As String
    Dim topLevel As String  ' Specify the top-level manager's name or ID
    Dim XlApp As Object
    Dim docPath As Variant
   

    ' Initialize Visio application
    Set objAddOn = Visio.Addons.ItemU("OrgCWiz")

    ' Specify your Excel file path
'*** In the npopup window, select/highlight desired file
'*** then HIT OK.

    docPath = ActiveDocument.Path                   'This is the default search path
    Set XlApp = CreateObject("Excel.Application")   'This is dummy.  FileDialog doesn't work with Visio directly.

' Find, select, and insert file:
    With XlApp.FileDialog(msoFileDialogFilePicker)
        .Filters.Clear
        .Filters.Add "Exel Files", "*.xls, *.xlsx"
        .InitialFileName = docPath
        .Show
        visExcelFile = .SelectedItems(1)
    End With
    XlApp.Quit

    ' Customize the wizard arguments
    orgWizArgs = "/FILENAME=" & visExcelFile & _
                 " /NAME-FIELD=Name /MANAGER-FIELD=Reports_To " & _
                 "/PAGES=" & topLevel & _
                 "/DISPLAY-FIELDS=Name,Title /SYNC-ACROSS-PAGES /HYPERLINK-ACROSS-PAGES"

    ' Run the Org Chart Wizard
    objAddOn.Run ("/S-ARGSTR " & orgWizArgs)
    objAddOn.Run ("/S-RUN")
End Sub


[WCTICK]

Fantastic!  Exactly what I was looking/hoping for.  Thank you.