Whether xlsx extension works while importing data sheet?

[vijaya.t]

hi

QTP 10 doesnt support .xlsx.so,if you try to import .xlsx file,it throws an error,showing invalid file.

thanks
vijaya

[RGummadidala]

Hi All,

I'm also struggling to import an excel file to Datatable. I have an excel by name Sample.xls and my script is as below

Code:

Dim qt
Set qt = createobject("QuickTest.Application")
qt.Launch
qt.Visible=True
DataTable.ImportSheet  "C:/Users/Desktop/Sample.xls",1,"Global"

I wrote this in expert view of QTP and ran it. When I run it, I'm seeing the Global sheet of DataTable getting populated (I see the values copied to cell quickly), but at the end of the run, there are no values in the DataTable. I also see 5 iterations of the execution, in the HP results view, where as I ran it only once.

Can somebody throw some light on this ?

[akbarsharifmd]

datatable.ImportSheet"D:\Akbar1.xls",1,1

Try this it is working.....cool

[mukesh_shenoy18]

Hi all,

Code:

Set oexcel = CreateObject("Excel.Application")
Set obook = oexcel.Workbooks.Open("C:\QTP_Assignment.xlsx","Append")
Set objDriverSheet =  obook.Worksheets("Mukesh")


' Writing values into excel sheet

objDriverSheet.Cells(2,5) = "111111111"
objDriverSheet.Cells(3,5) = "22222222"
obook.Save
obook.Close

///////////////////////////////////////////////
when i run this script i get an run error like this

////////////////////////////////////////////////////////
The test run cannot continue due to an unrecoverable error.

Microsoft Office Excel cannot access the file 'C:\76149810'. There are several possible reasons:

• The file name or path does not exist.
• The file is being used by another program.
• The workbook you are trying to save has the same name as a currently open workbook.

////////////////////////////////////////////////////////////////
The data 111111111 and 22222222 was written into the excel sheet.
I think i have to open the file in the append mode as it tries to write a new file with the same name.Can anyone help me how to open the xlsx
file in the append mode


Thanks

[Saket]

Hi,

first of all there is no need to use QTP AOM inside QTP in the expert view. your last statement only should work.Now answer to your question : "at the end of the run, there are no values in the DataTable" - The import you are doing is runtime, so the data which you see will not be visible after the run session. you can see the imported data in the runtime data table. for your second query - as the data is being imported into Global sheet, the test is being iterated to the number of rows in the global sheet. If your test is iterated 5 times then there must be data in 5 rows in the excel you are importing. try importing your data in a new sheet.
hope this solves your query.

---

@mukesh_shenoy18 - Please open a new thread for your query.

[AnujN]

Hi All,

I'm also struggling to import an excel file to Datatable. I have an excel by name Sample.xls and my script is as below

Dim qt
Set qt = createobject("QuickTest.Application")
qt.Launch
qt.Visible=True
DataTable.ImportSheet "C:/Users/Desktop/Sample.xls",1,"Global"

I wrote this in expert view of QTP and ran it. When I run it, I'm seeing the Global sheet of DataTable getting populated (I see the values copied to cell quickly), but at the end of the run, there are no values in the DataTable. I also see 5 iterations of the execution, in the HP results view, where as I ran it only once.

Can somebody throw some light on this ?

Hi,

first of all there is no need to use QTP AOM inside QTP in the expert view. your last statement only should work.Now answer to your question : "at the end of the run, there are no values in the DataTable" - The import you are doing is runtime, so the data which you see will not be visible after the run session. you can see the imported data in the runtime data table. for your second query - as the data is being imported into Global sheet, the test is being iterated to the number of rows in the global sheet. If your test is iterated 5 times then there must be data in 5 rows in the excel you are importing. try importing your data in a new sheet.
hope this solves your query.

---

@mukesh_shenoy18 - Please open a new thread for your query.

[swapnanil.jgec@gmail.com]

Use Excel 97-2003 Workbook type while saving the excel sheet.

[cflow]

I required this for my testing today, so here is a fully functional XLSX importer

Disclaimer: This does not format or cleanup the Excel sheets themselves, just renames them for import so they are CamelCase. If you want your columns in the datasheets to be CamelCase with no special characters you will need to do some further extension.

Code:

'Usage for XLS or XLSX:
Dim filePath
filePath = "C:\File\Path\Goes\Here\FileName.xlsx"
importAnyXL filePath

Function importAnyXL(filePath)
   Dim xl, xlFile 'Objects
   Dim FileFormatNum, iSheetCount, a, i 'Integers
   Dim newFilePath, sheetName, cleanSheetName 'Strings
   Dim fileDelete 'Boolean
   Dim sheetList()  'Array
        Set xl = CreateObject("Excel.Application")
        xl.DisplayAlerts = False
        Set xlFile = xl.Workbooks.Open(filePath)
        iSheetCount = xlFile.Sheets.Count
        For a = 0 To (iSheetCount - 1)
            ReDim Preserve sheetList(a)
            sheetList(a) = xlFile.Sheets(a + 1).Name
        Next
        If UCase(Right(Trim(filePath), 4)) = "XLSX" Or UCase(Right(Trim(filePath), 4)) = "XLSM" Then 'Dont forget to cater for macro enabled files ;)
            FileFormatNum = -4143
            filePath = Left(filePath, Len(filePath) - 5) & "_Temp.xls"
            xlFile.SaveAs filePath, FileFormatNum
            fileDelete = True
        End If
        xlFile.Close False
        xl.DisplayAlerts = True
        xl.Quit
        Set xl = Nothing
        For Each sheetName In sheetList
            cleanSheetName = camelCase(sheetName)
            DataTable.AddSheet camelCase(sheetName)
            DataTable.ImportSheet filePath, sheetName, cleanSheetName
        Next
        'Clean-up after yourself :)
        If fileDelete Then
            Dim FSO
            Set FSO = CreateObject("Scripting.FileSystemObject")
            If FSO.FileExists(filePath) Then
               FSO.DeleteFile filePath
            End If
            Set FSO = Nothing
        End If
End Function

And for a Bonus

Code:

Function camelCase(strIn)
   Dim strOut, boolUp, i, c, tc
    strOut = ""
    boolUp = True
        For i = 1 To Len(strIn)
            c = Mid(strIn, i, 1)
            If c = " " Or c = "'" Or c = "-" Then
                strOut = strOut & c
                boolUp = True
            Else
                If boolUp Then
                    tc = UCase(c)
                Else
                    tc = LCase(c)
                End If
                strOut = strOut & tc
                boolUp = False
            End If
        Next
    camelCase = Replace(Replace(strOut, " ", ""), ".*", "")
End Function

[QTPScript]

I am playing with excel and qtp regularly. In QTP 11 xlsx is not supporting.

[agarwl.anurag]

Hi,

All the QTP version only support one extension i.e .xls format when you are importing any excel file at run time in datatable.