How to always add Current Time on the top tow of Excel

Hi All,

Every time starts the automation, I need to log the Process Start Time and Complete Time in the row 2, which means insert the latest time record in the row 2. But I dont know how to do it. can anyone helps. Appreciate it.

row2new

Hi @mason_wong,

I’ll suggest you to store following VBA code in .txt file,

Sub insertRow()

    Rows("2:2").Select
    Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
End Sub

This is to insert a row at row 2.

Use Invoke VBA activity with that .txt file at the starting.
Followed by Assign activity for Time & Action

Time = Now.ToString("M/dd/yyyy hh:mmtt") —> Write Cell Time in"A2"
& Action = "Started" —> Write Cell Action in “B2”

Use that same Invoke VBA activity with that .txt file at the ending.
Followed by Assign activity for Time & Action

Time = Now.ToString("M/dd/yyyy hh:mmtt") —> Write Cell Time in “A2”
& Action = "Completed" —> Write Cell Action in “B2” (Action value changes here as completed)

Hi Samir,

Thank you so much for your very accurate answer. But I was wondering if there is a way without invoking VBA.

Thx again

@mason_wong

Okay… If you want to achieve the same without Invoke VBA then, You can add a datarow at the beginning of the table using invoke method activity… pls follow the steps below.

  1. Read Range to get excel data in dataTable var —> dt
  2. create new variable of type DataRow —> datarow1
    use it in assign —> datarow1 = dt.NewRow
  3. Use 2 assigns for Time & Action
    datarow1("Time") = Now.ToString("M/dd/yyyy hh:mmtt")
    datarow1("Action") = "Started"

Till now we’ve assigned values for those 2 columns in DataRow. Time to add DataRow in datatable at the beginning.

  1. Use Invoke method activity with MethodName - InsertAt & TargetObject - dt.Rows
    and create 2 parameter arguments in it, first of type DataRow with value datarow1 (variable) & 2nd int32 with value 0 (As you want to add row at beginning of a datatable).

here’s one screenshot to give you exact idea of Invoke method activity and its settings

That’s it. add this at the starting & ending of the project but don’t forget to change Action value as it should be “Completed”. :slightly_smiling_face:

Hi Samir,

Really appreciate your explanation. I have followed all your steps except step 3, cuz I would like to add a new row first and then add the “time”& “action” after certain other actions.

However, the code seems not working. I run the code and the Excel has nothing changed. Besides, even I include step 3, the “Time” and “Started” still were not able to add into the excel.

The below is my code. Could you pls have a look and kindly let me know what’s wrong.

Thx

@mason_wong,

See, if you eliminate step3 then also it’ll add empty row in datatable at the beginning.
Have you create those 2 arguments?? which I’ve shown in my last post (Screenshot), DataRow argument (In) with value as variable datarow1 & Inst32 argument with value 0.

Here’s one sample workflow —> Test1.xaml (9.3 KB)

Check the workflow in build datatable there’re 2 rows, run it you’ll get the output with 3 rows (with new row at the beginning).

Yes, I have created the 2 argument as well. I actually did the same as your workflow just provided. However, if i use read range activity, it still shows nothing.

yes, I did. Your workflow is working if u hv build datatable. But i cant do it by using read range activity and selecting an Excel.

Okay @mason_wong, here’s one example with Excel data, check the excel file data and run it.
addDataRowAtBeginning.zip (22.8 KB)

1 Like

The Zip works. The result stored in the datatable instead of showing in Excel.
thank you so much

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