Search code examples
vbaautomationvisioorgchart

Error with OrgChart Wizard exporting to Visio via VBA

I am trying to use the OrgChart Wizard for exporting a Visio drawing from Visual Studio via VBA.

First, we set up the page config params using:

'/PAGES=<top employee> <num levels> PAGENAME=<pagename>,<top employee> <num levels> PAGENAME=<pagename>...
 strPageConfig = " /PAGES=" & ListBox1.SelectedItem & " " & lvlNum & " PAGENAME=cleanedData"

We then store the excel file in a valiable called visExcelFile. Next, we created the OrgChart Wizard:

visApp = CreateObject("Visio.Application")
visApp.visible = False
objAddOn = visApp.Addons.ItemU("OrgCWiz")
objAddOn.Run("/S-INIT")

Add the Org Chart Arguments and run the Wizard:

orgWizArgs = " /FILENAME=" & visExcelFile & " /NAME-FIELD=Name" &
        " /MANAGER-FIELD=Reports_To" & strPageConfig &
        "/DISPLAY-FIELDS=Name, Title /SYNC-ACROSS-PAGES /HYPERLINK-ACROSS-PAGES"

objAddOn.Run("/S-ARGSTR " + orgWizArgs)
objAddOn.Run("/S-RUN")
visApp.visible = True

The error comes from the line where we set up the strPageConfig, specifically when the name is read from the ListBox1. When the value has any spaces in it, I will get a pop up for each word (2 or 3 because they're names) stating:

Employee name "Smith" is not in your organization data.
Employee name "John" is not in your organization data.

What am I missing or forgetting that is causing spaces to cause this error?

A work around that causes another issue is that when we load the employee names into the ListBox1, we replace the spaces with _'s, but these underscores show up in the drawing, which we don't want.

So my question is: How can I run the OrgChart Wizard from VBA with data that includes spaces? or: How can I go into these objects via VBA and edit the ShapeData text fields?

I've seen similar questions posted on other sites, but with no answer. I am also using Visio 2013

Solution

  • You need to quote your strings, so:

    Public Function QuoteString(str as String) as String

    Dim quotechar as String
    quotechar = Chr(34)

    QuoteString = quotechar & str & quotechar

End Function


strPageConfig = " /PAGES=" & QuoteString(ListBox1.SelectedItem) & " " & lvlNum & " PAGENAME=cleanedData"