vbs中对excel的常用操作

使用QTP自动化测试中,用到对excel的读写操作,这里把一些常用对excel操作的方法进行了归纳,总结。(对excel格式设置的常用操作这里没有进行总结。)

Function DataToExcel(byval filepath,byval filename)

    Dim objExcel,exlSheet,exlBook,fso,exApp,rows,cols

    '关闭所有excel
'SystemUtil.CloseProcessByName("excel.exe") Set objExcel = createobject("Excel.Application")
Set fso = createobject("scripting.filesystemobject")
Set exApp = getObject(,"excel.application") '关闭所有打开的excel文件
If TypeName(exApp) = "Application" Then
For each objBook in exApp.workbooks
msgbox objBook.FullName
objBook.close Next
End If Set exApp = nothing If fso.FileExists(filepath & filename) Then
Set exlBook =objExcel.Workbooks.Open(filepath & filename)
else
Set exlBook = objExcel.Workbooks.Add
End If objExcel.DisplayAlerts = false
'set exlBook = objExcel.Workbooks.Add '创建excel
'获取指定工作表
Set exlSheet = exlBook.Worksheets("sheet1")
'或Set exlSheet = exlBook.worksheets(1).activate exlSheet.cells(,).value = "aa" '获取excel可用的范围
rows = exlSheet.usedrange.rows.count
cols = exlSheet.usedrange.columns.count '设置单元格的列度
exlSheet.Columns("A").ColumnWidth =
'设置单元格的行高
exlSheet.Range("A1").RowHeight = exlBook.SaveAs(filepath & filename) 'excel另存为
'objExcel.SaveWorkspace '保存excel文件
'exlBook.close '关闭sheet页面
objExcel.Quit Set exlSheet = nothing
Set exlBook = nothing
Set objExcel = nothing
Set fso = nothing End Function Call DataToExcel ("D:\Documents\Desktop\","hello.xls")

补充:
excel文件另存为的操作:

1.工作表对象的SaveAs方法

exlBook.SaveAs(filepath & filename)

2.通过WScript对象实现:

Set shell = CreateObject("WScript.shell")

shell.SendKeys "^S"

shell.SendKeys filepath

shell.SendKeys "{enter}"

shell.SendKeys "%Y"

上一篇:如何通过SerialPort读取和写入设备COM端口数据


下一篇:怎样编写求均及格人数EXCEL函数公式