Solved

Moving visio on top of Excel after Excel has created a new instance of visio

Posted on 2013-06-17
4
815 Views
Last Modified: 2013-06-18
I use excel to create a new instance of Visio, in this Excel module VBA code:
            Set visioApp = New Visio.Application 'early binding
            Set visQDoc = visioApp.Documents.Open(fileSaveName)
            Set pgs = visQDoc.Pages

When I come to close visio from Excel (via visioApp.Quit), visio lies behind Excel, and  the visio windows dialogue box "Save visio file: Yes, No, Cancel"  is sandwiched between excel on top and visio at the back.

How to I get Visio on top of Excel, so the Visio windows dialogue box is visible?

- One work around would be to suppress the windows dialogue box and code my own, but a general solution to getting visio 'on top' will help in other situations.

- AppActivate Application.Caption works well for Excel, but AppActivate visioApp.Caption is not available to me from intellisense.

- When Excel VBA drives the creation of visio pages, Visio gets the focus, but I'd prefer to get visio on top without using the creation of temporary pages. It would be a last resort.

Thanks

Kelvin
0
Comment
Question by:Kelvin4
  • 2
  • 2
4 Comments
 
LVL 11

Expert Comment

by:Visio_Guy
ID: 39255773
Hi Kelvin,

Here's some VBA that I just tried. There are comments in the code that explain further what I tried, what worked, and what didn't. I used some Windows API stuff which is similar to an answer I gave for another question you asked.

Anyway, have a look. Here's the main test sub that starts Visio, adds a document, and tries to bring Visio to front:

Option Explicit

Sub ActiveVisioTest()
    
    '// Start a Visio instance:    
    Dim visApp As Visio.Application
    '// This needs a reference to Microsoft Visio XX Type Library:
    Set visApp = New Visio.Application
    '// This doesn't need the reference:
    '//...also: CreateObject("Visio.application")
    
    '// Add an empty document:
    Dim visDoc As Visio.Document
    Set visDoc = visApp.Documents.Add("")
    
    '// Use Win API to bring Visio to front:
    Call MWinAPI.BringWindowToTop(visApp.WindowHandle32)
    '// Note that the Visio.Application object has this nifty
    '// WindowHandle32 property!
    
    '// OK, the above seems to work, but if you are
    '// running from VBA directly, VBA will pop to the
    '// top, so Visio will end up in second place, which
    '// makes sense, now that I think about it...
    
    '// Try to activate the active window:
'    If Not (visApp.ActiveWindow Is Nothing) Then
'       '// Note: this doesn't seem to work:
'        Call visApp.ActiveWindow.Activate
'    End If
'    Call visApp.Window.Activate

    
    '// Cleanup:
    Set visApp = Nothing
    Set visDoc = Nothing
    
End Sub

Open in new window

Here is the moduel: MWinAPI, which does the nasty work:
Option Explicit

#If VBA7 Then
    '// Note: one of these lines will turn red and raise a compile error
    '//       if you edit it, but the code will run, so don't worry.
    
    '// 64-bit Visio:
    Public Declare PtrSafe Function BringWindowToTop Lib "user32" (ByVal lngHWnd As LongLong) As LongLong
#Else

    '// 32-bit Visio:
    public Declare Function BringWindowToTop Lib "user32" (ByVal lngHWnd As Long) As Long
    
#End If

Open in new window

0
 

Author Comment

by:Kelvin4
ID: 39256632
Hi  VisioGuy:
Thanks for such speedy investigative help.  (I've 64-bit visio.)
In the attached file,

sub ActiveVisioTest() is in a standard code module.

I puzzled over where to put MWinAPI, and settled for ThisWorkbook (because I could get the code there without causing an error..)

However, Call MWinAPI.BringWindowToTop(visApp.WindowHandle32) raised the compile error: 'variable not defined', and MWinAPI was blue-high-lit in the code.

I have Visio 14.0 type library referenced, and could find no hints on the web as to what I'm missing to reference MWinAPI - apart from more experience...

Where did I go off the rails?

Regards
Kelvin
GetVisWindowOnTop-EE-18June-2013.xlsm
0
 
LVL 11

Accepted Solution

by:
Visio_Guy earned 500 total points
ID: 39256712
Hi Kelvin,

Make a module called MWinAPI and put the funny #If...Then Windows API stuff in there. The other code can be in ThisDocument, a module, or a class. It doesn't matter.
0
 

Author Closing Comment

by:Kelvin4
ID: 39256897
I get it.. Thanks again.
Kelvin
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Microsoft Office Picture Manager was included in Office 2003, 2007, and 2010, but not in Office 2013. Users had hopes that it would be in Office 2016/Office 365, but it is not. Fortunately, the same zero-cost technique that works to install it with …
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
Learn how to make your own table of contents in Microsoft Word using paragraph styles and the automatic table of contents tool. We'll be using the paragraph styles in Word’s Home toolbar to help you create a table of contents. Type out your initial …
Learn how to create and modify your own paragraph styles in Microsoft Word. This can be helpful when wanting to make consistently referenced styles throughout a document or template.

895 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

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now