Solved

VB code to FreezePane a row in an Excel spreadsheet

Posted on 2004-10-12
6
884 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 

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

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

This article describes some techniques which will make your VBA or Visual Basic Classic code easier to understand and maintain, whether by you, your replacement, or another Experts-Exchange expert.
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
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…
Suggested Courses
Course of the Month8 days, 10 hours left to enroll

617 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