Roger
asked on
Passing variables by VBA between excel and visio in early binding
I control MS Office.Visio from Excel, using early binding, to manipulate Visio pages, shapes etc. Thus far, the superior scope for RibbonX customisation in Excel has given me control over customised processes in Visio, that greatly exceeds the customisation provided by the visio ribbon.
However, I now need to control the occurrence of specific RibbonX custom tabs in Excel, by detecting change events triggered by changes in the current Visio.activePage.
-------------------------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
VisioGuy (EE) provided me with code that detects change events in visio active page, when placed in the visio document code sheet:
Dim WithEvents m_visWins As Visio.Windows
Private Sub Document_RunModeEntered(By Val Doc As IVDocument)
Set m_visWins = Visio.Application.Windows
End Sub
Private Sub m_visWins_WindowTurnedToPa ge(ByVal visWin As IVWindow)
If (visWin.Type = Visio.VisWinTypes.visDrawi ng) Then
Debug.Print "WindowTurnedToPage: " & visWin.Page '<<<<<<<<<<<
Else
Debug.Print "WindowTurnedToPage, but not a drawing window: " & visWin.Caption
End If
End Sub
-------------------------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
This code works well in visio but, perhaps not surprisingly, does not capture the Visio event when the code is placed the worksheet code page of an excel workbook that has been set to reference the visio object model (via VBE/Tools/References).
How do I monitor in excel, visWin.Page (marked '<<<< above) as an object or as a string = object.Name
- can I use a public variable in visio, and make it detectable in excel (my attempts have failed so far)
- do I create a new class in visio, and attempt to detect visio page changes as a property of the visio object model?
- do I try to use the Application.Run Method inserted in code at the point marked '<<<< (above) and pass variables as arguments by position. I understand Application.Run arguments cannot be objects, but I could live with that, if it works for other variables).
- or something else?
- IF POSSIBLE! it would be helpful to have one generic method that can be used to pass variables in either direction between Excel and Visio, to transmit other variables in the future. Given that Visio macros are stored as documents, and hence called by ...ExecuteLine("myMacro"), would a bidirectional method be impossible?
Some pointers to start me off would be appreciated..
Thanks!
However, I now need to control the occurrence of specific RibbonX custom tabs in Excel, by detecting change events triggered by changes in the current Visio.activePage.
--------------------------
VisioGuy (EE) provided me with code that detects change events in visio active page, when placed in the visio document code sheet:
Dim WithEvents m_visWins As Visio.Windows
Private Sub Document_RunModeEntered(By
Set m_visWins = Visio.Application.Windows
End Sub
Private Sub m_visWins_WindowTurnedToPa
If (visWin.Type = Visio.VisWinTypes.visDrawi
Debug.Print "WindowTurnedToPage: " & visWin.Page '<<<<<<<<<<<
Else
Debug.Print "WindowTurnedToPage, but not a drawing window: " & visWin.Caption
End If
End Sub
--------------------------
This code works well in visio but, perhaps not surprisingly, does not capture the Visio event when the code is placed the worksheet code page of an excel workbook that has been set to reference the visio object model (via VBE/Tools/References).
How do I monitor in excel, visWin.Page (marked '<<<< above) as an object or as a string = object.Name
- can I use a public variable in visio, and make it detectable in excel (my attempts have failed so far)
- do I create a new class in visio, and attempt to detect visio page changes as a property of the visio object model?
- do I try to use the Application.Run Method inserted in code at the point marked '<<<< (above) and pass variables as arguments by position. I understand Application.Run arguments cannot be objects, but I could live with that, if it works for other variables).
- or something else?
- IF POSSIBLE! it would be helpful to have one generic method that can be used to pass variables in either direction between Excel and Visio, to transmit other variables in the future. Given that Visio macros are stored as documents, and hence called by ...ExecuteLine("myMacro"),
Some pointers to start me off would be appreciated..
Thanks!
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Fine with me!!!
ASKER
Thanks, again, for your suggestions. yrs Kelvin...
Scott: Thank you for referencing the resources available for variable-sharing between visio and excel.
This will help me a lot. As you point out, it will not address the requirement for an Event component.
Jkpieterse: I extracted a section of my Excel code to try to follow your suggestion, alas without success.
However, I am a novice at class creation so I've attached my files for your inspection, and any comment you may wish.
I admit that the class creation process is shorter that in your original suggestion, but it simply mimics the
class set up in pageTurn Event in Visio.vsd. Looking at that first:
File: pageTurn Event in Visio.vsd:
is a straight visio-vased solution (from visioGuy) showing a Visio.Application.Windows event for page turning.
Change the page, and a msgbox report.
Now my adaptation to Excel....
File: visPageTurnEventInXL add WithEvents v2.3.xlsm
is an extract from my Project that links Excel to drive Visio:
In ThisWorkbook:
I enable the visio file "try to detect page turn in Excel.vsd" to be loaded from the file browser.
All you need to do is place the .vsd file in your chosen directory,
Open excel
Cancel browser
Open vbe
in excel modules\globals\ set Public Const options_EndUser_OpenFile_D efaultPath As String = "C:" to your directory loc of "try to detect page turn in Excel.vsd"
Save and reopen excel file
Use browser to open "try to detect page turn in Excel.vsd"
All the supporting code for the page turn event is in ThisWorkbook, becuase:
I had to run the browser from ThisWorkbook to enable
Public WithEvents m_visWins As Visio.Windows to be created when the visio file is opened.
However, as you can see at the foot of the code page, in the starred comments:
Set m_visWins = Visio.Windows threw an error.
Your comments have set me thinking, can you push it a tad further?
Thanks, Kelvin
visPageTurnEventInXL-add-WithEve.xlsm
pageTurn-Event-in-Visio.vsd
try-to-detect-page-turn-in-Excel.vsd
Scott: Thank you for referencing the resources available for variable-sharing between visio and excel.
This will help me a lot. As you point out, it will not address the requirement for an Event component.
Jkpieterse: I extracted a section of my Excel code to try to follow your suggestion, alas without success.
However, I am a novice at class creation so I've attached my files for your inspection, and any comment you may wish.
I admit that the class creation process is shorter that in your original suggestion, but it simply mimics the
class set up in pageTurn Event in Visio.vsd. Looking at that first:
File: pageTurn Event in Visio.vsd:
is a straight visio-vased solution (from visioGuy) showing a Visio.Application.Windows event for page turning.
Change the page, and a msgbox report.
Now my adaptation to Excel....
File: visPageTurnEventInXL add WithEvents v2.3.xlsm
is an extract from my Project that links Excel to drive Visio:
In ThisWorkbook:
I enable the visio file "try to detect page turn in Excel.vsd" to be loaded from the file browser.
All you need to do is place the .vsd file in your chosen directory,
Open excel
Cancel browser
Open vbe
in excel modules\globals\ set Public Const options_EndUser_OpenFile_D
Save and reopen excel file
Use browser to open "try to detect page turn in Excel.vsd"
All the supporting code for the page turn event is in ThisWorkbook, becuase:
I had to run the browser from ThisWorkbook to enable
Public WithEvents m_visWins As Visio.Windows to be created when the visio file is opened.
However, as you can see at the foot of the code page, in the starred comments:
Set m_visWins = Visio.Windows threw an error.
Your comments have set me thinking, can you push it a tad further?
Thanks, Kelvin
visPageTurnEventInXL-add-WithEve.xlsm
pageTurn-Event-in-Visio.vsd
try-to-detect-page-turn-in-Excel.vsd
ASKER
Sunday 26 am.
I a solution to the EVENT component of my question, and will provide code shortly. In outline:
A visio event detects the turning of a new visio page
Application.Run passes the turned page.name as argument from visio to a standard module in Excel.
The Excel module writes the turned visio page.name to an excel sheet cell.
An excel event detects the excel sheet change and, if the changed cell does contain the turned page.name, the event creates that name as a public excel variable - which is my aim.
So far, it works fine..
I would be very glad to know if the single event solution suggested by jkpieterse can work. It would certainly help add to my tool-box of solutions.
Will propose a division of points on Tuesday am (European )
Kelvin
I a solution to the EVENT component of my question, and will provide code shortly. In outline:
A visio event detects the turning of a new visio page
Application.Run passes the turned page.name as argument from visio to a standard module in Excel.
The Excel module writes the turned visio page.name to an excel sheet cell.
An excel event detects the excel sheet change and, if the changed cell does contain the turned page.name, the event creates that name as a public excel variable - which is my aim.
So far, it works fine..
I would be very glad to know if the single event solution suggested by jkpieterse can work. It would certainly help add to my tool-box of solutions.
Will propose a division of points on Tuesday am (European )
Kelvin
ASKER
jkpieterse's outline suggestion grappled with the EVENT part of my question, and helped to get me thinking. But I could not exploit his suggestion and added some early feedback (above) for possible comment.
Scott's comments addressed the sharing of string data between office apps via Hidden Name space, which he has used to share string variables between visio and excel. So far, I've seen Hidden Name space working between instances of Excel (using his demo files in his EE Tips and Tricks paper: 2009-07-06) .
Scott: I would be grateful for the visio code lines that enable visio to write to xL Hidden Name space. My brain has got a bit spaced out!
Thanks, Kelvin
Scott's comments addressed the sharing of string data between office apps via Hidden Name space, which he has used to share string variables between visio and excel. So far, I've seen Hidden Name space working between instances of Excel (using his demo files in his EE Tips and Tricks paper: 2009-07-06) .
Scott: I would be grateful for the visio code lines that enable visio to write to xL Hidden Name space. My brain has got a bit spaced out!
Thanks, Kelvin
ASKER
Attached are the files I referred to above.
Place both in the same folder, open the excel file first and follow the instructions on sheet1
This sheet also summarises the purpose of the demo
Kelvin
TestGetxlObjectv3.vsd
VisAuthDev-Current.xlsm
Place both in the same folder, open the excel file first and follow the instructions on sheet1
This sheet also summarises the purpose of the demo
Kelvin
TestGetxlObjectv3.vsd
VisAuthDev-Current.xlsm
ASKER
All my shape-level and some page-level coding is in Visio; Excel handles higher levels between visio file creation/records and visio page creation.
I will take no more than three days to work on your comments. I hope you can wait til then for a settlement of points.
Appreciately, Kelvin.