I am using a For each row actvity where I loop for each row in a data table. Immediately before the For each row activity, I use the Excel application scope and then “Read Range” for one Excel worksheet.
Later on within this loop, I need to open the same source Excel worksheet, Execute a Macro, and then call a new variable created by this Macro in a Do while loop. I do this by calling the variable with row(“Column Name”).ToString.
The problem is, when I use Write Line to see what value is being passed in the Do while loop, it uses the original value (from the Excel Application scope outside of the For each row activity) and not the more recently called Excel Application Scope that executes the Macro.
How can I make sure the program, within my for each row loop, uses the new variable value that is updated by the Macro and not by the original excel value from outside the for each row loop?
Let me know if there is further clarification needed.
Basically what I am looking to do is to run a Macro on the excel being used for the For each row loop and overwrite the Data Table with the resulting worksheet that is created by the Macro. So when I use row(…) statements the current row is looking at the new updated values.
After the macro is executed , you can do dtExcel = new DataTable() and load the new excel data using Read Range.,after that you can iterate the data table rows using foreach.
I may not be sure about my answer, does the attached image look like your requirement?
Here is a simplified example of what I am trying to accomplish. I think your understanding is close. Let me know if this makes more sense. The issue I see is that the program is using the row(…) values from the initial Read Range in the first excel application scope. What I WANT is for it to read in the range after executing the Macro in the second excel application scope.
ashokkarale
anjani_priya
Dheerendra_vishwakarma
Parvathy
Aakash_Singh_Rawat
Luis_Fernando
bjorn2390
neco
pere
Shiva_Nikhil