Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 913
  • Last Modified:

VB code to FreezePane a row in an Excel spreadsheet

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
myfleetman
Asked:
myfleetman
1 Solution
 
PlamodoCommented:
Wouldn't it work without the comments on these two lines? :

sht.Rows("2:2").Select
ActiveWindow.FreezePanes = True
0
 
myfleetmanAuthor Commented:
Tried that, but didn't work.
0
 
CobolDotNetCommented:
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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
myfleetmanAuthor Commented:
OK...will try that.  Will get back when done.
0
 
myfleetmanAuthor Commented:
It works!   Thanks CobolDotNet!  The ponts are yours.
0
 
WestBillWestCommented:
I don't know how many points you will recieve but this helped me today..  Thanks again!
0

Featured Post

Vote for the Most Valuable Expert

It’s time to recognize experts that go above and beyond with helpful solutions and engagement on site. Choose from the top experts in the Hall of Fame or on the right rail of your favorite topic page. Look for the blue “Nominate” button on their profile to vote.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now