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)

9

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

Note:

If you would like to know how to use the macros described on this page (or on any other page on the ExcelTips sites), I've prepared a special page that includes helpful information. Click here to open that special page in a new browser tab.

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

Adjusting Formulas when Pasting

The Paste Special feature in Excel can be used to uniformly adjust values and formulas. This tip shows how powerful this ...

Discover More

Adding Pop-Up Documentation to a Cell

Want to have a small help screen pop up when a user selects a particular cell? This can be done by using data validation, ...

Discover More

Headings On Your Printout

If you've got a table that spans multiple printed pages, you probably want to repeat a row or two of that table as a ...

Discover More

Professional Development Guidance! Four world-class developers offer start-to-finish guidance for building powerful, robust, and secure applications with Excel. The authors show how to consistently make the right design decisions and make the most of Excel's powerful features. Check out Professional Excel Development today!

More ExcelTips (ribbon)

Converting Numbers Into Words

Write out a check and you need to include the digits for the amount of the check and the value of the check written out ...

Discover More

Setting Column Width in a Macro

Does your macro need to change the width of some columns in a worksheet? Here's how to do it.

Discover More

Replacing Some Formulas with the Formula Results

Macros are often used to process the data stored in a worksheet. Some of these processing needs can be pretty specific to ...

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 6Mpixels. 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 two more than 9?

2018-08-06 14:52:26

willy Vanhaelen

Disregard my previous comment. Further testing showed that the UDF doesn't provide reliable results and can not be used as an alternative for the one of this tip.


2018-08-04 09:15:41

Willy Vanhaelen

Here is a one-liner that does the job of this tip's big UDF just as well:

Function ExcelInstances() As Long
ExcelInstances = GetObject(, "Excel.application").Workbooks.Count
End Function


2018-08-04 00:14:36

Dave

Richard, the reason for the error messages is due to the fact that the workbook "FullName" required for workbooks that have previously been saved.
Eg:
Dim xlApp As Excel.Application
Set xlApp = GetObject("C:\Users\username\Desktop\ExampleBook.xlsx").Application


2018-03-21 04:24:57

Hans Hallebeek

Is there an explanation for this:
When I run the ExcelInstances function it returns 4
Does this mean that the count includes the loaded Add-Ins as instances and the Personal.xlsb each as a separate instance?
I run a xlam file that I use as interface to run several project specific workbooks.
I I do not open any workbooks and have the vba editor open and execute the function I get a count of 4

So if I really want to know what other instances besides these, I should then first make a count of loaded AddIns and the personacl xlsb and substratct these as well as the xlam I opened.

Is this assumption correct?

Thank you in advance for your thoughts.

Regards,
Hans


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.