how to freeze top row in excel using vbscript

Question

I use this script to open csv file and save them in another directory as xls file.

I also want to add one more feature - I want to free the top row so that when I scroll down it remain constant. How can I do that?

Dim app, fso, file, fName, wb, dir 

dir = "D:\TA\"

dirsave = "D:\TA\XLS"

Set app = CreateObject("Excel.Application")
Set fso = CreateObject("Scripting.FileSystemObject")

For Each file In fso.GetFolder(dir).Files
    If LCase(fso.GetExtensionName(file)) = "csv" Then  
    fName = fso.GetBaseName(file)

    Set wb = app.Workbooks.Open(file) 
    app.Application.Visible = True
    app.Application.DisplayAlerts = False
    app.ActiveWorkbook.SaveAs dirsave & "\" & fName & ".xls", 56
    app.ActiveWorkbook.Close 
    app.Application.DisplayAlerts = True 
    app.Application.Quit 

    End if
Next

score 1 · Answer 1 · edited May 23 '17 at 11:50

1

Try this answer

Rows("1:1").Select
ActiveWindow.FreezePanes = True

edited May 23 '17 at 11:50

Community

1
1

answered Apr 09 '16 at 02:10

Stephen Lloyd

733
8
21

score 1 · Answer 2 · edited May 23 '17 at 10:28

Your current code can't be working for more than 1 file.

App.application is same as app in this case; just use app.

Visible and displayalerts can be done once, before the for loop.

Instead of app.activeworkbook, use the variable wb.

Just before save as, use the two lines provided by Stephen; prefix the first line with wb.worksheets(1) and second with "app.".

Consider saving as xlsx instead of xls.

Resetting displayalerts and app.quit should be after for loop.

how to freeze top row in excel using vbscript

2 Answers2