Thread Rating:
  • 4 Vote(s) - 2.75 Average
  • 1
  • 2
  • 3
  • 4
  • 5
Whether xlsx extension works while importing data sheet?
#11
Solved: 10 Years, 7 Months, 3 Weeks ago
hi

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

thanks
vijaya
Reply
#12
Solved: 10 Years, 7 Months, 3 Weeks ago
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 ?
Reply
#13
Solved: 10 Years, 7 Months, 3 Weeks ago
datatable.ImportSheet"D:\Akbar1.xls",1,1

Try this it is working.....cool
Reply
#14
Solved: 10 Years, 7 Months, 3 Weeks ago
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
Reply
#15
Solved: 10 Years, 7 Months, 3 Weeks ago
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.

Reply
#16
Solved: 10 Years, 7 Months, 3 Weeks ago
(04-14-2011, 01:20 PM)Saket Wrote:
(02-08-2011, 09:25 AM)RGummadidala Wrote: 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.

Reply
#17
Solved: 10 Years, 7 Months, 3 Weeks ago
Use Excel 97-2003 Workbook type while saving the excel sheet.
Reply
#18
Solved: 10 Years, 7 Months, 3 Weeks ago
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 Cool
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
Reply
#19
Solved: 10 Years, 7 Months, 3 Weeks ago
I am playing with excel and qtp regularly. In QTP 11 xlsx is not supporting.
Reply
#20
Solved: 10 Years, 7 Months, 3 Weeks ago
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.
Reply


Possibly Related Threads…
Thread Author Replies Views Last Post
  Reading data from excel sheet serenediva 1 8,582 03-03-2017, 10:07 AM
Last Post: vinod123
  Chrome Extension UFT Agent for Chrome Browser testing with UFT santhosh kumar 2 12,010 04-20-2016, 01:20 AM
Last Post: santhosh kumar
Question QTP not performing iterations based on local data sheet Breaker 18 55,133 01-25-2016, 04:32 PM
Last Post: arunshuklainbox
  How QTP record/play works on google chrome shobi 6 7,073 01-11-2016, 09:53 AM
Last Post: vinod123
  Importing data sheet Santu 2 3,169 08-20-2015, 11:37 PM
Last Post: ADITI1992

Forum Jump:


Users browsing this thread: 1 Guest(s)