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(1,1).value = "aa"
    
    '获取excel可用的范围
    rows = exlSheet.usedrange.rows.count
    cols = exlSheet.usedrange.columns.count

    '设置单元格的列度
    exlSheet.Columns("A").ColumnWidth = 20
    '设置单元格的行高
    exlSheet.Range("A1").RowHeight = 15

    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:DocumentsDesktop","hello.xls")

View Code

补充：
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"