Workbook Read Range still doesn't handle VLOOKUP properly

Help Studio
, ,
1

Hi there,

the workbook read range activity does not work in case of excel cells with VLOOKUP formula. The read value is “NoValueAvailable”.

Therefore, I find out that you should use the Read Range activity of Use Excel in a Excel Process Scope. BUT this activity does not work really because the performance takes too much time ( I stopped the Debug run because it took longer than 5 minutes! and nothing happened).

So I am using the workbook Read Range activity and took into a Excel VBA macro the check whether the formula VLOOKUP is used. It is not the greatest solution, but for the performance it is the best right now.

Is it possible, to integrate VLOOKUP formulas into the workbook Read Range activity? Or alternatively, is it possible to adjust the Excel process scope Read Range Activity in order to run much more faster?

I use Studio Enterprise version 2024.10.7.

Much appreciated.

Kind regards
Stefi

2

Hi @stefi

Use VBA to Extract VLOOKUP Values Before Reading (Fastest approach with Workbook Read Range)

Use Invoke VBA to run a macro that copies the values of VLOOKUP formulas into a new column or replaces them with their calculated values.
Then,

Use Workbook Read Range to read the updated values.

Happy Automation.

1 Like
3

@stefi

did you try with and without preserve formatting? somethings it would read the data if the vlookup is from the same file and not a different file

cheers

1 Like
4

This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.