.FreezePanes

[Bob McClellan]

Hello,
I have been trying without success to get the freezepanes
to work from an ActiveX Script within an SQL DTS package.

I keep getting the error that .FreezePanes is not supported.
I have tried many variations of the following .....

..Splitcolumn = 2
..Splitrow = 11
..FreezePanes = True

any help would be much appreciated....
Thanks in advance,
bob.

 

[Jim Thomlinson]

FreezePanes is supported from the window object. so you need something like
this...

ActiveWindow.FreezePanes = True

 

[Bob McClellan]

Jim,
Thanks for the reply....

I am working in an ActiveX Script within an SQL DTS package
As far as I know, you can not use .Window or ActiveWindow...
--that's the problem

 

[NickHK]

Bob,
How are you refering to the other Excel objects then ?
Dim xlApp As Object 'Excel.Application
xlApp.ActiveWindow.FreezePanes = True

NickHK

 

[Bob McClellan]

Nick...
Thanks for the reply...

Here is part of the code...
with xlBook.Worksheets(1)
.Range("A3") = "Vendor: " & rtrim(rsDetail.fields("Vendor"))
.Range("A5") = "Machine Number: " & rtrim(rsDetail.fields("Unit"))
.Range("A7") = "Date: " & rsDetail.fields("ReportDate")

.Range("B3") = "ATLM Invoice#: " & rtrim(rsDetail.fields("AtlmInvoice"))
.Range("B5") = "ATLM Ref#: " & rtrim(rsDetail.fields("Ref#"))
.Range("B7") = rsDetail.fields("PaidOn")

.RANGE("A3..D10").FONT.BOLD = TRUE


iD = 11
.Columns("A").ColumnWidth = 33
.Columns("B").ColumnWidth = 15

.Columns("C").ColumnWidth = 11 'RATE
.Columns("C").NumberFormat = "#,##0.00"

.Columns("D").ColumnWidth = 11 'INVOICE NUMBER

.Range("A9..E9").Interior.ColorIndex = 15
'=====================================
' I would like to add a .FreezePanes Here.... but so far an unable to.
' .Range("A11").Select
' .FreezePanes = True
'=====================================

.Range("A10") = "Date"
.Range("B10") = "Refinery"
.Range("C10") = "Rate"
.Range("D10") = "Cust Inv#"

 

[NickHK]

Bob,
Yes, because .FreezePanes is a member of the Window object, not the
Worksheet object, as you are in a With block.
So you need the fully qualified hierachy :
xlApp.ActiveWindow.FreezePanes=True
or
xlApp.Windows(1).FreezePanes=True

NickHk

 

[Bob McClellan]

Very Cool!
It worked Great.
Thanks Nick...
I was banging my head against the wall on that one.

Thanks again,
bob.

 

[Bob McClellan]

Nick,
I thought of this after sending the thanks reply.....

I have no problem setting the number format
..Columns("C").NumberFormat = "#,##0.00"

....How do I set the date format?

 

[NickHK]

Bob,
Record a macro in Excel of performing the required action.
It will give you basic code that you can tidy up.

Hint: Depending which format you want:
..Columns("D").NumberFormat="yyyy-mm-dd"

NickHK

 

[Bob McClellan]

Yep...
didn't even think to do that.
Thanks again for all the help.
Bob.