topic You can apply a filter to a… in Product Forum

Find Value in MS Excel

michael.thorpe — Wed, 21 Nov 2018 20:34:00 GMT

Maybe I'm missing something really obvious here - but I'm really struggling to build something that would allow me to use the Find (CTRL F) tool within Excel. I've created an Extended VBO, I have a separate object that interacts with Excel in a more 'direct' way (ie update a dynamic sheet). I have tried Send Keys (^F) but all I get is the format cell option - is there something really obvious I'm missing here? A bit of background, just incase anyone has any other ideas for my current problem - I need to add a label to a data set that I am working with. This label (Salesperson) is dependent on what a second piece of information is (Client Location). We have a data set of around 120,000 lines (and growing about 50/day) which means that working with this as a single collection is out of the question. Next, I split each location 20 different groups, (for which working with collections would be much easier) with the idea being I would add each of these 20 groups to a dictionary meaning i dont have to loop through each line. The problem with this (would have been the same if I had tried with the 120k originally) is that Blue Prism runs out of memory (I can get about 14 before it crashes). Therefore, I'm thinking that importing this information into Blue Prism is out of the question. We currently don't have access directly to the DB, or to the APIs that Microsoft Dynamics - our CRM System have (internal security issues). So that's led me to where I am now - thinking the easy way to do this is going to be find each line of data relating to a specific postcode, then whittle down from there - hopefully i will have a collection with no more than 20-25 lines rather than 120k. Any ideas to; 1) How to Find a value in Excel 2) A wider or better solution to my current issue? Cheers!

Sounds like an OLEDB query…

John__Carter — Wed, 21 Nov 2018 21:37:00 GMT

Sounds like an OLEDB query might be the answer - it may allow you to read a subset of data instead of consuming the whole file. It's also possible to update an Excel file via OLEDB too. https://portal.blueprism.com/system/files/2017-09/Blue%20Prism%20-%20Gu…

You can apply a filter to a…

AmiBarrett — Wed, 21 Nov 2018 21:41:00 GMT

You can apply a filter to a specified cell range. I'm attaching a text file with XML containing an action to do this. Copy the contents to clipboard, then open your Excel VBO and paste as a new page.

Hi Michael, There was a…

MichalSzumski — Thu, 22 Nov 2018 13:52:00 GMT

Hi Michael, There was a code somewhere on the forum that allows to perform action similiar to CTRL+F. Below is that code that I'm using to search quickly in Excel: Dim excel, sheet As Object Try sheet = GetWorksheet(Handle, Workbook, Worksheet) excel = sheet.Application If Range="""" Then Message = ""No Range provided"" Else sheet.Activate sheet.Range(Range).Find(What:=Value, LookIn:= _ -4163, LookAt:=1, SearchOrder:=1, SearchDirection:= _ 1, MatchCase:=False, SearchFormat:=False).Activate CellRef = excel.ActiveCell.Address End If Catch e As Exception Message = e.Message Finally excel = Nothing sheet = Nothing End Try I created another page in Excel VBO for it and it worked just fine for 8000 rows but it may not be useful when there are duplicates in selected range. Inputs: handle, Workbook, Worksheet, Range (range where You want to search), Value (searched value) Outputs: CellRef, Message Best regards Michal

Thank you for your…

michael.thorpe — Thu, 22 Nov 2018 15:21:00 GMT

Thank you for your suggestions guys, really appreciated. @mszumsk I've gone with your option in the first instance and it works well - I might look to extend the code a little so that it finds all matches and adds to a collection but thats for later on! It seems crazy that there is nothing out the box in the VBOs that has a find function - even the similar objects within Collection Manipulation aren't the best. Appreciated! MT