Excel instances

Misca

Well-known Member
Joined
Aug 12, 2009
Messages
1,697
I'm stuck with this problem and need some help from people wiser than me:

I have a userform that lets the user select the file from the open files they want to run the macro to. It works like a charm as long as all the files are open on the same Excel instance but often times the files are open in other instances:

Is there a way to loop thought all the active files (or open windows) to check that all the Excel files are open on the same instance as the macro workbook? If they're not, the file should be saved (might need to use temp names for unsaved workbooks), closed and opened in the same instance as the macro workbook.

Thank you already in advance!
 

Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
36,957
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
What does the macro do? It is quite possible to manipulate a file in another instance of Excel.
 

Misca

Well-known Member
Joined
Aug 12, 2009
Messages
1,697
The macro uses the Application.InputBox(Type:=8) to get the column numbers from the user, then loops though all the rows in the A1.CurrentRegion to get data from the given columns, places it in the calculator in the MacroWorkbook and returns the results to each row and finally creates a PivotTable using the calculated data.

The code I'm using to get the filename is as follows:
Code:
Private Sub UserForm_Initialize()
Dim WB As Workbook
Dim i As Integer

'Removes the old names from the list:
For i = CBTiedostot.ListCount - 1 To 0 Step -1
    CBTiedostot.RemoveItem (i)
Next i

'Adds the new names:
For Each WB In Workbooks
    If WB.Name <> ThisWorkbook.Name Then
        CBTiedostot.AddItem WB.Name
    End If
Next WB

End Sub
 
Last edited:

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
36,957
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
FYI, you can just use:
Code:
CBTiedostot.Clear
to clear the combobox.

You could simply open a read-only copy of the other workbook in the current instance of Excel and then save it under another name, if that is an option?
 

Misca

Well-known Member
Joined
Aug 12, 2009
Messages
1,697
I'm open to anything: The problem seems to be that the combobox list can't find the other instances of Excel => The user can't select / do anything with any of those files.

The files to be worked with are usually opened by clicking the file icon in Explorer or Outlook attachments (or it can be a new file with the data pasted from IE window).

The Excel version is 2003.
 

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
36,957
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
This may be trickier than I thought. It's easy enough to get a list of all the open workbooks in the various instances, but that only gives you the title, not the full path (so it's not possible to reopen the workbook, or grab a reference to it with GetObject) and I am struggling to think of a way to get an object reference to it from the window handle. I'll do some more digging.
 

Watch MrExcel Video

Forum statistics

Threads
1,133,576
Messages
5,659,622
Members
418,515
Latest member
tobyvb

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top