Results 1 to 6 of 6
  1. #1

    How to access only the visible XL cells?

    I am importing an excel sheet where certain cells are
    filtered out based on a criteria!!

    When i try to read only the visible cells, QTP reads the
    values of the cells that are hidden because of filters.

    My script looks like this:

    Set objXL = CreateObject("Excel.Application")


    objXL.Application.DisplayAlerts = False

    Set objSheet = objXL.Worksheets.Item(1)

    objsheet.Range("A1").Autofilter 1,">14"

    Msgbox objsheet.UsedRange.Rows.Count



    Set objXL = Nothing

    DataTable.ImportSheet "C:\Test1.xls","Sheet1","Global"

    Rowcount = DataTable.GetSheet("Global").GetRowCount

    For counter = 1 to Rowcount
    DataTable.SetCurrentRow counter
    Msgbox DataTable("Numbers",dtGlobalSheet)


    While executing this script, QTP reads all the cells and displays the values that are less than 14 too. I mean, QTP reads the cells that hidden because of filter.

    How to read only the cells that are visible by ignoring the hidden out cells.

    Please let me know.

    - Devaraj R

  2. #2

    Re: How to access only the visible XL cells?

    The technique you are using won't work. The best option you have is to delete the rows that you don't need and then save it as another excel. Then you can import

  3. #3

    Re: How to access only the visible XL cells?

    The Actual Scenario is we have a load of Test cases in an Excel sheet. For every release, we decide whether to execute a particular Test Case or not with a column and giving the values of either "Yes" or "No" to it.
    Yes - Execute the Test Case
    No - Do not Execute

    When there are more number of "No"s, QTP takes time in reading the "NO" lines unnecessarily.

    I thought, if I could use a filter and filter out the "No" and have only the "Yes" cases, I can save time. Also, when the number of Cases are about to increase, this could be of much help!!

    So, there is no workaround for this in QTP, when we are quite particular in using XL sheet?

    - Devaraj R

  4. #4

    Re: How to access only the visible XL cells?

    Yes, instead of reading all excel cells. You can use the Find method on column and then look for values as Yes only. This way Excel will give you the row # of the cell that has yes and you don't need to loop through all the rows of that.

  5. #5

    Re: How to access only the visible XL cells?

    Hi Tarun,
    With Find method of the Excel COM, I could get that working.

    Thanks a Lot!!

    - Devaraj R

  6. #6

    Re: How to access only the visible XL cells?


    This could work in my case where I am looking for a specific string!!

    But just in case, if we have to operate on those rows, based on the value of a column, say, those which have the value of ">10000".

    Is there any work around in QTP, other than reading the value of every row and checking if that is greater than 10000?

    I know that this query sounds more a question of VBS than QTP!! But just being curious if there is a way to solve this. So, Kindly excuse me once.

    - Devaraj R



Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
BetaSoft Inc.
All times are GMT -8. The time now is 06:02 PM.

Copyright BetaSoft Inc.