Solved

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

Posted on 2013-06-17
4
811 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

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

Using Word 2013, I was experiencing some incredible lag when typing.  Here's what worked for me....
Outlook Free & Paid Tools
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

747 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

13 Experts available now in Live!

Get 1:1 Help Now