SPONSORS:






User Tag List

Thanks Thanks:  0
Likes Likes:  0
Dislikes Dislikes:  0
Results 1 to 8 of 8
  1. #1
    Junior Member
    Join Date
    Jul 2001
    Location
    New York, NY
    Posts
    26
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Total Downloaded
    0

    Reading columns in Excel

    I have an Excel file. The first row contains the column names. On the second row, right below the column names are the values associated with the column names. It will look like this:

    column1 column2 column3
    $123.00 $223.00 $321.00

    In my script, I want to be able to specify the column name and be able to retrieve the value associated with that column name.

    I have searched this and other forums and googled all over the web, but cannot seem to find a way to do this. Can anyone please show me how to do that.

    Thanks.

    [ 02-12-2006, 10:10 PM: Message edited by: jonlien ]

  2. #2
    Moderator
    Join Date
    Jul 2005
    Location
    Delhi
    Posts
    15,575
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    7 Thread(s)
    Total Downloaded
    0

    Re: Reading columns in Excel

    Well if you say that you have googled it and didnt find anything then it's something that i cant believe....Well you would should not look for the exact code that you want, but you should look for how to achieve something that you are looking for. I would say explore more on Excel Automation COM Model and find the solution.

  3. #3
    Member
    Join Date
    Sep 2005
    Location
    Bangalore
    Posts
    42
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Total Downloaded
    0

    Re: Reading columns in Excel

    Dear jonlien,

    Try this

    **************************************************
    Function isParameterExists(sheetName, paramName)
    isParameterExists = FALSE
    ParamTotal = DataTable.GetSheet("Global").GetParameterCount
    For i = 1 To ParamTotal
    value = DataTable.GetSheet("Global").GetParameter(i).Name
    'Msgbox value
    If strComp(paramName,value)=0 Then
    isParameterExists=TRUE
    Exit Function
    End if
    Next
    End Function
    'Example:
    If isParameterExists("Global","column1") then
    msgbox "Patameter exists"
    n=datatable.GlobalSheet.GetRowCount
    for i=1 to n
    datatable.GlobalSheet.setcurrentrow(i)
    value=DataTable("column1",DtGlobalSheet)
    Msgbox value
    Next
    Else
    msgbox "Patameter does not exist"
    End if

    **************************************************
    Regards
    Ravi B

  4. #4
    Member
    Join Date
    Sep 2005
    Location
    Bangalore
    Posts
    42
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Total Downloaded
    0

    Re: Reading columns in Excel

    Exactly tarun.i could have given some hints to achieve something that he is looking for. iam also suggesting tht explore more on Excel Automation COM Model.

    Regards
    Ravi B

  5. #5
    Moderator ifraser's Avatar
    Join Date
    Jul 2004
    Location
    Brisbane, Australia
    Posts
    2,090
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Total Downloaded
    0

    Re: Reading columns in Excel

    I have put code in this forum that will do what you want. I think that you need to work on your search skills before you move onto learning automation. The same code is also in the mercury User KB and on the Microsoft Scripting site there is also code that comes very close.

    Ian
    You can buy my Art from: "Post Cards now available"
    Ian Fraser Landscape Photography
    World Wide Shipping.

    http://mowogman.wordpress.com/

  6. #6
    Junior Member
    Join Date
    Jul 2001
    Location
    New York, NY
    Posts
    26
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Total Downloaded
    0

    Re: Reading columns in Excel

    IanF, your childish comments show that you lack people skills. Perhaps, you need to work on that before learning automation.

    I eventually created a function that does exactly what I want.

    Since I have gained so much QTP knowledge from experts such as Tarun, I would like to post my solution for others to use:

    Public Function GetValue(ColumnName)

    'Create Excel object
    Set XL = CreateObject("Excel.Application")

    'Open existing Excel spreadsheet
    Set Wb = XL.Workbooks.Open("C:\my loan.xls")

    ' Reset column to 1
    ColumnNum = 1

    'Search for column name
    While ExcelColumnName <> ColumnName

    ExcelColumnName = Wb.Sheets("Sheet1").Cells(1, ColumnNum)

    ColumnNum = ColumnNum + 1

    Wend

    'Store value in DataValue
    DataValue = Wb.Sheets("Sheet1").Cells(2, (ColumnNum - 1))

    GetValue = DataValue

    'Save and close Excel
    XL.DisplayAlerts = False
    Wb.SaveAs "c:\my loan.xls"
    Wb.Close
    XL.Quit

    End Function

  7. #7
    Moderator ifraser's Avatar
    Join Date
    Jul 2004
    Location
    Brisbane, Australia
    Posts
    2,090
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Total Downloaded
    0

    Re: Reading columns in Excel

    Jonlien

    I stand by what I said my post I also indicated that there was help to be had you just need to learn how to do reseach. Self learning is an important skill for any job and for automation it is even more so because there are no real rules for what good or bad automators do.

    Ian

    And to the moderator if you are going to delete posts dont play favorites.
    You can buy my Art from: "Post Cards now available"
    Ian Fraser Landscape Photography
    World Wide Shipping.

    http://mowogman.wordpress.com/

  8. #8
    Senior Member
    Join Date
    Sep 2000
    Location
    Twin Cities, MN, USA
    Posts
    653
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Total Downloaded
    0

    Re: Reading columns in Excel

    How come IanF was the only bad guy? I don't get it... 3 people said, "Go fish" and only one got hammered. There must be something there that I can't see...

 

 

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.00%
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 07:24 AM.

Copyright BetaSoft Inc.