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.