asked on

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

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

Chris Roth

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

Roger

ASKER

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

ASKER CERTIFIED SOLUTION

Chris Roth

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Roger

ASKER

I get it.. Thanks again.
Kelvin