How to freeze rows in Excel using VBScript?

[b82klo]

Hi everyone!

I have a script that fills out Excel cells with info from AD. (Names of computer objects, computer pwd age, if the computer reply a ping request, OS, group membership and so on). What I would like to do is to freeze the spread sheet at row 5 so that the first 5 rows are fixed, and that the rest of the rows further down the sheet will scroll making the first 5 rows a fixed heading.

Anyone?

Thank You!

 

[b82klo]

Tec-tips really work. All I had to do was to post the thread, and then it suddenly came to me.

There is still a thing that puzzles me. I don’t understand why I have to say

 objRange2.Range("A1").Select

to get it to get it to freeze the rows at row5. If I say

 objRange2.Range("A6").Select

the freeze will be at row 9??? Anyone know why?
Here is a sample of my playing with Excel if anyone else has the same problem as I had:

Option Explicit

Const xlAscending = 1
Const xlYes = 1

Dim objExcel, objWorkbook, objWorksheet, objRange, objRange2
Dim intColumns

Set objExcel = CreateObject("Excel.Application")
Set objWorkbook = objExcel.Workbooks.Add
Set objWorksheet = objWorkbook.Worksheets(1)
objExcel.Visible = True

objExcel.Cells(2, 1).Value = "Heading 1 - Time: " & Hour(Now) & ":" & Minute(Now)
objExcel.Cells(3, 2).Value = "Heading 2"

objExcel.Cells(5, 1).Value = "Number"
objExcel.Cells(6, 1).Value = "9"
objExcel.Cells(7, 1).Value = "2"
objExcel.Cells(8, 1).Value = "60"
objExcel.Cells(9, 1).Value = "0"
objExcel.Cells(10, 1).Value = "8"
objExcel.Cells(11, 1).Value = "2"
objExcel.Cells(5, 2).Value = "Letter"
objExcel.Cells(6, 2).Value = "B"
objExcel.Cells(7, 2).Value = "C"
objExcel.Cells(8, 2).Value = "P"
objExcel.Cells(9, 2).Value = "K"
objExcel.Cells(10, 2).Value = "J"
objExcel.Cells(11, 2).Value = "O"
objExcel.Cells(5, 3).Value = "More Letters"
objExcel.Cells(6, 3).Value = "n"
objExcel.Cells(7, 3).Value = "d"
objExcel.Cells(8, 3).Value = "o"
objExcel.Cells(9, 3).Value = "e"
objExcel.Cells(10, 3).Value = "w"
objExcel.Cells(11, 3).Value = "x"

' Autofit the column with the first 3 columns
For intColumns = 1 To 3
	objExcel.Columns(intColumns).AutoFit()
Next

' Change font size and setts the font to bold
objExcel.Cells(2,1).Font.Size = 32
objExcel.Cells(2,1).Font.Bold = True

' Sorts the cells in th spread sheet
Set objRange = objWorksheet.Range("A6")
Set objRange2 = objExcel.Range("A6")
objRange.Sort objRange2, xlAscending, , , , , , xlYes

' Freeze the spread sheet after row 5
objRange2.Range("A1").Select
objExcel.ActiveWindow.FreezePanes = "True"

 

[b82klo]

Same thing again. I just posted a reply to my own thread, and then realized that the problem was that the objRange2 was already set to A6. If I just reset the objRange2 it is easier to know where the freeze will be. Here goes a reawritten last section that will fix the row numbering confusion:

' Freeze the spread sheet after row 5
Set objRange2 = objExcel.Range("A1")
objRange2.Range("A6").Select
objExcel.ActiveWindow.FreezePanes = "True"

 

[SkipVought]

Hi,

Check out which OBJECT you are using...

[b]'this is the APPLICATION object[/b]
Set objExcel = CreateObject("Excel.Application")
[b]'this is the WORKBOOK object using your APPLICATION object[/b]
Set objWorkbook = objExcel.Workbooks.Add
[b]'this is the WORKSHEET object using your WORKBOOK object[/b]
Set objWorksheet = objWorkbook.Worksheets(1)
objExcel.Visible = True
[b]'why are you using your APPLICATION object as the parent to the cells range object???
'you ought to be using your WORKSHEET object!!![/b] 
[s]objExcel[/s][b]objWorksheet[/b].Cells(2, 1).Value = "Heading 1 - Time: " & Hour(Now) & ":" & Minute(Now)

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]