有时候,需要把
qtp的global sheet数据纪录到外面,比如excel。为了保证以前数据不被覆盖,需要先用qtp的datatable相关函数先导入,然后设定当前行为最后一行,再导出。
如此一来数据量比较大时势必影响速度,于是写这个函数,每次读取global table数据然后写入到指定的excel里。
其实就是用excel的COM接口。相关函数qtp示例里基本都包括。
Sub WriteGlobalExcel(strFileName)
Dim nGloColumnNum 'Get column and data from global sheet of QTP.
Set FSO = CreateObject("Scripting.FileSystemObject")
If Not oFSO.FileExists(strFileName) Then 'If file not exists.
Set XlsApp = CreateObject("Excel.Application")
oXlsApp.Workbooks.Add
oXlsApp.Visible = False
oXlsApp.Workbooks("Book1").Sheets("Sheet1").name = "output"
Set NewSheet = oXlsApp.WorkBooks("Book1").Sheets("output")
nGloColumnNum = DataTable.GlobalSheet.GetParameterCount
For i = 1 to nGloColumnNum
NewSheet.Cells(1, i).Font.Color = vbBlue
NewSheet.Cells(1, i) = DataTable.GlobalSheet.GetParameter(i).name
Next
DataTable.GlobalSheet.SetCurrentRow(1)
For i = 1 to nGloColumnNum
NewSheet.Cells(2, i) = DataTable(DataTable.GlobalSheet.GetParameter(i).name, dtGlobalSheet)
Next
oXlsApp.Workbooks("Book1").saveAs strFileName
oXlsApp.Quit
Set XlsApp = Nothing
Else 'If file exists.
Set XlsApp = CreateObject("Excel.Application")
oXlsApp.Workbooks.Open(strFileName)
oXlsApp.Visible = False
Set Sheet = oXlsApp.WorkBooks(1).Sheets("output")
nMaxRow = oSheet.UsedRange.Rows.Count
nGloColumnNum = DataTable.GlobalSheet.GetParameterCount 'get global data.
For i = 1 to nGloColumnNum
oSheet.Cells(nMaxRow+1, i) = DataTable(DataTable.GlobalSheet.GetParameter(i).name, dtGlobalSheet)
Next
oXlsApp.Workbooks(1).Save
oXlsApp.Quit
Set XlsApp = Nothing
End If
Set FSO = Nothing
End Sub