I had been using Sheet.split to emulate Freezing a row(s) as there is not a method to do so within API ExcelSheet, but have found a way to actually Freeze any row.
Here is the vbs code snippet I found which will do this and more if wanted This way you can have just a Frozen row(s) with or without having a split sheet.. It creates any type of Excel file wanted from a CSV file so is not appropriate specifically for what is wanted but could be modified to be. Quite a bit has been commented out for what I needed.
Code:
'Constants Const xlOpenXMLWorkbook = 51 '(without macro's in 2007-2016, xlsx) Const xlOpenXMLWorkbookMacroEnabled = 52 '(with or without macro's in 2007-2016, xlsm) Const xlExcel12 = 50 '(Excel Binary Workbook in 2007-2016 with or without macro's, xlsb) Const xlExcel8 =56 '(97-2003 format in Excel 2007-2016, xls) ' Extensions for old and new files 'changing between xls and xlsx requires changing the above Constant as well strExcel = "xlsx" strCSV = "csv" strXLS = "xls" ' Set up filesystem object for usage Set objFSO = CreateObject("Scripting.FileSystemObject") strFolder = "C:\__Some_Folder" strPath = "C:\__Some_Folder\Accounts Receivable.csv" ' Access the folder to process Set objFolder = objFSO.GetFolder(strFolder) ' Load Excel (hidden) for conversions Set objExcel = CreateObject("Excel.Application") objExcel.Visible = False objExcel.DisplayAlerts = False ' Process all files 'For Each objFile In objFolder.Files ' Get full path to file ' strPath = objFile.Path ' Only convert CSV files ' If LCase(objFSO.GetExtensionName(strPath)) = LCase(strCSV) Or LCase(objFSO.GetExtensionName(strPath)) = LCase(strXLS) Then ' Display to console each file being converted 'next will display a messagebox ' Wscript.Echo "Converting """ & strPath & """" ' Load CSV into Excel and save as native Excel file Set objWorkbook = objExcel.Workbooks.Open(strPath, False, True) strNewPath = objFSO.GetParentFolderName(strPath) & "\" & objFSO.GetBaseName(strPath) & "." & strExcel 'strXLS ....uses xlExcel8 constant below 'strExcel ....uses xlOpenXMLWorkbook constant below objExcel.Rows(1).Font.Bold = TRUE objExcel.Range("k2").Select 'freezes top row 1 -- takes all columns left of K and all rows above 2 objExcel.ActiveWindow.FreezePanes = True objWorkbook.SaveAs strNewPath, xlOpenXMLWorkbook 'xlExcel8 ...for XLS files 'xlOpenXMLWorkbook ....for XLSX files objWorkbook.Close False Set objWorkbook = Nothing ' End If 'Next 'Wrap up objExcel.Quit Set objExcel = Nothing Set objFSO = Nothing
Leave a comment: