FreezePanes not working with ActiveWindow

I am in need of setting Range("B16") to FreezePanes with VBScript. My primary area of trouble:

Set objExcel = CreateObject("Excel.Application")
Set objRange = objExcel.Range("B16")
objRange.FreezePanes = True

The script in total is just under 1200 lines and I can accomplish formula pastings, any formatting, PageSetup parameters all with very similar code as shown above. Only FreezePanes throws an error.

That error being: Object doesn't support this property or method: 'objRange.FreezePanes'

I have added .Select to the end of my Range and added .ActiveWindows. between the range and freezepanes to no avail. Most all of my Googleing has found that adding .ActiveWindows. should resolve the problem. Unfortunately it does not.

Note I have also tried:

Set objExcel = CreateObject("Excel.Application")
SetObjWorkbook = objExcel.Workbooks.open(d:\test1.xls)
Set objWorksheet1 = objWorkbook.worksheets(1)
Set objRange = objworksheet1.Rows("16:16").Active
objRange.ActiveWindow.FreezePanes = True

Which Produces ERROR: Object doesn't support this property or method: 'objWorksheet1.Rows(...).Active'

Removing .Active produces ERROR: Object doesn't support this property or method: 'objRange.ActiveWindow'

And Removing .ActiveWindow produces: Object doesn't support this property or method: 'objRange.FreezePanes'

Your help would be greatly appreciated.

Welcome to the forum.

Those error messages tell you that the Excel.Range class doesn't have FreezePanes, ActiveWindow or Active members. To work out what you need to do you can use Excel VBA tools such as the macro recorder or the object browser, and then convert it to late binding for your VB Script.

Have a go at using the macro recorder whilst freezing the panes manually and then review the VBA code it produces to see if it helps you at all.

Hi Colin and thank you for welcoming me to the forum.

The VBA recorder produces:

Range("B16").Select
ActiveWindow.FreezePanes = True

My VBA to VBS translation goes:

Set objRange = objExcel.Range("B16")
objRange.FreezePanes = True

The errors this produces are similar to those I posted before. First error complains about the .Select then after that is removed it complains that .FreezePanes is not a class of objRange.

Am I using the wrong object to set the range? If so what should I be using?

Thanks again for the reply.

Hi,

The macro recorder code is freezing panes in the Window - not the Range - so your VBA --> VBS translation has gone a little astray there. The Window class has a FreezePanes property; the Range class does not. The VBS equivalent (after starting an Excel instance and opening the workbook) would be:


Set objWorksheet1 = objWorkbook.worksheets(1)
Set objRange = objWorksheet1.Range("B16")

objWorksheet1.Activate
objRange.Select
objExcel.ActiveWindow.FreezePanes = True

Colin,

Thank you for your advice. What ended up working for me was:

set objRange = objWorksheet1.Range("B16")
objRange.Select
objExcel.ActiveWindow.FreezePanes = True

So I used every bit of the advice you offered and it works like a champ.

Thank you so very much!

Hi,

I recommend you keep the objWorksheet1.Activate line in there (or you could have objWorksheet1.Select); otherwise, when the file was last saved, if the first worksheet wasn't the active sheet, your code will error again.

Glad it helped... :)