VBA - freeze pane on different file

Question

I'm using Excel 2010 to for some automation.

In short, I create a new workbook with this method:

With CreateObject("Excel.Application")
    Set NewBook = .Workbooks.Add
    .Visible = True
End With

With NewBook
    Set WS = NewBook.Sheets("Sheet1")
End With

And now I would like to create a freeze pane.

I've tried using select and ActiveWindow as on this page:

WS.Range("F4").Select
ActiveWindow.FreezePanes = True

Somehow when editing different files, .select method always selects the original file instead of the added book.

I then looked at this page and tried:

NewBook.activate
With ActiveWindow
    If .FreezePanes Then .FreezePanes = False
    .SplitColumn = 5
    .SplitRow = 4
    .FreezePanes = True
End With

No use, freeze pane is created on the original file.

Nor does the following work:

With NewBook
    If .FreezePanes Then .FreezePanes = False
    .SplitColumn = 5
    .SplitRow = 4
    .FreezePanes = True
End With

Not sure if my .activate method is wrong, or ActiveWindow is wrong, or maybe .select is wrong. Lots of thanks anyone could help.

Also, to create freeze pane, it seems VBA always requires ActiveWindow, is there a way to get around it? — PAK S, Aug 15 '16 at 14:54
No. It's a property of the window, not the worksheet. You need to use `NewBook.Application.ActiveWindow.FreezePanes = True` or `NewBook.Windows(1).FreezePanes = True` — Rory, Aug 15 '16 at 15:00
You're creating a new instance of Excel, so you need to reference that instance explicitly. — Tim Williams, Aug 15 '16 at 15:08
Thanks @Rory and Tim, it works as I use NewBook.Application‌.ActiveWindow — PAK S, Aug 15 '16 at 15:33

score 0 · Answer 1 · answered Aug 15 '16 at 15:05

It's times like this that I try to debug the code through the selective use of msgbox statements. Trying placing a msgbox statement before you run the newbook.activate or the with loop, and outputting the name of the current workbook or worksheet. That should tell you which workbook Excel sees as currently active.

Also... https://msdn.microsoft.com/en-us/library/office/ff835568.aspx and https://msdn.microsoft.com/en-us/library/office/ff195422.aspx

maybe excel is not recognizing the name of the new workbook that you create. It also looks like each workbook has their own index however in the order in which they're created. Perhaps try referring the workbook by index.

score 0 · Answer 2 · answered Dec 08 '16 at 23:25

Instead of using ActiveWindow qualify the first (actually unique) window of the new workbook. There is a prerequisite if the workbook has multiple sheets; you need to activate the desired sheet in the workbook to have the FreezePanes applied to the correct worksheet in the workbook.

NewBook.Sheets("DesiredSheet").Activate
With NewBook.Windows(1)
    If .FreezePanes Then .FreezePanes = False
   .SplitColumn = 0
   .SplitRow = 1
   .FreezePanes = True
End With

VBA - freeze pane on different file

2 Answers2