Excel instances

Misca

Well-known Member
Joined
Aug 12, 2009
Messages
1,588
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!
 

Some videos you may like

Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
35,378
Office Version
365, 2019, 2016, 2010
Platform
Windows, 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,588
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
35,378
Office Version
365, 2019, 2016, 2010
Platform
Windows, 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,588
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
35,378
Office Version
365, 2019, 2016, 2010
Platform
Windows, 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.
 

Subscribe on YouTube

Watch MrExcel Video

Forum statistics

Threads
1,106,212
Messages
5,509,865
Members
408,757
Latest member
Jamarr123

This Week's Hot Topics

  • Turn fraction around
    Hello I need to turn a fraction around, for example I have 1/3 but I need to present as 3/1
  • TIme Clock record reformatting to ???
    Hello All, I'd like some help formatting this (Tbl-A)(Loaded via Power Query) [ATTACH type="full" width="511px" alt="PQdata.png"]22252[/ATTACH]...
  • TextBox Match
    hi, I am having a few issues with my code below, what I need it to do is when they enter a value in textbox8 (QTY) either 1,2 or 3 the 3 textboxes...
  • Using Large function based on Multiple Criteria
    Hello, I can't seem to get a Large formula to work based on two criteria's. I can easily get a oldest value based one value, but I'm struggling...
  • Can you check my code please
    Hi, Im going round in circles with a Compil Error End With Without With Here is the code [CODE=rich] Private Sub...
  • Combining 2 pivot tables into 1 chart
    Hello everyone, My question sounds simple but I do not know the answer. I have 2 pivot tables and 2 charts that go with this. However I want to...
Top