[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 927
  • 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
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
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

Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

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