myfleetman
asked on
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.OpenT ext 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
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.OpenT
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
ASKER
Tried that, but didn't work.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
OK...will try that. Will get back when done.
ASKER
It works! Thanks CobolDotNet! The ponts are yours.
I don't know how many points you will recieve but this helped me today.. Thanks again!
sht.Rows("2:2").Select
ActiveWindow.FreezePanes = True