Invoking VBA - Changing first row in Excel

Hi,

I’m wondering about the Invoke VBA Activity, I am working with an Excel spreadsheet and based on the filter the first row could be different everytime based on the report from that day, today it could be row 2 at the top and tomorrow it could be row 17.
Is there a way of looking for the top row then executing the VBA that it picks up the first row so that when executing a formula that it will still work correctly?

Regards,
Shawn

Hi @shawnmurray

Based on datatable select query you can do other manipulations

In the top select the columns and based on that you can do the invoke vba script

Thanks
Ashwin.S

Hi @AshwinS2

Thank you but I’m not quite sure what you mean?

Regards,
Shawn

Hi @shawnmurray,

This activity is awesome, because it’s like injecting the code to a excel file that doesn’t have the macros enable and you are able to do almost anything in excel using VBA. To use the activity is really simple.

  1. Drag and drop an excel application scope.
    image
  2. Place the invoke VBA inside of it.
  3. Select a txt file that contains the code and place the macro name.
    image
    The one after sub is the name of the macro you can see “Test()”, just type Test as the name.
    Any questions let me know.vba.zip (17.8 KB)
1 Like

You can use lookup to find the position. You just need to pass the column name. it will return column’s position.

1 Like

@rmunro
Thats perfect but one question, i am using the VBA to filter and then execute a formula so based on the day the number of the top row would be different and the VBA will only look for , for example row 2, when it could be row 7

Is there anyway to do that in VBA that it just picks the top row and works from there?

Thanks
Shawn

Hi @shawnmurray,

What I’m thinking of is doing a Match formula. This will return the column number, you can save it in a variable (VBA or Uipath) then edit the code using this number.

This is just a sample:
ActiveCell.FormulaR1C1 = “=MATCH(”“burger”",RC[-5]:RC[-2],0)"

image

1 Like