Excelのレポートを作成するVbscriptの共通操作のまとめ

On Error Resume Next
 Dim FileName, SheetName, Text, ExcelApp, ExcelBook, ExcelSheet
 FileName = "D:/Book1.xls"
 SheetName = "New Sheet"
 Text = "Hello QTP ! Hello, QuickTestProfessional ! "
Set ExcelApp = CreateObject("Excel.Application")
 Set ExcelBook= ExcelApp.Workbooks.Open(FileName)
 Set ExcelSheet = ExcelBook.Sheets.Add 'Insert worksheet
 Set ExcelSheet = ExcelBook.Sheets.Item(SheetName) 'Get the specified worksheet
' *************** Operations on data tables ***************
 For i=1 To ExcelBook.Sheets.
 If ExcelBook.Sheets(i).Name=SheetName Then
 ExcelApp.DisplayAlerts=False
 ExcelBook.Sheets(i).Delete 'Delete the worksheet
 DisplayAlerts=True
 Exit For
 End If
 Next
 ExcelSheet.Name = SheetName 'Rename the worksheet
' *************** Action on text ***************
 ExcelSheet.Cells(1,2) = Text
 ExcelSheet.Range("B2","B20").Value = Text
 ExcelSheet.Cells(1,2).Font.Name = "Verdana" 'Set the font
 Cells(1,2).Font.Size = 25 'Set the font size
 Cells(1,2).Font.Color = RGB(0, 0, 255) 'set the font color
 Cells(2,2).Font.Bold = True 'Bold the text
 Cells(3,2).Font.Italic = True 'Skew text
 Cells(4,2).Font.Underline = True 'Underline text
 ExcelSheet.Cells(5,2).Font.Strikethrough = True 'Text with strikethrough
 ExcelSheet.Cells(6,2).Characters(2, 2).Font.Superscript = True 'Set text superscript
 ExcelSheet.Cells(7,2).Characters(2, 2).Font.Subscript = True 'Set the text subscript
' *************** Operations on cells ***************
 ExcelSheet.Columns("B").ColumnWidth = 40 'Set the column width
 ExcelSheet.Columns("B").AutoFit 'automatically adjust the width of the column
 ExcelSheet.Range("B11").RowHeight = 40 'set the row height
 Rows(11).Rows.AutoFit 'Automatically adjust the row height
 ExcelSheet.Range("B8","D8").Merge 'Merge cells, horizontal direction
 ExcelSheet.Range("B18","B19").Merge 'merge cells, vertical direction
 ExcelSheet.Range("B8","D8").Borders.Color = RGB(0,255,0) 'Set the cell border color
 ExcelSheet.Range("B12").Interior.Color = RGB(255,0,0) 'set the cell background color
 ExcelSheet.Cells(9,2).WrapText = True 'automatic line feed
 Cells(10,2).HorizontalAlignment = 3 'set the horizontal alignment, 1 regular, 2 left, 3 center, 4 right
 ' 5 padding, 6 alignment at both ends, 7 centered across columns, 8 scattered alignment
 VerticalAlignment = 1 'set vertical alignment, 1 up, 2 center, 3 down
 ' 4 align both ends, 5 disperse alignment
 ExcelSheet.Range("B14").Borders(1).LineStyle = 1 'set the left border style
 ExcelSheet.Range("B14").Borders(2).LineStyle=2 'Set the right box style
 ExcelSheet.Range("B14").Borders(3).LineStyle=3 'Set the upper border style
 ExcelSheet.Range("B14").Borders(4).LineStyle=4 'Set the lower border style
 ExcelSheet.Range("B15").ClearContents 'Clear the contents of the cell
 ExcelSheet.Range("B16").Formula="=1+10" 'set the cell formula
 ExcelSheet.Range("B17").AddComment("Hello" & vbLf & "QTP") 'Insert comment
 ExcelSheet.Range("B17").Comment.Visible=True 'Show comment
 ClearComments 'Clear the comments, with the same effect as deleting the comments
 'ExcelSheet.Range("B17").Comment.Delete 'Delete the comment, the same effect as clearing the comment
 ExcelSheet.SaveAs("D:\Book2.xls") 'Save as
ExcelBook.Save
 ExcelBook.Close
 ExcelApp.Quit
 Set ExcelBook = Nothing
 Set ExcelApp = Nothing
 SystemUtil.CloseProcessByName "Excel.exe" 'If there is still an Excel.exe process, you can use this sentence to close the process
 If Err.number>0 Then
 MsgBox Err.Description
 End If
 On Error GoTo 0

ExcelApp.DisplayAlerts = False 'Turn off compatibility checking
ExcelBook = ExcelApp.Workbooks.Add 'Create a new Excel
ExcelSheet = ExcelBook.ActiveSheet 'Activate the first sheet
ExcelSheet.Columns("A:E").AutoFit() 'Set columns A to E to automatically adjust column widths
ExcelBook.SaveAs("D:\Book2.xls",FileFormat:=Excel.XLFileFormat.xlAddIn) 'Save file as