QTP export data to excel

Question ID: 104181

I am pulling data from a application and needing to save the data in to a Excel document. Currently I am loading the datatable one cell and row at a time. This is taking hours to run and I was wondering if anybody uses another method that might speed up this process.

Marked as spam
Posted by (Questions: 15, Answers: 10)
Asked on August 9, 2010 5:09 pm
Answers (2)
Private answer

I found on Microsoft site (http://support.microsoft.com/kb/306022) an example under the section ''Use Automation to transfer an array of data to a range on a worksheet''. I modified the example for QTP. Below is the code I use converted from the site to work in side QTP.

    Dim oExcel 
    Dim oBook 
    Dim oSheet
    'Start a new workbook in Excel.

    Set oExcel = CreateObject(''Excel.Application'')
    Set  oBook = oExcel.Workbooks.Add

    'Create an array with 3 columns and 100 rows.
    Dim DataArray(99, 3)
    Dim r 
    For r = 0 To 99
        DataArray(r, 0) = r
        DataArray(r, 1) = Rnd() * 1000
        DataArray(r, 2) = DataArray(r, 1) * 0.07
        DataArray(r, 3) = ''Green Cows in Auguest Test::::''

    'Add headers to the worksheet on row 1.
    Set oSheet = oBook.Worksheets(1)
    oSheet.Range(''A1'').Value = ''Order ID''
    oSheet.Range(''B1'').Value = ''Amount''
    oSheet.Range(''C1'').Value = ''Tax''

    'Transfer the array to the worksheet starting at cell A2.
    oSheet.Range(''A2'').Resize(100, 4).Value = DataArray

    'Save the Workbook and quit Excel.
    oBook.SaveAs(sSampleFolder & sSampleFile )
    Set oSheet = Nothing
    Set oBook = Nothing
    Set oExcel = Nothing
Marked as spam
Posted by (Questions: 2, Answers: 98)
Answered on August 9, 2010 5:42 pm
Private answer

we can give other path to export excel sheet

Marked as spam
Posted by (Questions: 0, Answers: 1)
Answered on March 20, 2012 9:22 am