Always open xlsx files in the already-open instance of Excel

[nevyoung]

Hi - when I click on an xlsx in Explorer, I want it to open in the Excel instance that is already open - NOT in a new instance of Excel. (99% of the google search results are for people who want it to open in separate instances - my excel does that all the time and I don't want that.) The only way I can have all spreadsheets in the same window is to open them from File / Open or File /Recents.

Before I upgraded to Win10 in Jan 2023, they DID open in the already-open instance. I also used Task Scheduler to open different files at certain times (to prompt me to add certain info). The Scheduler triggers a vbs script (see below) that checks if an instance is already open. If an instance is NOT open, the script opens an instance before opening the file I want opened. (I think I found that script on this forum 10 years ago but cannot find it now to see exactly what the the poster intended it to do. )

In addition, I use an add-in which puts tabs above each opened file - can't think why file tabs are not standard anyway. I can see at a glance which files are open and switch to them in a single click without multiple clicks or keystroking through the View menu. Opening from Explorer or vb script opens the file in a separate instance (which I don't want) but will NOT include the Tab add-in.

I have tried so many combinations of the open options in the FileTypes manager such as , /n, "%1", [open("%1"] without success.

I can run Excel from Start Menu or from a shortcut and each time it opens a new instance - as expected - but DOES include the add-in. Then subsequently when opening a xlsx from Explorer, it opens a new instance WITHOUT the add-in.

So - what I want is to be able to trigger the scheduled opening of a spreadsheet AND have it open in the instance that is currently open (or simply open an Excel instance as usual when opening an xlsx) AND have the Tab add-in.

Using Office 2010 in Win10.

Hope what I have described is clear. Thanks in anticipation ................................................

*******************************************
dim myXL
dim myWkbk
Dim XLWasRunning

xlwasrunning = true
On Error Resume Next
Set myXL = GetObject(, "Excel.Application")
If Err.Number = 429 Then
set myXL = createobject("excel.application")
xlwasrunning = false
end If
on error goto 0

myxl.visible = true 'nice for testing

set mywkbk = myxl.workbooks.open("D:\xxxxx\xxxxx..xlsx")
***********************************************

set mywkbk = nothing
Set myxl = nothing

 

[Tom Urtis]

There is at least 1 strangeness (my just-invented word) with your post.

I have never seen a default behavior of a new instance being created when just opening a workbook programmatically from the executed code in an Excel workbook. It opens in the instance as the (presumably) code-containing workbook that's executing the code (but see my last sentence). There are methods to create and open a workbook in its own instance, but no need to do the opposite (open in the host file's instance) because that is what will happen by default.

You could programmatically determine the instance of interest and open the workbook to that instance, but unless I am missing something obvious, which can and has been the case many times in my career, this just seems out of the ordinary to the point of suggesting that something else is going on with your set-up that is either not being mentioned or about which you are unaware.

Finally, the code you posted shows that you are well experienced with Excel and VBA but I'll suggest anyway, if you are using an add-in, I (if it were me) would expect that the to-be-opened workbook would not be involved with or reside in the same instance as the add-in file.

 

[nevyoung]

There is at least 1 strangeness (my just-invented word) with your post.

I have never seen a default behavior of a new instance being created when just opening a workbook programmatically from the executed code in an Excel workbook.

Thanks Tom - When you mean 'programmatically', I suspect you are referring to opening it from within Excel via Open or Recent. I intended saying in my post that that is the only way I can have a file open in the already-opened instance.

It opens in the instance as the (presumably) code-containing workbook that's executing the code (but see my last sentence). There are methods to create and open a workbook in its own instance, but no need to do the opposite (open in the host file's instance) because that is what will happen by default.

Perhaps I need to better understand the terminology and the opening process - eg 'code-containing workbook' and 'open a work book in its own instance'

You could programmatically determine the instance of interest and open the workbook to that instance,

I thought that is what the VB script is meant to do.

but unless I am missing something obvious, which can and has been the case many times in my career, this just seems out of the ordinary to the point of suggesting that something else is going on with your set-up that is either not being mentioned or about which you are unaware.

I am very sure that there is much I am not aware of. Not sure what else I could mention.

Finally, the code you posted shows that you are well experienced with Excel and VBA but I'll suggest anyway, if you are using an add-in, I (if it were me) would expect that the to-be-opened workbook would not be involved with or reside in the same instance as the add-in file.

My only expertise with VBA is having had some programming experience, so can more-or-less figure out what code is trying to do. Will play around with this code to isolate the problem. Being in my 70s now, I am lucky to be working in a sw development environment, so have several expert programmers I can bother -or 'bug'.