Freeze Panes in Excel from Access

[TimTDP]

I need to freeze the panes in an Excel spreadsheet form within Access
I have the following code:

Dim ApXL As Object
Dim xlWBk As Object
Dim xlWSh As Object

Set ApXL = CreateObject("Excel.Application")
Set xlWBk = ApXL.Workbooks.Add
Set xlWSh = xlWBk.Worksheets(1)

With xlWSh.Range("B5")
    ActiveWindow.FreezePanes = True
End With

The codes runs, but the panes are not frozen. (I got this from the Excel macro recorder)

I need both panes frozen

Where is my error?

Many thanks in advance

 

[Brianwarnock]

This is one of those situations where I believe you do need to use Select as you are acting on the spreadsheet view.

First activate the worksheet
Then select the cell
Then freeze the window

Never done this but
XlWsh.activate
Range("B5").Select
Activewindow.freezepanes=true

Is my guess

Brian

 

[Rx_]

Do this all the time, that answer is exactly correct.
Here is a sample of my working code

1270    objXL.Rows((intRowPos - 1) & ":" & (intRowPos - 1)).Select              ' based on relative position for where data starts 
1280    objXL.Selection.AutoFilter
1300    objXL.Rows((intRowPos) & ":" & (intRowPos)).Select  ' first Data row to freeze payne
1310    objXL.ActiveWindow.FreezePanes = True
        ' **** auto fit again - filter cuts off last letters
1320    objXL.Cells.EntireColumn.AutoFit      ' did this to show arrows in filter

 

[TimTDP]

Thanks guys

Suppose I get used to knowing when to use "Select" and when not too!

 

[Brianwarnock]

Thanks guys

Suppose I get used to knowing when to use "Select" and when not too!

Let me know when you have a definitive answer.

My take on it is that the Select approach always works, but as vbaInet said on another thread it is fractionally slower, this is because it keeps interacting with the spreadsheet, which is why recorded macros use it, but in a sub you only need the spreadsheet interaction at the end.
However occasionally you have to interact with the spreadsheet. In your example you needed to activate the worksheet, then select the reference for the split for the freeze you were not actually using any info from the cell, the freezepanes acts on the active window not on a nominated worksheet, hence the need to activate the sheet.
Hope that is a bit clearer than mud.

Brian

 

[Brianwarnock]

FWIW
the reason your select method failed in your previous thread was. I think . because you said

With xlWSh.Selection
instead of
With Selection

xlWSh had already been defined as part of the range, but yes the solution from Mile-O was better.

Brian