SPONSORS:






User Tag List

Thanks Thanks:  0
Likes Likes:  0
Dislikes Dislikes:  0
Page 1 of 2 12 LastLast
Results 1 to 10 of 16
  1. #1
    Member
    Join Date
    May 2007
    Location
    Dublin, OH USA
    Posts
    467
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Total Downloaded
    0

    Import data into script from excel

    I am importing data from an external excel file into my QTP script. I have script code already written, but it is a bit kludgy and cumbersome, yet it does work.

    Can anyone here give me an example of how to make it "look better" or point me to a link? Thanks!

    Here is a sample of what I am using now:

    ================================================== =========

    'Pulling data from the ISBN.xls Excel spreadsheet.
    Set objExcel = CreateObject("Excel.Application")
    Set objWorkbook = objExcel.WorkBooks.Open("C:\ISBN.xls")
    Set objDriverSheet = objWorkbook.Worksheets("ISBN")

    'Importing data for the first ISBN line item
    Dim sValue
    row = 1
    sValue = objDriverSheet.Cells(row,"A").Value
    OracleFormWindow("Sales Order").OracleTabbedRegion("Line Items").OracleTextField("Ordered Item").Enter sValue

    'Importing data for the secondISBN line item
    wait 2
    OracleFormWindow("Sales Order").OracleTabbedRegion("Line Items").OracleTextField("Ordered Item_2").SetFocus
    Dim sValue1
    row = 2
    sValue1 = objDriverSheet.Cells(row,"A").Value
    OracleFormWindow("Sales Order").OracleTabbedRegion("Line Items").OracleTextField("Ordered Item_2").Enter sValue1

    'Importing data for the third ISBN line item
    wait 2
    OracleFormWindow("Sales Order").OracleTabbedRegion("Line Items").OracleTextField("Ordered Item_3").SetFocus
    Dim sValue2
    row = 3
    sValue2 = objDriverSheet.Cells(row,"A").Value
    OracleFormWindow("Sales Order").OracleTabbedRegion("Line Items").OracleTextField("Ordered Item_3").Enter sValue2

    'Importing data for the fourth ISBN line item
    wait 2
    OracleFormWindow("Sales Order").OracleTabbedRegion("Line Items").OracleTextField("Ordered Item_4").SetFocus
    Dim sValue3
    row = 4
    sValue3 = objDriverSheet.Cells(row,"A").Value
    OracleFormWindow("Sales Order").OracleTabbedRegion("Line Items").OracleTextField("Ordered Item_4").Enter sValue3

    'extra
    ================================================== =========

    As I had stated earlier, the above code does work, it just isn't the most efficient way in doing it, I'm sure.

    By the way in my excel file I have 26 rows and only 1 column, column "A".

    I didn't include the code for all 26 rows above.

    SBsteve

  2. #2
    Senior Member
    Join Date
    Mar 2006
    Location
    Bristol, UK
    Posts
    1,530
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Total Downloaded
    0

    Re: Import data into script from excel

    Why not import the excel sheet into QTP data table?

  3. #3
    Member
    Join Date
    May 2007
    Location
    Dublin, OH USA
    Posts
    467
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Total Downloaded
    0

    Re: Import data into script from excel

    Thanks Robbie. I did read that in this forum, but not 100% how to do that.

    Any help would be great.

    SBsteve

  4. #4
    Senior Member
    Join Date
    Mar 2006
    Location
    Bristol, UK
    Posts
    1,530
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Total Downloaded
    0

    Re: Import data into script from excel

    Couple of ways.

    1) ImportSheet

    2) Point QTP to look at the spreadsheet via the Test Settings dialog

    Have a look at ImportSheet in the Helpguide. There is a little bug regarding row iteration when you import but this is well documented in this forum and easily worked around.

    Once you have imported it you use the DataTable object it's associated methods to access the data

  5. #5
    Moderator
    Join Date
    Sep 2001
    Location
    Doncaster, UK
    Posts
    5,788
    Post Thanks / Like
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)
    Total Downloaded
    0

    Re: Import data into script from excel

    Steve you can either right-click the datatabe and selet - Sheet > Import > From file, or use:

    DataTable.ImportSheet "C:\ISBN.xls" ,1 ,"ISBN"

    Mark Smith.

  6. #6
    Member
    Join Date
    May 2007
    Location
    Dublin, OH USA
    Posts
    467
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Total Downloaded
    0

    Re: Import data into script from excel

    Thank you Robbie and Mark for your suggestions.

    I will work this out in my script.

    SBsteve

  7. #7
    Member
    Join Date
    May 2007
    Location
    Dublin, OH USA
    Posts
    467
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Total Downloaded
    0

    Re: Import data into script from excel

    I'm now using this code:

    ================================================== =========

    DataTable.ImportSheet "C:\ISBN.xls" ,1 ,"ISBN"
    datatable.GetSheet(1)
    Dim rowCount, val
    rowCount=datatable.GetRowCount
    For i=1 to rowCount
    datatable.SetCurrentRow(i)
    val=datatable.Value("ISBN","Global")
    msgbox(val)
    Next

    ================================================== ========

    But when I run my script I get an error message, "The DataTable.ImportSheet operation failed. The specified sheet does not exist." What am I doing wrong??

    SBsteve

  8. #8
    Junior Member
    Join Date
    Apr 2008
    Posts
    25
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Total Downloaded
    0

    Re: Import data into script from excel

    Hi Steve,

    Try this,


    DataTable.ImportSheet "C:\ISBN.xls" ,"Sheet1" ,"Global"
    Dim rowCount, val
    rowCount=datatable.GetRowCount

    For i=1 to rowCount
    datatable.SetCurrentRow(i)
    val=datatable.Value("ISBN","Global")
    msgbox(val)
    Next



    Here i am assuming that you input excel sheet has a column with the header "ISBN" , then this piece of code will return each cell under that column one by one.

    -
    Vikas

  9. #9
    Member
    Join Date
    May 2007
    Location
    Dublin, OH USA
    Posts
    467
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Total Downloaded
    0

    Re: Import data into script from excel

    Vikas,

    That works great, but the msg box displayed all of the rows with data, then went on to the following blank rows as well. I had to kill the QTP process to get out of it.

    What can I add to my code to only go to the last row that has data in it then stop?

    Right now there are 26 lines of data, but that can change.

    Thanks again,
    SBsteve

  10. #10
    Junior Member
    Join Date
    Apr 2008
    Posts
    25
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Total Downloaded
    0

    Re: Import data into script from excel

    Hi Steve,

    I tried the same piece of code and it's working fine for me.

    So i am guessing either of the following might be the problem in your case.

    1) In QTP client , go to File->Settings->Run
    Under "data table iterations" current setting is "Run on all rows" , you will have to change this to "Run one iteration only"

    OR

    2) You may have to create a fresh excel sheet , because the excel you are using might have previously had many rows, which somebody might have just cleared the cells , instead of deleting the entire row.
    Try by creating a new excel sheet with just a few rows from scratch , and you will see it working.

    Regards
    Vikas

 

 
Page 1 of 2 12 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Search Engine Optimisation provided by DragonByte SEO v2.0.36 (Pro) - vBulletin Mods & Addons Copyright © 2016 DragonByte Technologies Ltd.
Resources saved on this page: MySQL 10.71%
vBulletin Optimisation provided by vB Optimise v2.6.4 (Pro) - vBulletin Mods & Addons Copyright © 2016 DragonByte Technologies Ltd.
User Alert System provided by Advanced User Tagging v3.2.8 (Pro) - vBulletin Mods & Addons Copyright © 2016 DragonByte Technologies Ltd.
vBNominate (Lite) - vBulletin Mods & Addons Copyright © 2016 DragonByte Technologies Ltd.
Feedback Buttons provided by Advanced Post Thanks / Like (Pro) - vBulletin Mods & Addons Copyright © 2016 DragonByte Technologies Ltd.
Username Changing provided by Username Change (Free) - vBulletin Mods & Addons Copyright © 2016 DragonByte Technologies Ltd.
BetaSoft Inc.
Digital Point modules: Sphinx-based search
All times are GMT -8. The time now is 03:58 AM.

Copyright BetaSoft Inc.