Finding Other Instances of Excel in a Macro

You are missing an underscore at the end of the 2nd line in this part:

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

This is NOT counting/listing Excel instances. This is counting the number of Excel *windows* - and in 2016+ each workbook is a new window but not necessarily a new instance.

This may have appeared to be correct in 2014, since workbooks opened in the same window, but still, it was only listing windows, not instances.

Also note the link to the original is broken.

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.

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

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

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

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)

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?

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!

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???

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,