Author Topic: Export and import Shape Data to / from Excel  (Read 12573 times)

0 Members and 1 Guest are viewing this topic.

Thomas Winkel

  • Full Member
  • ***
  • Posts: 239
Export and import Shape Data to / from Excel
« on: December 03, 2016, 07:17:54 AM »
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
« Last Edit: December 04, 2016, 05:29:36 PM by Thomas Winkel »

Yacine

  • Hero Member
  • *****
  • Posts: 2991
Re: Export and import Shape Data to / from Excel
« Reply #1 on: December 03, 2016, 10:34:33 AM »
Hut ab!
Yacine

JuneTheSecond

  • Hero Member
  • *****
  • Posts: 1027
    • Visio Shapes, Stencils and Sample Drawings
Re: Export and import Shape Data to / from Excel
« Reply #2 on: December 04, 2016, 08:30:34 AM »
Cool! I like this!
Best Regards,

Junichi Yoda
http://june.minibird.jp/

Surrogate

  • Hero Member
  • *****
  • Posts: 1630
    • ShapeSheet™ Knowledge Base
Re: Export and import Shape Data to / from Excel
« Reply #3 on: December 04, 2016, 04:01:57 PM »
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

  • Full Member
  • ***
  • Posts: 239
Re: Export and import Shape Data to / from Excel
« Reply #4 on: December 04, 2016, 05:49:10 PM »
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  ;D

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

  • Hero Member
  • *****
  • Posts: 1630
    • ShapeSheet™ Knowledge Base
Re: Export and import Shape Data to / from Excel
« Reply #5 on: December 10, 2016, 06:26:15 PM »
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 !
« Last Edit: December 10, 2016, 06:29:31 PM by Surrogate »

Thomas Winkel

  • Full Member
  • ***
  • Posts: 239
Re: Export and import Shape Data to / from Excel
« Reply #6 on: December 10, 2016, 07:00:43 PM »
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

  • Hero Member
  • *****
  • Posts: 1630
    • ShapeSheet™ Knowledge Base
Re: Export and import Shape Data to / from Excel
« Reply #7 on: December 11, 2016, 05:33:41 PM »
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

  • Full Member
  • ***
  • Posts: 239
Re: Export and import Shape Data to / from Excel
« Reply #8 on: December 11, 2016, 05:42:33 PM »
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..  ???