Excel COM Application.Visible to target specific workbook

[DRocks]

How do you make ExcelWorkbook1.xslx visible and keep ExcelWorkbook2.xlsx invisible when both belong to the same Excel Application object ?

XlApp.Visible := True will automatically bring every workbooks in the xlApp object visible together. How do you target only workbook1 or workbook2 with the Visible property ?

MS lead me to confusion with their help file here and what I tried does not suceed.
https://docs.microsoft.com/en-us/office/vba/api/excel.application.visible

I am using this class to help me get handle to the 3 main Excel object:
https://www.autohotkey.com/boards/viewtopic.php?f=6&t=69841

For example I've tried things like :
xlWB := ExcelCOM.GetWorkbook(FichierKM)
xlWB.Windows(fileName).Visible := true
It has worked but is very unreliable and I can't understand what Excel expects.. I'm still reading through their object model trying to get clues

Thanks in advance

[DRocks]

I found a way that feels hacky to me but here's the sample. Is that even close to a good way of doing it ?

Code: Select all

;...
if (!xlApp.Visible && WindowShouldBeVisible) {
    xlApp.Visible := true
}
xlWB.Windows(FileName).Visible := WindowShouldBeVisible
;...

What's wierd about it is that the window is opened for a fraction of time and closed when the xlWB.Windows(FileName) is called.
What do you think about it and how would you approach this situation ?

[flyingDman]

You are not stating what WindowShouldBeVisible is, but it should resolve to True or False. I have done some reading and could not find a better way. Call it "hacky" or something else as long as there is a way get it to work reliably... One thing is clear, making the app visible or not (i.e xl.visible := True / False) and making a workbook visible or not (i.e xl.Windows("filename").Visible := True / False) are 2 different things. So, if you need to open two excel files, one of which needs to visible and the other not, you need to include xl.visible := true but you can do it after hiding one of the workbooks to avoid having the second file "flash". So this would be a way to do it:

Code: Select all

Xl := ComObjCreate("Excel.Application") 		
Xl.Workbooks.Open("C:\Spreadsheets\test 123.xlsx")
Xl.Workbooks.Open("C:\Spreadsheets\test 456.xlsx")
; XL.Windows("test 123.xlsx").Activate                                 ;not needed
Xl.Windows("test 456.xlsx").Visible := False
Xl.visible := true

or, if there are a bunch of workbooks open, this would be a way to hide all but one:

Code: Select all

Xl := ComObjCreate("Excel.Application") 				            
for x in xl.Workbooks
	if (x.name != "test 123.xlsx")                                   ;keep this one visible 
		xl.Windows(x.name).visible := false

To activate a hidden workbook, you'll have to make it visible first, then to activate it:

Code: Select all

Xl.Windows("test 456.xlsx").visible := true
XL.Windows("test 456.xlsx").activate

[DRocks]

@flyingDman
Thanks a lot FlyingDman.

you need to include xl.visible := true but you can do it after hiding one of the workbooks to avoid having the second file "flash".

Ah that's exactly what I wanted to acheive, nice.

To activate a hidden workbook, you'll have to make it visible first, then to activate it

Nice find too, I didn't know that and was infact trying to activate a hidden workbook before showing the window in my current code.

I'll put all your examples in the class I'm using to make it work like you've shown here.
I really appreciate your help thanks again.