By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
455,097 Members | 1,161 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 455,097 IT Pros & Developers. It's quick & easy.

If xapplication is open, Then

100+
P: 142
How to defind the if function in excel vba: if xxapplication is open, then...?
Apr 6 '07 #1
Share this Question
Share on Google+
13 Replies


SammyB
Expert 100+
P: 807
How to defind the if function in excel vba: if xxapplication is open, then...?
You use GetObject. In your case,
Expand|Select|Wrap|Line Numbers
  1. Sub IsOpen()
  2.     Dim vsApp As Visio.Application
  3.     On Error Resume Next
  4.     Set vsApp = GetObject(, "Visio.Application")
  5.     If vsApp Is Nothing Then
  6.         MsgBox "Not Open"
  7.     Else
  8.         MsgBox "Open"
  9.     End If
  10. End Sub
Apr 6 '07 #2

100+
P: 142
You use GetObject. In your case,
Expand|Select|Wrap|Line Numbers
  1. Sub IsOpen()
  2.     Dim vsApp As Visio.Application
  3.     On Error Resume Next
  4.     Set vsApp = GetObject(, "Visio.Application")
  5.     If vsApp Is Nothing Then
  6.         MsgBox "Not Open"
  7.     Else
  8.         MsgBox "Open"
  9.     End If
  10. End Sub
yes, that is what i am looking for.
thanks
Apr 6 '07 #3

100+
P: 142
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
Apr 6 '07 #4

SammyB
Expert 100+
P: 807
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:
Expand|Select|Wrap|Line Numbers
  1. Option Explicit
  2. Private Sub Workbook_BeforeClose(Cancel As Boolean)
  3.     If Not vsApp Is Nothing Then vsApp.Close
  4. End Sub
Does that make sense?
Apr 6 '07 #5

SammyB
Expert 100+
P: 807
> "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! ;)
Apr 6 '07 #6

100+
P: 142
> "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.
Apr 8 '07 #7

100+
P: 142
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
Expand|Select|Wrap|Line Numbers
  1. Option Explicit
  2. Public vsApp As Visio.Application
  3. Private Sub Workbook_BeforeClose(Cancel As Boolean)
  4.     If Not vsApp Is Nothing Then vsApp.Close
  5. End Sub
  6.  
but after I open the visio as not visible, and then close the excel, it said the vsApp is not defined.
Apr 9 '07 #8

SammyB
Expert 100+
P: 807
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
Expand|Select|Wrap|Line Numbers
  1. Option Explicit
  2. Public vsApp As Visio.Application
  3. Private Sub Workbook_BeforeClose(Cancel As Boolean)
  4. If Not vsApp Is Nothing Then vsApp.Close
  5. End Sub
  6.  
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.
Apr 10 '07 #9

100+
P: 142
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.
Apr 10 '07 #10

SammyB
Expert 100+
P: 807
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.
Apr 10 '07 #11

100+
P: 142
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.
Apr 10 '07 #12

SammyB
Expert 100+
P: 807
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
Apr 10 '07 #13

100+
P: 142
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
Apr 10 '07 #14

Post your reply

Sign in to post your reply or Sign up for a free account.