FreezePanes without ActiveWindow

[gauntletxg]

I'm automating some Excel tasks from another language (AutoIt), and have hit a snag with freezing panes. The basic structure of the AutoIt code is

$xlApp = ObjCreate("Excel.Application")
If Not IsObj($xlApp) Then Exit
With $xlApp
     .Visible = False
     ; A bunch of stuff
     .Quit
EndWith

Everything works fine, except the code I have in my Excel macro to freeze panes doesn't work, as the application is not visible and thus there's no active Excel window to work with. Is there a way I can freeze panes in this context? The only thing I can think of is ActiveSheet.FreezePanes, but that doesn't work.

Thanks.

 

[Tom Urtis]

OK, there are some idiosyncrasies at work here, which can get confusing for this to work in your case, because the full context of your code is not evident.

FreezePanes is a property of a Window and of the ActiveWindow. Note, key words here (believe it or not) are "a" and "the".

The ActiveWindow is the window that is active, meaning it needs to be activated (selected) for it to be active, as there can only be one ActiveWindow in a single instance of Excel, hence the significance of the word "the".

A Window can be another workbook in the same instance of Excel, or a second Window in the same workbook such as if you click Window > New Window for the active workbook. In that respect, a Window does not need to be active for FreezePanes to be set. However, if for example you have Sheet1 active and you create a new window, and in that window you activate Sheet2, then re-activate your first (original) window to be on Sheet1 and you exectute
Windows(2).FreezePanes = True
it will freeze panes on Sheet2 in the second window but not Sheet2 on the window you are actively on.

So in your case, where this leaves you might be right smack in the middle, where you are creating an Excel object, which (and this is where I cannot tell) may or may not be in the same instance of Excel. If you are creating a seperate instance, that instance will not be recognized as a Window in the Windows collection of your active workbook.

If the Excel app yuou are creating is a part of your current active workbook's Window collection, you could execute this codeline to freeze panes on that unselected Window app...
Windows(2).FreezePanes = True
...assuming it is the second Window in your collection, as an example.

Otherwise, you are out of luck unless you make visible that xl object being created, make it active, and then set the Freeze Panes at that point.

 

[mikeyj777]

A Window can be another workbook in the same instance of Excel, or a second Window in the same workbook such as if you click Window > New Window for the active workbook.

I just want to say, its 2018, and I'm just learning this...