Solved: VBSCript created XLS and Freezing Panes

Create Account

Microsoft Excel

Questions

Followers

Top Experts

Lee W, MVP🇺🇸

VBSCript created XLS and Freezing Panes

I'm trying Freeze Panes in an Excel file I'm creating from a vb script but am having some difficulty with getting the object references correct.

For reference (this is not the exact code; I'd have to sanitize it first to post, but I'm hopefully providing enough for your reference for now):
Worksheet = 2
IndexNumberCol = 2
Set ExcelObject = CreateObject("Excel.Application")
Set WorkbookObject = ExcelObject.Workbooks.Add()
Set WorksheetObject = ExcelObject.ActiveWorkbook.Worksheets(Worksheet) 'There are 3 worksheets created

'This all works below
      WorksheetObject.Cells(1,1).Value = SignetCompanyName & " - " & WorkSheetName & " - " & StartDate & " thru " & EndDate
      WorksheetObject.Cells(1,1).HorizontalAlignment = -4108      '(-4152 = right, -4131 = Left)
      WorksheetObject.Range(WorksheetObject.Cells(1,1), WorksheetObject.Cells(1, LastColumn + Offset)).Merge
      WorkSheetObject.Cells(1,1).Font.Size = 24
      WorkSheetObject.Cells(1,1).Font.Bold = True
      WorkSheetObject.Cells(1,1).EntireRow.Autofit

FreezePanes(2)

'This is choking - usually on the Select...
Sub FreezePanes(SheetNumber)
      'WorksheetObject.Range(WorksheetObject.Cells(Row + 1, IndexNumberCol + 1)).ActiveSheet.FreezePanes = True
      ExcelObject.ActiveWorkbook.Worksheets(SheetNumber).Rows(Trim(CStr(Row + 1)) & ":" & Trim(CStr(Row + 1))).Select
      ExcelObject.ActiveWorkbook.Worksheets(SheetNumber).ActiveWindow.FreezePanes = True
      ExcelObject.ActiveWorkbook.Worksheets(SheetNumber).Range("A1").Select
End Sub

I want to reiterate, there's a LOT more code than this, but I don't have time to sanitize it to post. If something jumps out, please let me know. Thanks.

Zero AI Policy

We believe in human intelligence. Our moderation policy strictly prohibits the use of LLM content in our Q&A threads.

Ryan Chong🇸🇬

try this:

Sub FreezePanes(SheetNumber)
    ExcelObject.ActiveWorkbook.Worksheets(SheetNumber).Range("A" & Trim(CStr(Row + 1))).Select
    ExcelObject.ActiveWorkbook.Worksheets(SheetNumber).Parent.Windows(1).FreezePanes = True
    ExcelObject.ActiveWorkbook.Worksheets(SheetNumber).Range("A1").Select
End Sub

Open in new window

ste5an🇩🇪

hmm, Row is undefined in your snippets.. So are other variables.
Also what kind of pane freeze do you want? Top rows or custom panes?

E.g.VBA

Option Explicit

Public Sub test()

  ActiveWorkbook.Sheets("Sheet3").Select
  FreezePanes ActiveWorkbook.Sheets("Sheet1")
  FreezePanes ActiveWorkbook.Sheets("Sheet2"), "C3"

End Sub

Public Sub FreezePanes(ByVal CWorksheet As Excel.Worksheet, Optional ByVal CSplitAddress As String = "")

  CWorksheet.Select
  If CSplitAddress = "" Then
    ' Top Row
    CWorksheet.Parent.Windows(1).SplitColumn = 0
    CWorksheet.Parent.Windows(1).SplitRow = 1
  Else
    ' Custom
    CWorksheet.Range(CSplitAddress).Select
  End If
  
  CWorksheet.Parent.Windows(1).FreezePanes = True
  CWorksheet.Range("A1").Select
  
End Sub

Open in new window

As VBScript:

Sub FreezePanes(CWorksheet, CSplitAddress)

  CWorksheet.Select
  If CSplitAddress = "" Then
    ' Top Row
    CWorksheet.Parent.Windows(1).SplitColumn = 0
    CWorksheet.Parent.Windows(1).SplitRow = 1
  Else
    ' Custom
    CWorksheet.Range(CSplitAddress).Select
  End If
  
  CWorksheet.Parent.Windows(1).FreezePanes = True
  CWorksheet.Range("A1").Select
  
End Sub

Open in new window

And for your code:

  Set WorksheetObject = ExcelObject.ActiveWorkbook.Worksheets(Worksheet)   'There are 3 worksheets created

Open in new window

The number of sheets created is an Excel setting. This is not a constant. Thus don't assume that every users has this set to 3. Especially since Excel 2016 you'll find often 1 as corporate default.

Lee W, MVP🇺🇸

ASKER

I haven't been to the client today to test Ryan's suggestion.

To answer Ste5an's questions:
Row is a variable that starts as a defined integer and is used in various loops. so that excel rows increase appropriately as the data is populated.

The frozen panes would be lines below the Row variable and to the columns to the Left of and including the IndexNumberCol.

EARN REWARDS FOR ASKING, ANSWERING, AND MORE.

Earn free swag for participating on the platform.

ste5an🇩🇪

It's almost always a bad idea to use such global variables.. it makes code reuse harder then necessary and testing is also harder than necessary. Make it a parameter:

Sub FreezePanes(CWorksheet, CSplitRow)

  CWorksheet.Select
  CWorksheet.Parent.Windows(1).SplitColumn = 0
  CWorksheet.Parent.Windows(1).SplitRow = CSplitRow
  CWorksheet.Parent.Windows(1).FreezePanes = True
  CWorksheet.Range("A1").Select
  
End Sub

Open in new window

Lee W, MVP🇺🇸

ASKER

In general, I agree. But in VBScripts, it seems all variables are global variables.

ste5an🇩🇪

Sometimes, yes. But more for invariants or constants.

When you see a strong need for global variables, then use classes.

Get a FREE t-shirt when you ask your first question.

We believe in human intelligence. Our moderation policy strictly prohibits the use of LLM content in our Q&A threads.

Lee W, MVP🇺🇸

ASKER

@Ryan

I tried your version of the FreezePanes Sub - it fails on:
ExcelObject.ActiveWorkbook.Worksheets(SheetNumber).Range("A" & Trim(CStr(Row + 1))).Select
Error: Microsoft Excel: Select method of Range class failed

Lee W, MVP🇺🇸

ASKER

@Ste5an

The number of sheets created is an Excel setting. This is not a constant. Thus don't assume that every users has this set to 3. Especially since Excel 2016 you'll find often 1 as corporate default.

In this case, the script is creating the Excel sheet from scratch and I know this works as it's been used for labeling the sheets, among other things. But good point to make - and I would assume, if I were editing an excel sheet as opposed to creating it from scratch, the sheet order could change either inadvertently or intentionally causing this to fail miserably.

Lee W, MVP🇺🇸

ASKER

@Ryan
Also, I just tested with an echo line before verifying that the Range was being constructed properly:

ExcelObject.ActiveWorkbook.Worksheets(SheetNumber).Range("A" & Trim(CStr(Row + 1))).Select
is evaluating to:
ExcelObject.ActiveWorkbook.Worksheets(SheetNumber).Range(A1).Select

This is the exact code I'm using for the sub:

Sub FreezePanes(SheetNumber)		'Ryan: EE Q 29163471
    Wscript.Echo "ExcelObject.ActiveWorkbook.Worksheets(SheetNumber).Range(" & "A" & Trim(CStr(Row + 1)) & ").Select"
    ExcelObject.ActiveWorkbook.Worksheets(SheetNumber).Range("A" & Trim(CStr(Row + 1))).Select
    ExcelObject.ActiveWorkbook.Worksheets(SheetNumber).Parent.Windows(1).FreezePanes = True
    ExcelObject.ActiveWorkbook.Worksheets(SheetNumber).Range("A1").Select
End Sub

Open in new window

EARN REWARDS FOR ASKING, ANSWERING, AND MORE.

Earn free swag for participating on the platform.

Lee W, MVP🇺🇸

ASKER

@ste5an

I believe I've appropriately adapted your code to my script, but am getting a different error:

'Calling code:
FreezePanes ExcelObject.ActiveWorkbook.Worksheets(WorkSheetObject.Name), "B3"

Sub FreezePanes(CWorksheet, CSplitRow)	'Ste5an: EE Q 29163471
	WScript.Echo "CWorksheet is an object: " & IsObject(CWorksheet) & ", CSplitRow = " & CSplitRow
	CWorksheet.Select
	CWorksheet.Parent.Windows(1).SplitColumn = 0
	CWorksheet.Parent.Windows(1).SplitRow = CSplitRow
	CWorksheet.Parent.Windows(1).FreezePanes = True
	CWorksheet.Range("A1").Select
  
End Sub

Open in new window

Results in:

CWorksheet is an object: True, CSplitRow = B3
M:\Reporting Automation\All Clients\Report.vbs(163, 2) Microsoft Excel: Unable to set the SplitRow property of the Window class

Open in new window

ASKER CERTIFIED SOLUTION

ste5an🇩🇪

membership

Signing up is free and takes 30 seconds. No credit card required.

Create Account

Lee W, MVP🇺🇸

ASKER

Thanks ste5an, I was able to adapt your code to the project and it works great.

Microsoft Excel

Questions

Followers

Top Experts

Microsoft Excel topics include formulas, formatting, VBA macros and user-defined functions, and everything else related to the spreadsheet user interface, including error messages.