Hi Hasipri,
"If we automate the vlookups through blueprism what is the best possible way? Do we have to copy the vloopup formulae to each and every cell? Is there any other way?"
There are two ways to do this and they are explained as below-
Approach #1: Using predefined actions provided by Blue Prism:
- Refer InputFiles.jpg. Refer their names in the title bar.
Your scenario of the files may or may not be the same as explained below, but you can get an idea of what can be done.
First file is your Main file where only Name column is filled initially and after running the process the Age and the City columns are populated by looking up from the external xlsx file (the second one).
- Refer 1.jpg: This is how the Process diagram looks when the predefined actions of BP are used
- At first you are simply setting the vlookup formula only in the first cell of Age column (Screenshot 2.jpg)
- And then you are simply pasting this formula till the last row of that column (3.jpg)
- There is an action available to get the number of rows in a sheet. But note that it will give the value of the column that has the maximum number of rows so assuming you have such a primary column in your sheet (eg Sr.No or Name in this case).
- You do the same for the City column.
Approach #2: Using a Code stage
- Screenshot 4.jpg is the process diagram for this approach
- This involves creating a new object and using the code stage. I created a copy of an existing action available in the MS Excel VBO, made the necessary removals and added a new action page. This new page looks as 5.jpg
- I am passing the last row from the calling Process to this action and using it in the code as below
Rich (BB code):
Dim sw, dw As Object
Dim ss, ds As Object
Dim excel, sheet As Object
Try
sw = GetWorkbook(Handle, Source_Workbook)
ss = GetWorksheet(Handle, Source_Workbook, Source_Worksheet)
sw.Activate()
ss.Activate()
excel = ss.Application
sheet = excel.ActiveSheet
sheet.Range("B2:B" & LastRow) = "=VLOOKUP(A2,'C:\Users\Administrator\Desktop\[ExternalWkbk.xlsx]Sheet1'!$A$2:$C$4,2,FALSE)"
sheet.Range("C2:C" & LastRow) = "=VLOOKUP(A2,'C:\Users\Administrator\Desktop\[ExternalWkbk.xlsx]Sheet1'!$A$2:$C$4,3,FALSE)"
Success = True
Catch e As Exception
Success = False
Message = e.Message
Finally
sw = Nothing
ss = Nothing
dw = Nothing
ds = Nothing
excel = Nothing
sheet = Nothing
End Try
You simply need to add the formula for your required columns in the below lines:
Code:
sheet.Range("B2:B" & LastRow) = "=VLOOKUP(A2,'C:\Users\Administrator\Desktop\[ExternalWkbk.xlsx]Sheet1'!$A$2:$C$4,2,FALSE)"
sheet.Range("C2:C" & LastRow) = "=VLOOKUP(A2,'C:\Users\Administrator\Desktop\[ExternalWkbk.xlsx]Sheet1'!$A$2:$C$4,3,FALSE)"
Comparison:
Approach #1 did not use any action other than what was already available in BP.
So if you do not have much awareness of Visual Basic coding or macros you can use this.
It took the process 2 seconds and 78 milliseconds for 1000 rows of data for 2 columns (Age and city)
Approach #2 uses the Visual Basic coding semantics which is the language used by the other actions present in MS Excel VBO.
It took 2 seconds and 70 milliseconds for 1000 rows of data for 2 columns (Age and city)
These were run from within the diagram (and not from Control Room) with Debug Speed set to the Fastest.
I am using a Virtual Machine so things would be faster if you are using a local machine.
Approach #3: Using a connection string
You can connect to Excel using a Connection String and access the data on the sheet using SQL queries. I won't recommend this given the complexities that may involve while applying vlookups and formulas
Approach #4: Using SQL and Blue Prism approach - the one that you are thinking of
This will involve designing the database according to the way you need it. Then use SQL queries on that data.
The actions to connect to a database and fire queries are very much available in Blue Prism, but I leave this approach for you to explore. While designing approaches #1 and #2 I didn't see a reason to discard excel and start using sql, unless you will be finding the excel file with the existing data of 2,50,000 records to be very heavy and slow in response.
Approach #5: Macros
This was one of the approaches but cannot be utilized due to security reasons so I won't explain how that can be used here.
Based on the above shown approaches you will need to gauge how complex or simple your sheet is, how the data is arranged and if the formulas can be applied in a straightforward way as shown above and decide upon the best suitable option.