Force Macro to Open Excel File in New Instance

[DougRobertson]

Hello,

Just ran into a situation where I now need a Macro to open an Excel file in a new Instance, instead of just the regular way. It's so that 2 Excel files can be compared to each other, side by side, in completely different Windows.

Is that possible?

Thanks as always in advance - Mr. Excel is the greatest!

~ DWR

 

[Tom Urtis]

Maybe this:

<code>
Sub NewInstance2()
Dim NewExcel As Object
Set NewExcel = New Excel.Application
With NewExcel
.Visible = True
.Workbooks.Open "C:\Your\File\Path\YourFileName.xlsx"
AppActivate .Caption
End With
End Sub
</code>

If you want the first instance on top, maybe this:

<code>
Sub NewInstance3()
Dim NewExcel As Object
Set NewExcel = New Excel.Application
With NewExcel
.Visible = True
.Workbooks.Open "C:\Your\File\Path\YourFileName.xlsx"
End With
AppActivate Application.Caption
End Sub
</code>

 

[MrIfOnly]

Doug,

For what it's worth, I tried Tom's code using Excel 2016 on Windows10 and the new instance of Excel opened on top of the workbook running the code.

Sorry I couldn't be of more assistance.

Regards,

CJ

Edit: Tom posted his new code before I posted this, so I should specify: Tom's original code worked for me.

 

[DougRobertson]

CJ: With Tom's earlier code I got it to do that as well, but it was not consistent, and I couldn't figure out what made go one way one time, and another way the next time.

Tom: Just ran a lot of tests with your new addition, and it looks like that nailed it - 100% of the time!

Now since we're in the same section, is there a way to also Save the document in the Second Instance? I've tried a

    .Save

and

    .Save "YourFileName.xlsx"

and some variations of those, but to no avail!

I'd like it to Save it right after opening it - also, it's actually an ".xlsm" file if that makes any difference, but not a template.

This is the final piece of the puzzle!

Thanks again guys!!

 

[GTO]

Presuming you are using either variation of Mr. Urtis' code at post #11, I would study what belongs to the With...End With currently in play. Seeing as how that turns out to be a reference to Excel.Application and we can't very well .Save the App, then how would you set a reference to the opening workbook, so you could save it?

Mark

 

[Tom Urtis]

Seeing as you want to save the workbook when it opens in that other instance, can you not put an Open event in that workbook for that purpose, example,

<code>
Private Sub Workbook_Open()
ThisWorkbook.Save
End Sub
</code>

If you don't want to do that, this modification should work:

<code>
Sub NewInstance4()
Dim NewExcel As Object
Set NewExcel = New Excel.Application
With NewExcel
.Visible = True
.Workbooks.Open "C:\Your\File\Path\YourFileName.xlsm"
.Workbooks(1).Save
AppActivate .Caption
End With
End Sub
</code>

 

[DougRobertson]

That was it! Many thanks Tom!
I've been able to create Macros that do amazing things, but being self-taught, I didn't get some of the basics ... okay, a LOT of the basics!
It's because of many kind offers of help from generous people like you that I'm able to learn and grow.
Next time I'm in SF, dinner's on me!

~ Doug