FreezePanes in Excel spreadsheet from within Access

I've got a subroutine that exports data from Access to Excel and then (thanks to rorya) formats that data.

The only thing that isn't working is that I'm unable to use FreezePanes so that the top two rows and left two columns are always visible.  Both techniques (mine and rorya's) in the original post are successful at freezing the left two columns, but neither freezes the top two rows.

LVL 50
Dale FyeAsked:
Who is Participating?
pteranodon72Connect With a Mentor Commented:

I would make sure FreezePanes is off before selecting the pivot cell and turning it on:

If ActiveWindow.FreezePanes = True Then
    ActiveWindow.FreezePanes = False
End If
ActiveWindow.FreezePanes = True

Also, if you ever have a direct reference to a window, use it instead of ActiveWindow to avoid confusion/errors.

Rey Obrero (Capricorn1)Commented:
try this simple codes

Dim xlApp As Object
Set xlApp = CreateObject("excel.application")
    xlApp.workbooks.Open <path to excel file>
    With xlApp
        .ActiveWindow.FreezePanes = True
    End With

with your codes

'    sht.Range("C3").Select
'    xlapp.ActiveWindow.FreezePanes = True

    With xlApp
        .ActiveWindow.FreezePanes = True
    End With
Hi, fyed.

In your previous solution, please try replacing line 109 ("  xlapp.ActiveWindow.FreezePanes = True") by...

xlapp.ActiveWindow.SplitColumn = 2
xlapp.ActiveWindow.SplitRow = 2
xlapp.ActiveWindow.FreezePanes = True

Open in new window

Dale FyeAuthor Commented:
capricorn1:  Tried that method, same results

redmondb:  This is one of the EE responses I tested before posting, but I want to freezepanes, not split.

PT72:  That seems to have resolved the issue, although I cannot figure out why.
NorieVBA ExpertCommented:
This has worked for me.
Sub FreezeExcel()
Dim appXL As Object
Dim wbXL As Object
Dim wsXL As Object
Dim windowXL As Object

    Set appXL = CreateObject("Excel.Application")
    Set wbXL = appXL.Workbooks.Open("C:\FreezeTest.xlsm")
    Set wsXL = wbXL.Worksheets("Data")
    appXL.Goto wsXL.range("A1"), True
    appXL.Goto wsXL.range("B2")
    Set windowXL = appXL.ActiveWindow
    windowXL.FreezePanes = True
    wbXL.Close True
    appXL.Visible = True
    Set appXL = Nothing
End Sub

Open in new window

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.