Excel instances

Misca

Well-known Member
Joined
Aug 12, 2009
Messages
1,748
Office Version
  1. 365
Platform
  1. Windows
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

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.
What does the macro do? It is quite possible to manipulate a file in another instance of Excel.
 
Upvote 0
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:
Upvote 0
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?
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,484
Messages
6,113,923
Members
448,533
Latest member
thietbibeboiwasaco

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
Back
Top