If xapplication is open, Then

[joemo2003]

How to defind the if function in excel vba: if xxapplication is open, then...?

[SammyB]

How to defind the if function in excel vba: if xxapplication is open, then...?

You use GetObject. In your case,

Sub IsOpen()
    Dim vsApp As Visio.Application
    On Error Resume Next
    Set vsApp = GetObject(, "Visio.Application")
    If vsApp Is Nothing Then
        MsgBox "Not Open"
    Else
        MsgBox "Open"
    End If
End Sub

[joemo2003]

You use GetObject. In your case,

Sub IsOpen()
    Dim vsApp As Visio.Application
    On Error Resume Next
    Set vsApp = GetObject(, "Visio.Application")
    If vsApp Is Nothing Then
        MsgBox "Not Open"
    Else
        MsgBox "Open"
    End If
End Sub

yes, that is what i am looking for.
thanks

[joemo2003]

Sam,
got another question, since I open the visio within excel, and I set the "vsApp.Visible=False". I use "vsApp.quit" after "SaveAsEx", but sometime I doesn't do the "SaveAsEx" command and close the excel, so that visio still open and can see in the Task Manager. therefore, how to make it if the excel closed, the visio also will closed.

thanks

[SammyB]

Sam,
got another question, since I open the visio within excel, and I set the "vsApp.Visible=False". I use "vsApp.quit" after "SaveAsEx", but sometime I doesn't do the "SaveAsEx" command and close the excel, so that visio still open and can see in the Task Manager. therefore, how to make it if the excel closed, the visio also will closed.

thanks

You just need to add a Workbook Close event:

1. In the Excel IDE, double click on ThisWorkbook in the project explorer to open the code for the workbook.
2. Unless you have defined an event, he code window will be blank. Enter "Option Explicit" as the the first line. (BTW, you should do this in every code module. It forces you to define everything, thus keeping from spelling a variable two different ways.)
3. At the top of the code window: On the left, click the drop-down and choose Workbook. On the right, click the drop-down and choose BeforeClose. Remove the Open event outline.
4. In the module where you define vsApp, make sure it is public:
   Public vsApp As Visio.Application
5. Find all of the places that you do vsApp.Quit and make sure that the next line is:
   Set vsApp = Nothing
6. Make your Close event look like:

Option Explicit
Private Sub Workbook_BeforeClose(Cancel As Boolean)
    If Not vsApp Is Nothing Then vsApp.Close
End Sub

Does that make sense?

[SammyB]

> "vsApp.Visible=False"
BTW, I find that it is the best policy to make any application that I use visible. It's just too easy to leave a hidden application running. Plus, it makes the users think that more is happening! ;)

[joemo2003]

> "vsApp.Visible=False"
BTW, I find that it is the best policy to make any application that I use visible. It's just too easy to leave a hidden application running. Plus, it makes the users think that more is happening! ;)

thanks, I try it at monday.

[joemo2003]

sam,
It doesn't work.
My code is in sheet I, so I use Option Explicit and Public vsApp as Visio.Application in sheet I under Microsoft Excel Objects. And under ThisWorkbook, I use

Option Explicit
Public vsApp As Visio.Application
Private Sub Workbook_BeforeClose(Cancel As Boolean)
    If Not vsApp Is Nothing Then vsApp.Close
End Sub
 

but after I open the visio as not visible, and then close the excel, it said the vsApp is not defined.

[SammyB]

sam,
It doesn't work.
My code is in sheet I, so I use Option Explicit and Public vsApp as Visio.Application in sheet I under Microsoft Excel Objects. And under ThisWorkbook, I use

Option Explicit
Public vsApp As Visio.Application
Private Sub Workbook_BeforeClose(Cancel As Boolean)
If Not vsApp Is Nothing Then vsApp.Close
End Sub
 

but after I open the visio as not visible, and then close the excel, it said the vsApp is not defined.

You cannot have vsApp defined in two different places. Define it as Public in Sheet1; remove the definition in ThisWorkbook.

[joemo2003]

You cannot have vsApp defined in two different places. Define it as Public in Sheet1; remove the definition in ThisWorkbook.

I try that too, if I only define it as public in Sheet1, then it have error vsApp not define when I close up the excel, that is why I try also define it on ThisWorkbook.

[SammyB]

I try that too, if I only define it as public in Sheet1, then it have error vsApp not define when I close up the excel, that is why I try also define it on ThisWorkbook.

Sorry, I wrote that when I didn't have Visio. It should be vsApp.Quit. Also, in some versions of Excel, you cannot have Public variables in sheet code, so it may be best to put the "Public vsApp as Visio.Application" in a code (macro) module.

[joemo2003]

Sorry, I wrote that when I didn't have Visio. It should be vsApp.Quit. Also, in some versions of Excel, you cannot have Public variables in sheet code, so it may be best to put the "Public vsApp as Visio.Application" in a code (macro) module.

How can i transfer my code from Sheet 1 to module, i try to copy and paste or export and import, but it lose the link to the button in sheet1.

[SammyB]

How can i transfer my code from Sheet 1 to module, i try to copy and paste or export and import, but it lose the link to the button in sheet1.

Don't move any code! Just move the one line Public vsApp as ...
In other words:
ThisDocument -- document event code
Sheet1 -- button event code
Module1 -- global definition of vsApp

[joemo2003]

Don't move any code! Just move the one line Public vsApp as ...
In other words:
ThisDocument -- document event code
Sheet1 -- button event code
Module1 -- global definition of vsApp

yeah, that work. thanks