Excel MDI vs SDI

[JKwan]

This morning a user of my just updated to Office 365. This is where the fun began. All of a sudden she starts to get errors that one of the worksheets is not found?? This can be solved if she only have one instance of Excel running, however, if she has multiple instances, the macro fails. I am wonder is there a way for me to set my Workbook and Worksheets explicitly (which office 2010 does, with MDI)




set WB = workbook.open ("c:\data.xlsx") ' this runs fine
set WS = WB.Worksheets("Data") ' this fails if multiple Excel is running


How do I overcome of this problem, for the time being, I told her to only have one Excel running :-(.

Thanks

[JKwan]

bump

[Aflatoon]

Your code as posted is explicit (although wrong as it's Workbooks.Open not Workbook.open) and should not be affected by MDI/SDI or how many application instances you have open. What is the exact code and what is the actual error?

[JKwan]

You are right about Workbooks.Open, that was only a typo on my part.

The error is Error 9, subscript out of range. I thought by explicitly defined my workbook, then set the worksheet would work, but that is not the case. I agree with you that should not happen, especially that I explicitly defined everything. As soon as I have one instance of excel or using excel 2010 (not office 365), runs like a charm.

Thanks.

[Aflatoon]

The only way you can get that error with the code you posted is if the data.xlsx workbook doesn't have a sheet called Data in it. Version or MDI/SDI doesn't come into it. It could come into it if you were actually using code like this:


Workbooks.Open "c:\data.xlsx"
Set WB = ActiveWorkbook
Set WS = WB.Worksheets("Data")

[JKwan]

I know what you are saying, it just does not make sense.... What I posted is what is currently in my code. Like I said in my last post, works with Excel 2010 just not Office 365!!

[Aflatoon]

Does it happen with every workbook, or just one? I have never seen that with Office 365.

[JKwan]

Sorry, I cannot answer that question. It is one of the users that updated to 365, I don’t have this problem, I am still using 2010.....
i don’t know if this make any difference or not. It is an add-in that I created. When you run the add-in, it then open up the data workbook and worksheet....

[snb]

What happens ?


Sub M_tst()
set WB = workbooks.open("c:\data.xlsx").sheets("Data")
End Sub

[Paul_Hossler]

I'm assuming that WB is Dim-ed as a Workbook, and not a Worksheet?

@JKwan -- I had this issue at work, and I ended up haveing to check if other Excel instances were running, and -- if there were -- then throwing up an error message "Close the other one(s) and try again", and then exiting

https://excelribbon.tips.net/T009452_Finding_Other_Instances_of_Excel_in_a_Macro.html




https://answers.microsoft.com/en-us/msoffice/forum/msoffice_excel-mso_win10-mso_2016/what-are-excel-instances-and-why-is-this-important/20c39a6f-0857-4033-b713-18bf72e91d8b



Separate instances have always acted independent of each other. But starting with Excel 2013, a change was implemented (SDI versus MDI) that improves the ability to work with the same Excel.exe instance across multiple monitors (based on customer requests as more and more customers started using 2 monitors). Read more and see the first 2 pictures ofthis article (https://msdn.microsoft.com/en-us/vba/excel-vba/articles/programming-for-the-single-document-interface-in-excel)
. The reason this is mentioned is that because of this change, you might get the impression that there are 2 instances running, while there is actually just 1.

Additionally, a change was made starting with September 2017 updates for Office, resulting in separate instances when opening multiple files simultaneously when no Excel process is already running.

[JKwan]

@snb - I will recode my module and have the person give it a test and let you know.
@Paul - Yes, I explicitly dimmed WB as workbook and WS as worksheet. Thanks for the article, I will give it a read.

[JKwan]

Paul, the article gave me an idea perhaps may solve my problem, I will know on Monday when the user is back so I can test it. A big thank you.

[JKwan]

Looks like it worked based on the article, thanks Paul

[Paul_Hossler]

Glad you got it

Can you post your solution so that it might help others?

[JKwan]

This is what I did to solve my problem. For me, I checked the version number, if > 14, I execute below code, otherwise, I launch he old way. Thanks again

Set xlApp = CreateObject("excel.application")
#If MBU = 1 Then
Set WBHeadSwing = xlApp.Workbooks.Open("I:\scada\dailyreadings\Head_Swing_Dates_2018_05_07.xlsm")
#Else
Set WBHeadSwing = xlApp.Workbooks.Open(cDirectory & "Head_Swing_Dates_2018_05_07.xlsm")
#End If
xlApp.Visible = True
Set WSHeadSwing = WBHeadSwing.Worksheets("Data")