Exporting shape and sub-shape texts to Excel

[Surrogate]

I am running code in Excel VBA with Visio document open.

Did you add reference to Visio Application in your VBA-project ?

Code Select Expand

Sub ShapesList(ByVal shps As Shapes, ByVal xlWS As Object)
If 'Not" then Excel's Shape Object not equal as Visio's Shape Object!

[LeighH]

Thanks for helping :-)
I believe I have, see attached. I am clueless as to why this is not working when its worked for others :-(

I am running code in Excel VBA with Visio document open.

Did you add reference to Visio Application in your VBA-project ?

Code Select Expand

Sub ShapesList(ByVal shps As Shapes, ByVal xlWS As Object)
If 'Not" then Excel's Shape Object not equal as Visio's Shape Object!

[Nikolay]

Ah, you run the code in Excel. That explains the mystery. Excel also has "Shapes" but those are different kind of "Shapes". You need to change the function as following:

This:

Code Select Expand


Sub ShapesList(ByVal shps As Shapes, ByVal xlWS As Object)
    Dim sh As Shape


Should be (note the "Visio." prefix before "Shapes" and "Shape". We want Visio shapes, not Excel shapes here):

Code Select Expand


Sub ShapesList(ByVal shps As Visio.Shapes, ByVal xlWS As Object)
    Dim sh As Visio.Shape


[Surrogate]

IMHO Best way declare all variables types explicit!

Code Select Expand

Dim vsPage As Visio.Page
Dim vsDoc As Visio.Document
Dim XlApp As Excel.Application
Dim xlWB As Excel.Workbook
Dim xlWS As Excel.Worksheet
Dim vsApp As Visio.Application
And in this line too

Code Select Expand

Sub ShapesList(ByVal shps As Visio.Shapes, ByVal xlWS As Excel.Worksheet)

[LeighH]

Thanks Nikolay and Surrogate, this has now worked :-)

I appreciate all the help :-)

Ah, you run the code in Excel. That explains the mystery. Excel also has "Shapes" but those are different kind of "Shapes". You need to change the function as following:

This:

Code Select Expand


Sub ShapesList(ByVal shps As Shapes, ByVal xlWS As Object)
    Dim sh As Shape


Should be (note the "Visio." prefix before "Shapes" and "Shape". We want Visio shapes, not Excel shapes here):

Code Select Expand


Sub ShapesList(ByVal shps As Visio.Shapes, ByVal xlWS As Object)
    Dim sh As Visio.Shape


[Yacine]

IMHO Best way declare all variables types explicit!

This works only when early binding.
When late binding - that is the case when you need to make the code suitable for "any" Visio version - then a late binding is better suited and you would declare all foreign objects (the Visio ones) as objects. VBA then figures out by itself what type of object it is.
Down sides:
- no event handling from the foreign object - at least no obvious solutions.
- worse debugging - you can however make a switch - debug with early binding, then switch to late.

[wapperdude]

Early vs late binding:
The backward compatibility seems like more of a deployment/automation issue, and not something typically encountered.  (How's that for a nervy statement?  As one who has very small exposure bubble, there likely is a lot of naivety.). I did find 3 references.  The first is more in-depth.  The 2nd hits more at the local user scenario.  The 3rd has nice summary.

https://learn.microsoft.com/en-us/previous-versions/office/troubleshoot/office-developer/binding-type-available-to-automation-clients

https://learn.microsoft.com/en-us/dotnet/visual-basic/programming-guide/language-features/early-late-binding/

https://learn.microsoft.com/en-us/power-apps/developer/data-platform/org-service/early-bound-programming

...and I have NOT fully explored the 1st and 3rd.  The 2nd covers my typical use cases, those being early binding.  There are a lot of benefits to such, which speaks directly to how I use VBA.

[Yacine]

I had to handle about 20 installations, where different Visio versions where installed. I had to walk to each one and set the right reference to Visio.
Since then I switched all my code to late binding (with the mentioned drawbacks). Now with Visio 365 and its continuous update feature that may not be necessary anymore.
Need to check it.

[wapperdude]

Ouch.  Sounds like a nightmare.

[Nikolay]

With a script (.vbs, vbscript file) it is somewhat possible to have events:

runme.vbs

Code Select Expand


Sub AppEvents_ShapeAdded(sh) ' <<< prefix match
    WScript.Echo sh.Name
End Sub

Set app = WScript.CreateObject("Visio.Application", "AppEvents_")  ' <<<< here

Set doc = app.Documents.Add("")

doc.Pages.Item(1).DrawRectangle 1, 2, 3, 4

doc.SaveAs "C:\temp\foo.vsd"


[Yacine]

@Nikolay,
So your snippet is pure VBA and you set a reference to WScript instead of Visio?


I will try it. I have the problem that I need to make modifications to my Visio file before the user closes it. You're trick could help.

[Nikolay]

It's not exactly VBA, it is a VB script (you can copy snippet code to notepad, then save the content to a file with ".vbs" extension and double-click to run it).
VBScript is a sub-set of VBA. But yes, it does not have any references, as far as I can tell. WScript is "windows scripting" built-in reference.

You can basically run it without Visio or any office application installed.
Now it's mostly replaced with PowerShell.

[Yacine]

How would I use it from inside my Access VBA project?

[Nikolay]

You are right, there appears to be no way to do this in pure VBA
The magic "WScript" is not available.

[Yacine]

Hi guys, I am just back from a chat with Chat-GPT. I am overwhelmed.
Have a read, it's worthwhile.
https://chat.openai.com/share/8b2bac74-19ac-499b-a2be-a30f80e2955e