Solved

VB code to FreezePane a row in an Excel spreadsheet

Posted on 2004-10-12
6
850 Views
Last Modified: 2012-07-20
I've tried several ways to FreezePane a row without success.  Here is my code:

Please advise me on the proper way.

Private Function ConvertExcel(sFileName As String) As String
    Dim doc As Excel.Workbook
    Dim sht As Excel.Worksheet
    Dim rng As Excel.Range
    Dim cols As Long
    Dim sNewFileName As String
   
   
    m_appExcel.Workbooks.OpenText m_sDirectory & sFileName
    Set doc = m_appExcel.Workbooks(1)
   
    Set sht = doc.Sheets.Item(1)
    cols = sht.Columns.Count
   
    Set rng = sht.Range(sht.Cells(1, 1), sht.Cells(1, cols))
    rng.Font.Bold = True
    rng.Font.Size = 8
    rng.WrapText = True
    rng.VerticalAlignment = xlCenter
    rng.HorizontalAlignment = xlCenter
     
    sht.Cells.Font.Size = 8
    sht.Rows.AutoFit
    sht.Columns.AutoFit
   
    'sht.Rows("2:2").Select
    'ActiveWindow.FreezePanes = True
           
    sNewFileName = Left$(sFileName, InStr(1, sFileName, ".", vbBinaryCompare) - 1) & ".xls"
    doc.SaveAs m_sDirectory & sNewFileName, xlExcel9795
   
    doc.Close False
    ConvertExcel = sNewFileName
End Function
0
Comment
Question by:myfleetman
6 Comments
 
LVL 6

Expert Comment

by:Plamodo
ID: 12290577
Wouldn't it work without the comments on these two lines? :

sht.Rows("2:2").Select
ActiveWindow.FreezePanes = True
0
 

Author Comment

by:myfleetman
ID: 12293040
Tried that, but didn't work.
0
 
LVL 1

Accepted Solution

by:
CobolDotNet earned 125 total points
ID: 12294107
I tried your posted code with the two lines uncommented and it seemed to work on the first run, but failed on the second time that function was called.  I assume that this is what you mean by not working.

Personally, I find that using the ActiveWindow object is chancy at best.  Try putting in
     doc.Windows.Item(1).FreezePanes = True
for the line
    'ActiveWindow.FreezePanes = True

(obviously uncomment the row selection immediately before so that the row that you want is selected.)

This worked for me for repeated calls to the function on a file.

Hope that helps.
0
Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

 

Author Comment

by:myfleetman
ID: 12297442
OK...will try that.  Will get back when done.
0
 

Author Comment

by:myfleetman
ID: 12297497
It works!   Thanks CobolDotNet!  The ponts are yours.
0
 

Expert Comment

by:WestBillWest
ID: 38207323
I don't know how many points you will recieve but this helped me today..  Thanks again!
0

Featured Post

The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Need VBS version of Excel date conversion 4 70
Recommendation vb6 to vb.net or others 14 148
MsgBox 4 59
fso.FolderExists("\\server\HiddenFolder$") 4 65
Introduction While answering a recent question about filtering a custom class collection, I realized that this could be accomplished with very little code by using the ScriptControl (SC) library.  This article will introduce you to the SC library a…
Most everyone who has done any programming in VB6 knows that you can do something in code like Debug.Print MyVar and that when the program runs from the IDE, the value of MyVar will be displayed in the Immediate Window. Less well known is Debug.Asse…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

770 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question