Please Note: This article is written for users of the following Microsoft Excel versions: 2007, 2010, and 2013. If you are using an earlier version (Excel 2003 or earlier), this tip may not work for you. For a version of this tip written specifically for earlier versions of Excel, click here: Finding Other Instances of Excel in a Macro.

Finding Other Instances of Excel in a Macro

by Allen Wyatt
(last updated May 17, 2014)

5

If you run a VBA program from within a particular instance of Excel, you can create other instances of Excel, open and modify workbooks in the newly created instances, and then close those instances. However, you may wonder how you can determine, within a macro, if other instances of Excel are already running, and, if so, take control of those instances.

There are a few ways you can go about doing this. If you simply want to know how many instances of Excel are running, you can use a macro that makes use of the Windows API. The following function implements this approach:

Public Declare Function GetDesktopWindow Lib "user32" () As Long
Public Declare Function FindWindowEx Lib "user32" Alias _
  "FindWindowExA" (ByVal hWnd1 As Long, ByVal
hWnd2 As Long, ByVal lpsz1 As String, ByVal lpsz2 As String) As Long

Function ExcelInstances() As Long
    Dim hWndDesk As Long
    Dim hWndXL As Long

    'Get a handle to the desktop
    hWndDesk = GetDesktopWindow

    Do
        'Get the next Excel window
        hWndXL = FindWindowEx(GetDesktopWindow, hWndXL, _
          "XLMAIN", vbNullString)

        'If we got one, increment the count
        If hWndXL > 0 Then
            ExcelInstances = ExcelInstances + 1
        End If

        'Loop until we've found them all
    Loop Until hWndXL = 0
End Function

This code was developed by Excel MVP Stephen Bullen and can be found at this site:

http://www.officekb.com/Uwe/Forum.aspx/excel-prog/55941

This, obviously, won't allow you access to the individual instances of Excel; it only returns a count of the number of instances open. If you want to develop code to use the instances, then you don't need to rely upon the Windows API. You can, instead, use code such as the following to determine if an instance of Excel is open:

Dim xlApp As Excel.Application
Set xlApp = GetObject(, "Excel.Application")

If an instance is running you can access it using the xlApp object. If an instance is not running you will get a run-time error. The GetObject function gets the first instance of Excel that had been loaded. To get to others, you can close that one and then try GetObject again to get the next one, etc.

If you want to set the xlApp to a particular instance of Excel, you can do so if you know the name of an open workbook in that instance:

Dim xlApp As Excel.Application
Set xlApp = GetObject("ExampleBook.xlsx").Application

ExcelTips is your source for cost-effective Microsoft Excel training. This tip (9452) applies to Microsoft Excel 2007, 2010, and 2013. You can find a version of this tip for the older menu interface of Excel here: Finding Other Instances of Excel in a Macro.

Author Bio

Allen Wyatt

With more than 50 non-fiction books and numerous magazine articles to his credit, Allen Wyatt is an internationally recognized author. He  is president of Sharon Parq Associates, a computer and publishing services company. ...

MORE FROM ALLEN

AutoText Limits

Are there limits to AutoText entries in Word? If you are having problems saving entries, it may not be due to limits imposed ...

Discover More

Adjusting Your View of 3-D Graphs

Do you use Excel's charting capabilities to display three-dimensional views of your data? The program provides a way that you ...

Discover More

Replacing an X with a Check Mark

In order to provide a finishing touch to your document, you may want to replace mundane X marks with fancier check marks. ...

Discover More

Program Successfully in Excel! John Walkenbach's name is synonymous with excellence in deciphering complex technical topics. With this comprehensive guide, "Mr. Spreadsheet" shows how to maximize your Excel experience using professional spreadsheet application development tips from his own personal bookshelf. Check out Excel 2013 Power Programming with VBA today!

More ExcelTips (ribbon)

Unprotecting Groups of Worksheets

Unprotecting a single worksheet is relatively easy. Unprotecting a whole lot of worksheets is harder. Here's how you can make ...

Discover More

Swapping Two Numbers

When programming macros, variables are used extensively. At some point you might want to exchange the values held by two ...

Discover More

Magnifying Only the Current Cell

You can use the Zoom feature of Excel to magnify what Excel shows of your workbook, but it affects the entire screen. What if ...

Discover More
Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

View most recent newsletter.

Comments

If you would like to add an image to your comment (not an avatar, but an image to help in making the point of your comment), include the characters [{fig}] in your comment text. You’ll be prompted to upload your image when you submit the comment. Maximum image size is 8Mpixels. Images larger than 600px wide or 1000px tall will be reduced. Up to three images may be included in a comment. All images are subject to review. Commenting privileges may be curtailed if inappropriate images are posted.

What is 6 - 3?

2017-02-13 00:17:55

matheson

PS - a decade ago, I used to use SQL queries to join the appropriate SAP tables, and extract... but they took that feature away
I fully admit that I am little more than "dangerous" on computers - but viewed the SQL complete with my Excel VBA used to allow us to put together solid algorithms for SAP guru's to eventually develop interfaces.. once we knew what we wanted we sent them our code to our Gurus to build (practical uses for the end-users).

SAP's use of Scripting appears to be foolish - I have about 10 levels of "fault handling routines" to handle all the "unexpected pop-ups" and "SAP generated messages"- and I suspect that MY Excel VBA is affecting their code (lol) - is it possible that they are the ones shutting me down?
(even all the tricks on this page no longer work)


2017-02-12 23:59:42

Matheson

I utilize EXCEL VBA scripting to :
.Open SAPGUI
.Logon SAP 740
.Assure that I am on SAP EASY ACCESS
.Start Transaction IW49N
.Populate the fields (Plant, Planning Group, Start Dates/EndDates, Layout etc)
.Initate the query.
Once the data is retrieved, it asks that the Results to be exported to EXCEL as a Table, and SAP responds by opening a Excel File (“Worksheet in Basis (1)”) in a SEPARATE INSTANCE of Excel (ie differ than the instance I am utilizing with VBA).

Originally, even though it was a separate instance – I could still “sense” the file via the Excel VBA Project List.

Hence I could change to the SAP Generated file, Select All cells, copy it over to MY Excel Workbook, then return to SAP (scripting) to close out the SAP generated Instance (“Worksheet in Basis (1)”)

That worked for a few months…. Then suddenly I was no longer to see the SAP generated file (“Worksheet in Basis (1)”) in the Excel my Excel VBA Project List…

So I started using Window API Codes, to find the File and the window Handle. This allowed me to use Window API codes to Activate the window as Excel and hence, Copy the content of the spreadsheet….


Now that stopped working…

So … anyone know the Proper way of Extracting the contents off the SAP Generated Worksheet?


2017-01-18 12:05:17

Richard

When i run getobject to grab a specific excel instance:

Dim xlApp As Excel.Application
Set xlApp = GetObject("ExampleBook.xlsx").Application

I get the following error:
Automation error
Invalid syntax

Any inputs are appreciated!


2015-08-12 20:09:24

Andrew Park

Hello,

A fellow developer obviously saw this post and used it to ensure that only 1 instance of Excel is running when the macro executes. However, we recently upgraded to Office 2013 and for some strange reason this code returns the value of 8 when executed and only 1 instance of Excel is running.

Even more strangely, when the same VBA code is executed again, the function returns 9. and keeps incrementing by 1 every time!!!

This code return a nice "1" when there was only 1 instance running in Excel 2010.

Are you sure this works in Excel 2013???


2014-12-16 03:39:26

Rob

Hi Allen,

Here's a situation I am facing. I have two instances of similar Excel workbooks. Similarity, in terms of functionalities, tabs, sheets, back-end VBA etc. Now, I'm projecting both of these instances on two different screens. I want to replicate all the actions/events/selections on one Excel instance (which I will be controlling), on the other instance as well, automatically. Is this possible? I felt this thread speaks about something similar.

Thanks in advance,


This Site

Got a version of Excel that uses the ribbon interface (Excel 2007 or later)? This site is for you! If you use an earlier version of Excel, visit our ExcelTips site focusing on the menu interface.

Newest Tips
Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

(Your e-mail address is not shared with anyone, ever.)

View the most recent newsletter.