Excel Formatting using QTP

Hey folks,

Here, I try to write code for excel formatting. Code is fetching excel files from the folder and do formatting of Font, Size etc. It count the number of excel files from the folder and do formatting on each file and save it. This type of example helps when user wants to do same kind of formatting on each file in folder.

Set xlApp = CreateObject("Excel.Application")
Set fso = CreateObject("Scripting.FileSystemObject")
 PathofFolder="D:\Trupti\Copy of Testcases"
 Set oFolder = fso.GetFolder(PathofFolder) ' to get the folder
 Set oFiles = oFolder.Files
 NumberOfFiles = oFiles.Count ' to get the count of files
 msgbox "no of files" &NumberOfFiles
 For Each oFile In oFiles
 On Error Resume Next
 If InStr(1, lcase(oFile.path),".xls") > 0 then ' to check the type of file
            Set xlbook = xlApp.Workbooks.Open(oFile.path)
            set xlSheet=xlbook.WorkSheets.item(1)
            xlSheet.Activate
            xlApp.Visible=True
            xlSheet.Cells.Select ' to select all the active cells
            'xlApp.Selection.ClearFormats
            With xlApp.Selection
                    .Font.Name="Calibri"
                    .Font.Size=11
                    .HorizontalAlignment = -4131 '-4131 letf align, -4152 right align, -4130 justify, -4108 center
                     .VerticalAlignment = -4160 ' -4160 top align, -4107 bottom align, -4108  center, -4130 justify
                     .WrapText = True
                    ' .Borders(1).linestyle=1
            End With
            xlSheet.Rows("1:1").Font.Size=14            'select the first row and change the font size of it
            xlbook.SaveAs oFile.path, 51
 end If
 Err.clear
 Next
xlApp.Quit
Set xlApp = Nothing
Set xlbook = Nothing
set xlSheet = Nothing    

Thanks,
Trupti