?
Solved

Visio Macro Execution

Posted on 2008-10-11
5
Medium Priority
?
1,433 Views
Last Modified: 2012-05-05
I have a 3 part question:

1.  Is there an Auto_Open Macro type in Visio, if so what is the syntax?

2.  How do I execute a Visio Macro from Excel VBA?

3.  How do I execute an Excel macro from Visio? - Excel.Application.Run???
0
Comment
Question by:Brian_Fitch24
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
5 Comments
 
LVL 31

Expert Comment

by:Scott Helmers
ID: 22697821
1) There are several events that can be used to run code when a document is opened but you have to pick which event is appropriate. DocumentOpened fires when an existing document is opened; DocumentCreated fires when you create a new document from a template. Each has its purpose. See below for syntax for both.

If you want yet another approach, see this article by Chris Roth, aka the Visio Guy:
http://www.visguy.com/2007/05/25/run-vba-code-when-documents-open/

2) & 3) For these two questions I need to clarify what you want to do. Do you want to run Excel VBA code from within a Visio document (and vice versa)? Or do you want code running in a Visio document to execute a macro that lives in an Excel document (and vice versa)? I've used the former quite a bit, for example to create an Excel document based on data in a Visio drawing. I have done the latter, though I'm sure it's possible.


Private Sub Document_DocumentCreated(ByVal doc As IVDocument)
 
    MsgBox "new document has been created", vbOKOnly
    
End Sub
 
 
Private Sub Document_DocumentOpened(ByVal doc As IVDocument)
 
    MsgBox "document has been opened", vbOKOnly
    
End Sub

Open in new window

0
 

Author Comment

by:Brian_Fitch24
ID: 22697996
Hey Scott -

Sorry I didnt make it worth  more points, I only had 265 left.  

1.  Looks good

2 & 3) I want code in a Visio document to execute a macro in an excel document and vice versa.

Thanks,

Brian
0
 
LVL 31

Expert Comment

by:Scott Helmers
ID: 22698999
Brian,

There may be a better way than this but this Visio code opens an Excel sheet and uses the Excel Workbook_Open event to run the Excel macro.

You should be able to do the reverse to run a Visio macro from Excel, but let me know if you need help with that.

Scott
IN EXCEL
 
Sub Workbook_Open()
' Runs whenever workbook is opened
 
    MsgBox "Greetings from Excel workbook open macro", vbOKOnly
 
End Sub
 
 
IN VISIO
 
Sub CallMarcoInExcel()
' opens Excel workbook, which runs workbook_open macro contained in the workbook
'
' Add Reference to Excel to this VBA project before running macro
 
    Dim xlApp As Excel.Application
    Dim xlBook As New Excel.Workbook
    Dim xlDocPath As String
 
    xlDocPath = "c:\Temp\Excel book with workbook_open macro.xls"
 
    'Use existing Excel object or create a new one
    On Error Resume Next
    Set xlApp = GetObject(, "Excel.Application") ' get ref if already running
    If Err = 429 Then
        Set xlApp = CreateObject("Excel.Application")
    End If
 
    'Bring Excel to front
    xlApp.Visible = True
    Set xlBook = xlApp.Workbooks.Open(xlDocPath)
 
End Sub

Open in new window

0
 
LVL 31

Accepted Solution

by:
Scott Helmers earned 1000 total points
ID: 22703163
It's been bothering me that I couldn't run a specific macro and could only get the workbook_open macro to run.

Well, after a bit of experimenting the code below will work. Note that it is essentially the same Visio code that I posted yesterday with a few new lines at the bottom of the sub. There is really only one line that matters but I've provided two variations: the first runs code in a module other than ThisWorkbook; the second runs code in ThisWorkbook.

One important note on the syntax: The spreadsheet name must be enclosed in single quotes. It doesn't show this in the Excel help text for the Run method but after trying dozens of variations, I discovered this syntax by poking around in the EE Excel zone.
Sub CallMacroInExcel()
' opens Excel workbook (workbook_open macro, if any, will run
' then calls specific macros contained in the workbook
'
' Add Reference to Excel to this VBA project before running macro
 
    Dim xlApp As Excel.Application
    Dim xlBook As New Excel.Workbook
    Dim xlSheet As Excel.Worksheet
    Dim xlDocPath As String
 
    xlDocPath = "c:\Temp\Excel book with macro.xls"
 
    'Use existing Excel object or create a new one
    On Error Resume Next
    Set xlApp = GetObject(, "Excel.Application") ' get ref if already running
    If Err = 429 Then
        Set xlApp = CreateObject("Excel.Application")
    End If
 
    'Bring Excel to front
    xlApp.Visible = True
    Set xlBook = xlApp.Workbooks.Open(xlDocPath)
    
    'workbook name must be surrounded by single quotes
    xlApp.Run "'" & xlBook.Name & "'!RegularMacro"
    'run macro in ThisWorkbook module
    xlApp.Run "'" & xlBook.Name & "'!ThisWorkbook.RegularMacro"
 
End Sub

Open in new window

0
 

Author Closing Comment

by:Brian_Fitch24
ID: 31505297
Scott -
Thanks so much for your help.  I got it to work.

Brian
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.

801 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question