VBS连接EXCEL及其操作

操作及其操作VBS连接EXCEL及其(一) 使用动态创建的方法首先创建Excel 对象，使用ComObj:oExcel = CreateObject( "Excel.Application" )1) 显示当前窗口：oExcel.Visible = True2) 更改Excel 标题栏：oExcel.Caption = "应用程序调用Microsoft Excel"3) 添加新工作簿：oExcel.WorkBooks.Add4) 打开已存在的工作簿：oExcel.WorkBooks.Open( "C:\Excel\Demo.xls" )5) 设置第2个工作表为活动工作表：oExcel.WorkSheets(2).Activate或oExcel.WorksSheets( "Sheet2" ).Activate6) 给单元格赋值：oExcel.Cells(1,4).Value = "第一行第四列"7) 设置指定列的宽度（单位：字符个数），以第一列为例：oExcel.ActiveSheet.Columns(1).ColumnsWidth = 58) 设置指定行的高度（单位：磅）（1磅＝0.035厘米），以第二行为例：oExcel.ActiveSheet.Rows(2).RowHeight = 1/0.035 ' 1厘米9) 在第8行之前插入分页符：oExcel.WorkSheets(1).Rows(8).PageBreak = 110) 在第8列之前删除分页符：oExcel.ActiveSheet.Columns(4).PageBreak = 011) 指定边框线宽度：oExcel.ActiveSheet.Range( "B3:D4" ).Borders(2).Weight = 31-左2-右3-顶4-底5-斜( \ ) 6-斜( / )12) 清除第一行第四列单元格公式：oExcel.ActiveSheet.Cells(1,4).ClearContents13) 设置第一行字体属性：oExcel.ActiveSheet.Rows(1) = "隶书"oExcel.ActiveSheet.Rows(1).Font.Color = clBlueoExcel.ActiveSheet.Rows(1).Font.Bold = TrueoExcel.ActiveSheet.Rows(1).Font.UnderLine = True14) 进行页面设置：a.页眉：oExcel.ActiveSheet.PageSetup.CenterHeader = "报表演示"b.页脚：oExcel.ActiveSheet.PageSetup.CenterFooter = "第&P页"c.页眉到顶端边距2cm：oExcel.ActiveSheet.PageSetup.HeaderMargin = 2/0.035d.页脚到底端边距3cm：oExcel.ActiveSheet.PageSetup.HeaderMargin = 3/0.035e.顶边距2cm：oExcel.ActiveSheet.PageSetup.TopMargin = 2/0.035f.底边距2cm：oExcel.ActiveSheet.PageSetup.BottomMargin = 2/0.035 g.左边距2cm：oExcel.ActiveSheet.PageSetup.LeftMargin = 2/0.035h.右边距2cm：oExcel.ActiveSheet.PageSetup.RightMargin = 2/0.035i.页面水平居中：oExcel.ActiveSheet.PageSetup.CenterHorizontally = 2/0.035 j.页面垂直居中：oExcel.ActiveSheet.PageSetup.CenterVertically = 2/0.035 k.打印单元格网线：oExcel.ActiveSheet.PageSetup.PrintGridLines = True15) 拷贝操作：a.拷贝整个工作表：ed.Range.Copyb.拷贝指定区域：oExcel.ActiveSheet.Range( "A1:E2" ).Copy c.从A1位置开始粘贴：oExcel.ActiveSheet.Range.( "A1" ).PasteSpecial d.从文件尾部开始粘贴：oExcel.ActiveSheet.Range.PasteSpecial16) 插入一行或一列：a. oExcel.ActiveSheet.Rows(2).Insertb. oExcel.ActiveSheet.Columns(1).Insert17) 删除一行或一列：a. oExcel.ActiveSheet.Rows(2).Deleteb. oExcel.ActiveSheet.Columns(1).Delete18) 打印预览工作表：oExcel.ActiveSheet.PrintPreview19) 打印输出工作表：oExcel.ActiveSheet.PrintOut20) 工作表保存：if not oExcel.ActiveWorkBook.Saved then oExcel.ActiveSheet.PrintPreview21) 工作表另存为：oExcel.SaveAs( "C:\Excel\Demo1.xls" )22) 放弃存盘：oExcel.ActiveWorkBook.Saved = True23) 关闭工作簿：oExcel.WorkBooks.Close24) 退出Excel：oExcel.Quit(二) 使用VBS 控制Excle二维图1）选择当第一个工作薄第一个工作表set oSheet=oExcel.Workbooks(1).Worksheets(1)2）增加一个二维图achart=oSheet.chartobjects.add(100,100,200,200)3）选择二维图的形态achart.chart.charttype=44）给二维图赋值set series=achart.chart.seriescollectionrange="sheet1!r2c3:r3c9"series.add range,true5）加上二维图的标题achart.Chart.HasTitle=Trueachart.Chart.ChartTitle.Characters.Text=" Excle二维图"6）改变二维图的标题字体大小achart.Chart.ChartTitle.Font.size=187）给二维图加下标说明achart.Chart.Axes(xlCategory, xlPrimary).HasTitle = Trueachart.Chart.Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = "下标说明"8）给二维图加左标说明achart.Chart.Axes(xlValue, xlPrimary).HasTitle = Trueachart.Chart.Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "左标说明"9）给二维图加右标说明achart.Chart.Axes(xlValue, xlSecondary).HasTitle = Trueachart.Chart.Axes(xlValue, xlSecondary).AxisTitle.Characters.Text = "右标说明"10）改变二维图的显示区大小achart.Chart.PlotArea.Left = 5 achart.Chart.PlotArea.Width = 223 achart.Chart.PlotArea.Height = 108最近有不少人在问QTP操作Excel的问题，其实QTP安装目录中的CodePlusSample里面就有一个名为“UsingExcel.vbs”的文件，里面有很多操作Excel的函数：Dim ExcellApp 'As Excel.ApplicationDim excelSheet1 'As Excel.worksheetDim excelSheet2 'As Excel.worksheetSet ExcelApp = CreateExcel()'Create a workbook with two worksheetsret = RenameWorksheet(ExcelApp, "Book1", "Sheet1", "Example1 Sheet Name")ret = RenameWorksheet(ExcelApp, "Book1", "Sheet2", "Example2 Sheet Name")ret = RemoveWorksheet(ExcelApp, "Book1", "Sheet3")'SaveAs the work bookret = SaveWorkbook(ExcelApp, "Book1", "D:\Example1.xls")'Fill worksheetsSet excelSheet1 = GetSheet(ExcelApp, "Example1 Sheet Name")Set excelSheet2 = GetSheet(ExcelApp, "Example2 Sheet Name")For column = 1 to 10For row = 1 to 10SetCellValue excelSheet1, row, column, row + columnSetCellValue excelSheet2, row, column, row + columnNextNext'Compare the two worksheetsret = CompareSheets(excelSheet1, excelSheet2, 1, 10, 1, 10, False)If ret ThenMsgBox "The two worksheets are identical"End If'Change the values in one sheetSetCellValue excelSheet1, 1, 1, "Yellow"SetCellValue excelSheet2, 2, 2, "Hello"'Compare the worksheets againret = CompareSheets(excelSheet1, excelSheet2, 1, 10, 1, 10, True)If Not ret ThenMsgBox "The two worksheets are not identical"End If'save the workbook by index identifierSaveWorkbook ExcelApp, 1, ""'Close the Excel applicationCloseExcel ExcelApp' ****************************************** Function Library ***********************************************************Dim ExcelApp 'As Excel.ApplicationDim excelSheet 'As Excel.worksheetDim excelBook 'As Excel.workbookDim fso 'As Scripting.FileSystemObject' This function will return a new Excel Object with a default new WorkbookFunction CreateExcel() 'As Excel.ApplicationDim excelSheet 'As Excel.worksheetSet ExcelApp = CreateObject("Excel.Application") 'Create a new excel ObjectExcelApp.Workbooks.AddExcelApp.Visible = TrueSet CreateExcel = ExcelAppEnd Function'This function will close the given Excel Object'excelApp - an Excel application object to be closedSub CloseExcel(ExcelApp)Set excelSheet = ExcelApp.ActiveSheetSet excelBook = ExcelApp.ActiveWorkbookSet fso = CreateObject("Scripting.FileSystemObject")On Error Resume Nextfso.CreateFolder "C:\Temp"fso.DeleteFile "C:\Temp\ExcelExamples.xls"excelBook.SaveAs "C:\Temp\ExcelExamples.xls"ExcelApp.QuitSet ExcelApp = NothingSet fso = NothingErr = 0On Error GoTo 0End Sub'The SaveWorkbook method will save a workbook according to the workbookIdentifier'The method will overwrite the previously saved file under the given path'excelApp - a reference to the Excel Application'workbookIdentifier - The name or number of the requested workbook'path - the location to which the workbook should be saved'Return "OK" on success and "Bad Workbook Identifier" on failureFunction SaveWorkbook(ExcelApp, workbookIdentifier, path) 'As String Dim workbook 'As Excel.workbookOn Error Resume NextSet workbook = ExcelApp.Workbooks(workbookIdentifier)On Error GoTo 0If Not workbook Is Nothing ThenIf path = "" Or path = workbook.FullName Or path = Then workbook.SaveElseSet fso = CreateObject("Scripting.FileSystemObject")'if the path has no file extension then add the 'xls' extensionIf InStr(path, ".") = 0 Thenpath = path & ".xls"End IfOn Error Resume Nextfso.DeleteFile pathSet fso = NothingErr = 0On Error GoTo 0workbook.SaveAs pathEnd IfSaveWorkbook = "OK"ElseSaveWorkbook = "Bad Workbook Identifier"End IfEnd Function'The SetCellValue method sets the given 'value' in the cell which is identified by'its row column and parent Excel sheet'excelSheet - the excel sheet that is the parent of the requested cell'row - the cell's row in the excelSheet'column - the cell's column in the excelSheet'value - the value to be set in the cellSub SetCellValue(excelSheet, row, column, value)On Error Resume NextexcelSheet.Cells(row, column) = valueOn Error GoTo 0End Sub'The GetCellValue returns the cell's value according to its row column and sheet'excelSheet - the Excel Sheet in which the cell exists'row - the cell's row'column - the cell's column'return 0 if the cell could not be foundFunction GetCellValue(excelSheet, row, column)value = 0Err = 0On Error Resume NexttempValue = excelSheet.Cells(row, column)If Err = 0 Thenvalue = tempValueErr = 0End IfOn Error GoTo 0GetCellValue = valueEnd Function'The GetSheet method returns an Excel Sheet according to the sheetIdentifier'ExcelApp - the Excel application which is the parent of the requested sheet'sheetIdentifier - the name or the number of the requested Excel sheet'return Nothing on failureFunction GetSheet(ExcelApp, sheetIdentifier) 'As Excel.worksheetOn Error Resume NextSet GetSheet = ExcelApp.Worksheets.Item(sheetIdentifier)On Error GoTo 0End Function'The InsertNewWorksheet method inserts an new worksheet into the active workbook or'the workbook identified by the workbookIdentifier, the new worksheet will get a default'name if the sheetName parameter is empty, otherwise the sheet will have the sheetName'as a name.'Return - the new sheet as an Object'ExcelApp - the excel application object into which the new worksheet should be added'workbookIdentifier - an optional identifier of the worksheet into which the new worksheet should be added'sheetName - the optional name of the new worksheet.Function InsertNewWorksheet(ExcelApp, workbookIdentifier, sheetName) 'As Excel.worksheet Dim workbook 'As Excel.workbookDim worksheet 'As Excel.worksheet'In case that the workbookIdentifier is empty we will work on the active workbookIf workbookIdentifier = "" ThenSet workbook = ExcelApp.ActiveWorkbookElseOn Error Resume NextErr = 0Set workbook = ExcelApp.Workbooks(workbookIdentifier)If Err <> 0 ThenSet InsertNewWorksheet = NothingErr = 0Exit FunctionEnd IfOn Error GoTo 0End IfsheetCount = workbook.Sheets.Countworkbook.Sheets.Add , sheetCountSet worksheet = workbook.Sheets(sheetCount + 1)'In case that the sheetName is not empty set the new sheet's name to sheetNameIf sheetName <> "" Then = sheetNameEnd IfSet InsertNewWorksheet = worksheetEnd Function'The RenameWorksheet method renames a worksheet's name'ExcelApp - the excel application which is the worksheet's parent'workbookIdentifier - the worksheet's parent workbook identifier'worksheetIdentifier - the worksheet's identifier'sheetName - the new name for the worksheetFunction RenameWorksheet(ExcelApp, workbookIdentifier, worksheetIdentifier, sheetName) 'As StringDim workbook 'As Excel.workbookDim worksheet 'As Excel.worksheetOn Error Resume NextErr = 0Set workbook = ExcelApp.Workbooks(workbookIdentifier)If Err <> 0 ThenRenameWorksheet = "Bad Workbook Identifier"Err = 0Exit FunctionEnd IfSet worksheet = workbook.Sheets(worksheetIdentifier)If Err <> 0 ThenRenameWorksheet = "Bad Worksheet Identifier"Err = 0Exit FunctionEnd If = sheetNameRenameWorksheet = "OK"End Function'The RemoveWorksheet method removes a worksheet from a workbook'ExcelApp - the excel application which is the worksheet's parent'workbookIdentifier - the worksheet's parent workbook identifier'worksheetIdentifier - the worksheet's identifierFunction RemoveWorksheet(ExcelApp, workbookIdentifier, worksheetIdentifier) 'As String Dim workbook 'As Excel.workbookDim worksheet 'As Excel.worksheetOn Error Resume NextErr = 0Set workbook = ExcelApp.Workbooks(workbookIdentifier)If Err <> 0 ThenRemoveWorksheet = "Bad Workbook Identifier"Exit FunctionEnd IfSet worksheet = workbook.Sheets(worksheetIdentifier)If Err <> 0 ThenRemoveWorksheet = "Bad Worksheet Identifier"Exit FunctionEnd Ifworksheet.DeleteRemoveWorksheet = "OK"End Function'The CreateNewWorkbook method creates a new workbook in the excel application'ExcelApp - the Excel application to which an new Excel workbook will be addedFunction CreateNewWorkbook(ExcelApp)Set NewWorkbook = ExcelApp.Workbooks.Add()Set CreateNewWorkbook = NewWorkbookEnd Function'The OpenWorkbook method opens a previously saved Excel workbook and adds it to the Application'excelApp - the Excel Application the workbook will be added to'path - the path of the workbook that will be opened'return Nothing on failureFunction OpenWorkbook(ExcelApp, path)On Error Resume NextSet NewWorkbook = ExcelApp.Workbooks.Open(path)Set OpenWorkbook = NewWorkbookOn Error GoTo 0End Function'The ActivateWorkbook method sets one of the workbooks in the application as Active workbook 'ExcelApp - the workbook's parent excel Application'workbookIdentifier - the name or the number of the workbookSub ActivateWorkbook(ExcelApp, workbookIdentifier)On Error Resume NextExcelApp.Workbooks(workbookIdentifier).ActivateOn Error GoTo 0End Sub'The CloseWorkbook method closes an open workbook'ExcelApp - the parent Excel application of the workbook'workbookIdentifier - the name or the number of the workbookSub CloseWorkbook(ExcelApp, workbookIdentifier)On Error Resume NextExcelApp.Workbooks(workbookIdentifier).CloseOn Error GoTo 0End Sub'The CompareSheets method compares between two sheets.'if there is a difference between the two sheets then the value in the second sheet'will be changed to red and contain the string:'"Compare conflict - Value was 'Value2', Expected value is 'value2'"'sheet1, sheet2 - the excel sheets to be compared'startColumn - the column to start comparing in the two sheets'numberOfColumns - the number of columns to be compared'startRow - the row to start comparing in the two sheets'numberOfRows - the number of rows to be comparedFunction CompareSheets(sheet1, sheet2, startColumn, numberOfColumns, startRow, numberOfRows, trimed) 'As BooleanDim returnVal 'As BooleanreturnVal = True'In case that one of the sheets doesn't exists, don't continue the processIf sheet1 Is Nothing Or sheet2 Is Nothing ThenCompareSheets = FalseExit FunctionEnd If'loop through the table and fill values into the two worksheetsFor r = startRow to (startRow + (numberOfRows - 1))For c = startColumn to (startColumn + (numberOfColumns - 1))Value1 = sheet1.Cells(r, c)Value2 = sheet2.Cells(r, c)'if 'trimed' equels True then used would like to ignore blank spacesIf trimed ThenValue1 = Trim(Value1)Value2 = Trim(Value2)End If'in case that the values of a cell are not equel in the two worksheets'create an indicator that the values are not equel and set return value'to FalseIf Value1 <> Value2 ThenDim cell 'As Excel.Rangesheet2.Cells(r, c) = "Compare conflict - Value was '" & Value2 & "', Expected value is '" & Value1 & "'."Set cell = sheet2.Cells(r, c)cell.Font.Color = vbRedreturnVal = FalseEnd IfNextNextCompareSheets = returnValEnd Function本文来自CSDN博客，转载请标明出处：/Testing_is_believing/archive/2008/07/04/2612221.aspx用VBS操控EXCEL命令首先创建Excel 对象，使用ComObj:oExcel = CreateObject( "Excel.Application" )1) 显示当前窗口：oExcel.Visible = True2) 更改Excel 标题栏：oExcel.Caption = "应用程序调用Microsoft Excel"3) 添加新工作簿：oExcel.WorkBooks.Add4) 打开已存在的工作簿：oExcel.WorkBooks.Open( "C:\Excel\Demo.xls" )5) 设置第2个工作表为活动工作表：oExcel.WorkSheets(2).Activate或oExcel.WorksSheets( "Sheet2" ).Activate6) 给单元格赋值：oExcel.Cells(1,4).Value = "第一行第四列"7) 设置指定列的宽度（单位：字符个数），以第一列为例：oExcel.ActiveSheet.Columns(1).ColumnsWidth = 58) 设置指定行的高度（单位：磅）（1磅＝0.035厘米），以第二行为例：oExcel.ActiveSheet.Rows(2).RowHeight = 1/0.035 ' 1厘米9) 在第8行之前插入分页符：oExcel.WorkSheets(1).Rows(8).PageBreak = 110) 在第8列之前删除分页符：oExcel.ActiveSheet.Columns(4).PageBreak = 011) 指定边框线宽度：oExcel.ActiveSheet.Range( "B3:D4" ).Borders(2).Weight = 31-左2-右3-顶4-底5-斜( \ ) 6-斜( / )12) 清除第一行第四列单元格公式：oExcel.ActiveSheet.Cells(1,4).ClearContents13) 设置第一行字体属性：oExcel.ActiveSheet.Rows(1) = "隶书" oExcel.ActiveSheet.Rows(1).Font.Color = clBlue oExcel.ActiveSheet.Rows(1).Font.Bold = TrueoExcel.ActiveSheet.Rows(1).Font.UnderLine = True14) 进行页面设置：a.页眉：oExcel.ActiveSheet.PageSetup.CenterHeader = "报表演示"b.页脚：oExcel.ActiveSheet.PageSetup.CenterFooter = "第&P页"c.页眉到顶端边距2cm：oExcel.ActiveSheet.PageSetup.HeaderMargin = 2/0.035d.页脚到底端边距3cm：oExcel.ActiveSheet.PageSetup.HeaderMargin = 3/0.035e.顶边距2cm：oExcel.ActiveSheet.PageSetup.TopMargin = 2/0.035f.底边距2cm：oExcel.ActiveSheet.PageSetup.BottomMargin = 2/0.035 g.左边距2cm：oExcel.ActiveSheet.PageSetup.LeftMargin = 2/0.035h.右边距2cm：oExcel.ActiveSheet.PageSetup.RightMargin = 2/0.035i.页面水平居中：oExcel.ActiveSheet.PageSetup.CenterHorizontally = 2/0.035 j.页面垂直居中：oExcel.ActiveSheet.PageSetup.CenterVertically = 2/0.035 k.打印单元格网线：oExcel.ActiveSheet.PageSetup.PrintGridLines = True15) 拷贝操作：a.拷贝整个工作表：ed.Range.Copyb.拷贝指定区域：oExcel.ActiveSheet.Range( "A1:E2" ).Copyc.从A1位置开始粘贴：oExcel.ActiveSheet.Range.( "A1" ).PasteSpeciald.从文件尾部开始粘贴：oExcel.ActiveSheet.Range.PasteSpecial16) 插入一行或一列：a. oExcel.ActiveSheet.Rows(2).Insertb. oExcel.ActiveSheet.Columns(1).Insert17) 删除一行或一列：a. oExcel.ActiveSheet.Rows(2).Deleteb. oExcel.ActiveSheet.Columns(1).Delete18) 打印预览工作表：oExcel.ActiveSheet.PrintPreview19) 打印输出工作表：oExcel.ActiveSheet.PrintOut20) 工作表保存：if not oExcel.ActiveWorkBook.Saved then oExcel.ActiveSheet.PrintPreview21) 工作表另存为：oExcel.SaveAs( "C:\Excel\Demo1.xls" )22) 放弃存盘：oExcel.ActiveWorkBook.Saved = True23) 关闭工作簿：oExcel.WorkBooks.Close24) 退出Excel：oExcel.QuitSet oExcel = CreateObject("Excel.Application")with oExcel.Visible = True.Workbooks.Open "C:\temp\text.xls".DisplayAlerts = False.ActiveWorkbook.SaveAs "C:\temp\text.csv", 6, False end with。