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

A Picture Is Worth a Thousand Words

Nothing beats a screen shot when you are trying to convey information about using the computer. With just a couple of easy ...

Discover More

Setting a Default Document Format

Word allows you to save your documents in a variety of different formats. You can specify the format when you actually save, ...

Discover More

Entering Large Time Values

If you need to input humongous times into a worksheet, you may run into a problem if you need to enter times greater than ...

Discover More

Create Custom Apps with VBA! Discover how to extend the capabilities of Office 2013 (Word, Excel, PowerPoint, Outlook, and Access) with VBA programming, using it for writing macros, automating Office applications, and creating custom applications. Check out Mastering VBA for Office 2013 today!

More ExcelTips (ribbon)

Saving Information in a Text File

The VBA programming language provide with Excel allows you to create and modify text files quite easily. Here's how to open a ...

Discover More

Making Modal Dialog Boxes Appear in Front of Workbooks

Perhaps the most common way of communicating with programs is through the use of dialog boxes. We expect dialog boxes to be ...

Discover More

Debugging a Macro

Part of writing macros is to make sure they work as you expect. This involves a process known as debugging. Here's how you ...

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 seven less than 7?

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.