Export and import Shape Data to / from Excel

[Thomas Winkel]

Hi,

the attached stencil draws a new Ribbon "Visio-Tools".
Here you find a button to export properties and user data of a selected shape to Excel.
You can modify, add, delete or reorder this data in Excel.
With another button you can import the data to selected shapes.

Here is a Video:
https://youtu.be/OYpENi7Pg4U

Regards,
Thomas


P.S.
Information about the Ribbon can be found here:
http://visguy.com/vgforum/index.php?topic=7134.0

[Yacine]

Hut ab!

[JuneTheSecond]

Cool! I like this!

[Surrogate]

Hi, Thomas !

Nice job !
One of my ribbon looks same


When i try press button Export Shape Data, and get reference error

I use MS Office & Visio 2010 - i think better use late binding, for support
other versions also!

[Thomas Winkel]

Hi Surrogate,

thanks for your feedback.
You're right with late binding.
Sad, that this does not work reliable.
In our company we support only the combination Visio 2010 and Office 2013 for this reason.
Sometimes it is working with other versions and sometimes not.
Depending on what Office / Visio versions had been installed before and the phase of the moon 

I switched the stencil in the first post to late binding.
The Excel reference is still set, but only for the constants like "xlSrcRange".
Could you please try if this is working for you? I fear it's not...

Another modification is, that the error message on wrong user entry in Excel now is more detailed.

[Surrogate]

Hi, Thomas !

i just change some lines in your code

Code

' Dim xlsWB As Excel.Workbook
Dim xlsWB As Object 'instead previous line

Public Sub export()
    Dim shp As Visio.Shape
    Dim row As Visio.row
    Dim i As Integer
    ' Dim xlsApp As Excel.Application
    Dim xlsApp As Object 'instead previous line
    ' Dim xlsWS As Excel.Worksheet
    Dim xlsWS As Object 'instead previous line
    Dim arrProp(1 To 1, 1 To 12) As String
    Dim arrUser(1 To 1, 1 To 4) As String
    
    If ActiveWindow.Selection.Count > 0 Then
        Set shp = ActiveWindow.Selection.PrimaryItem
    End If
    
    ' Set xlsApp = New Excel.Application
    Set xlsApp = CreateObject("Excel.Application") 'instead previous line
    xlsApp.Visible = True ' i want to see this application window

this code works !

[Thomas Winkel]

Thanks, Surrogate.

but you also had to replace the constant xlSrcRange with its actual value (1).
The idea was, that this may not be necessary, even it the reference is broken.

In my new topic I included a module "GlobalConstants" with:

Code

Public Const xlSrcRange As Integer = 1

But there I cannot use late binding because of "WithEvents" in:

Code

Public WithEvents ExcelAppEvents As Excel.Application

[Surrogate]

Hi, Thomas !

You are right about Excel specific constants, such as xlSrcRange and xlYes !
I agree you can't use excel events with late binding. Which excel events you want
use ?

[Thomas Winkel]

Hi Surrogate,

have a look in my new topic here:
http://visguy.com/vgforum/index.php?topic=7581.0

In the video you can see that I react an SheetSelectionChange and on SheetChange:
https://youtu.be/LQpllJpzsU4

I don't know if there is a way with late binding to achieve this..