QTP - 23 (Working with MS Excel) QTP与Excel交互

23 Working with MS Excel

Concepts

Excel provides methods for automation by COM interface.

Application – The Main object which references the Excelapplication itself.

Workbooks – The object references a collection of currentlyopened Excel Workbooks

Workbook – The object references a single open Excelworkbook

The same way to Worksheets & Worksheet.

The Excel Automation Object Model:

Application       Workbooks (Workbook)             Names (Name)             Worksheets (Worksheet)                  Names (Name)                  Range

 

项目经验：在QTP安装目录的CodeSamplesPlus的子目录下，有UsingExcel.vbs的文件，提供QTP与Excel交互的功能代码示例。

0.Example -- Add or open an Excel File

 

'Add or open an Excel File

Dim xlApp, sSourceFile, fso

sSourceFile = "C:\frank.xls"

Set fso =CreateObject("Scripting.FileSystemObject")

bFileExsit = fso.FileExists(sSourceFile)

print "File Exsit : " & sSourceFile &"=="&  bFileExsit

 

Set xlApp = CreateObject("Excel.Application")

xlApp.Visible = True

 

If bFileExsit Then

               SetxlWorkBook = xlApp.Workbooks.Open(sSourceFile)

Else

               SetxlWorkBook = xlApp.Workbooks.Add

End If

 

'Add new worksheet like: Set xlWorkSheet = xlWorkBook.Worksheets.Add

‘Or Open a worksheet

Set xlWorkSheet = xlWorkBook.Worksheets("Sheet1")

xlWorkSheet.Cells(1,2) = "This is new addedcontent"

 

If bFileExsit Then

               xlWorkBook.Save

Else

               xlWorkBook.SaveAssSourceFile

End If

 

xlWorkBook.Close

xlApp.Quit                          'Terminalthe Excel instance

Set xlWorkBook = Nothing

Set xlWorkSheet = Nothing

Set xlApp = Nothing                        'Destroythe Excel instance

'============================================================================

1.    Example – Access all non-blank row values in aspecific column

Thereare 2 methods to do it.

Method 1 : loop each cell in a selected column and stop when a blank valueis encountered.

While xlWorkSheet.Cells(i,1) <>""

               printxlWorkSheet.Cells(i,1)

               i= i + 1

Wend

Method 2: to get the count of all non-blank values in the selected column

 

'============================================================================

2.    'Search for a text string  and update another cell's value

Set objFind =xlWorkSheet.Range("A:A").Find("Name8")

If objFind is Nothing Then

               print"Name8 not found"

Else

               iRowNum= objFind.Row

               xlWorkSheet.Cells(iRowNum,2)= "123456"

End If

 

'==============================================================================

3.    'Allow user to select a file to be opened

sExcelFileName = xlApp.GetOpenFilename("ExcelFiles","*.xls", , "Select the Excel File")

If  sExcelFileNameThen

               print"File Selected"

Else

               print"No File Selected"

End If

 

'===============================================================================

4.    'Converting VBA to VBS

Notice: The constant in Excel(VBS), e,g, xlSolid, notavaiable in QTP. You should search the value and define yourself

'============================================================================

5.    Access an Excel spreadsheet embedded in IE

Use following statement to access the document object:

Set objDoc = Browser(“CreationTime : = 0”).object.document

But the above statement cause “General run error” exception”

Use the following function to access IE’s COM interfacethrough its window’s handle:

 

'================================================================================

'Access an Excel spreadsheet embedded in IE

hwnd =Browser("CreationTime:=0").GetROProperty("hwnd")

Set ieBrowser = GetIEObjectFromhwnd(hwnd)

Set objDocument = ieBrowser.document  'This objDocument can be any embeddeddocument, like word, PPT or more

Set objWWorkbook = objDocument

 

Function GetIEObjectFromhwnd(ByVal hwnd)

   Dim objShell,objShellWindows

               SetobjShell = CreateObject("Shell.Application")    'Create shelll object

               SetobjShellWindows = objShell.Windows               'Getthe collection of all open explorer windows

               If (NotobjShellWindows Is Nothing) Then

                              DimobjEnumItems

                                             ForEach objEnumItems In objShellWindows

                                                            IfobjEnumItems.hwnd = hwnd Then

                                                                           SetGetIEObjectFromhwnd = objEnumItems           'returnthe object whose hwnd meet

                                                                           ExitFor

                                                            Else

                                                                           SetGetIEObjectFromhwnd = Nothing                       'Noobject found

                                                            EndIf

                                             Next

               End If

 

               SetobjShellWindows = Nothing

               SetobjShell = Nothing

End Function

 

'============================================================================

6.    Performance Enhancement:

Each QTP call to an Excel object has its cost, like

For  i = 0 To 1000

               xlworksheet.cell(I,1)= i

Next

QTP make 1000 calls, so reduce call can improve runtimeperformance.

Dim iArray

ReDim iArray (999,6)

For i = 1 To 1000

               For j= 1To 7

                              iArrary(i-1,j-1) = I*j

               Next

Next

xlworksheet.Range(“A1:”& GetCoumnName(j-1)&(i-1))=iArrary

'============================================================================