From VBA in Outlook, how do I freeze panes in Excel?

Question

I am writing a VBA macro in Outlook that references an Excel workbook and makes changes to that workbook. I want to freeze panes (not "Split") just below row 1.

Here's the code I tried:

With Excel.ActiveWindow
    .SplitColumn = 0
    .SplitRow = 3
    .FreezePanes = True
End With

...and...

Dim excApp As Object
Set excApp = CreateObject("Excel.Application")

With excApp.ActiveWindow
    .SplitColumn = 0
    .SplitRow = 3
    .FreezePanes = True
End With

Both threw run-time error 91 at the line .SplitColumn = 0.

I appreciate any help or pointers. Thanks!

Answer 1

With freeze panes, it's actually relative to the cursor position, so this is one of the few places where .Select is actually necessary.

This code should do what you seek:

Dim excApp As Excel.Application
Set excApp = GetObject(, "Excel.Application")

excApp.ActiveWorkbook.ActiveSheet.Range("2:2").Select
excApp.ActiveWindow.FreezePanes = True

It presupposes Excel is already open.

-- EDIT --

Per @ChrisB, there is a way to do this without the dreaded .Select:

With excApp.ActiveWindow
    .SplitRow = 3
    .FreezePanes = True
End With

Answer 2

Here is another one. Its a Function which you can also use outside from Excel, e.g. from Access or Outlook:

Public Sub FreezeHeader(Sheet As Excel.Worksheet, _
                        Optional Rows As Long = 1, _
                        Optional Columns As Long = 0)
    If Sheet Is Nothing Then Exit Sub

    Sheet.Activate      ' must be the active sheet!
    With Sheet.Parent.Windows(1)
        If .FreezePanes Then .FreezePanes = False ' always delete the old freeze

        .SplitColumn = Columns
        .SplitRow = Rows

        If Rows > 0 Or Columns > 0 Then .FreezePanes = True
    End With
End Sub

And now use it like this:

Dim EXL As Excel.Application
Dim Book As Excel.Workbook
Dim Sheet As Excel.Worksheet

Set EXL = New Excel.Application
Set Book = EXL.Workbooks.Open("YourFilename.xlsx")
Set Sheet = Book.Sheets(1)

FreezeHeader Sheet       ' Freezes first row (the usual case)
FreezeHeader Sheet, 2    ' Freezes first two rows
FreezeHeader Sheet, 0, 1 ' Freezes first column
FreezeHeader Sheet, 0    ' Unfreezes all