Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1530
  • Last Modified:

Visio Macro Execution

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
Brian_Fitch24
Asked:
Brian_Fitch24
  • 3
  • 2
1 Solution
 
Scott HelmersVisio Consultant, Trainer, Author, and DeveloperCommented:
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
 
Brian_Fitch24Author Commented:
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
 
Scott HelmersVisio Consultant, Trainer, Author, and DeveloperCommented:
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
 
Scott HelmersVisio Consultant, Trainer, Author, and DeveloperCommented:
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
 
Brian_Fitch24Author Commented:
Scott -
Thanks so much for your help.  I got it to work.

Brian
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

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.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now