Solved

VB code to FreezePane a row in an Excel spreadsheet

Posted on 2004-10-12
6
862 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
Independent Software Vendors: 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!

 

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: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

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

Introduction While answering a recent question (http://www.experts-exchange.com/Q_27402310.html) in the VB classic zone, I wrote some VB code in the (Office) VBA environment, rather than fire up my older PC.  I didn't post completely correct code o…
If you have ever used Microsoft Word then you know that it has a good spell checker and it may have occurred to you that the ability to check spelling might be a nice piece of functionality to add to certain applications of yours. Well the code that…
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
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…

680 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