Just to confirm, the process is something like this?
Open up recent report (new excel file)
Copy all the data from specific tab
Open my Excel File
Overwrite all the data in this specific 'existing' tab
Move on with the flow
If so, I recommend Excel VBA. We can create a Macro workbook where the bot writes to cells within the macro workbook and the Macro will read those cells to perform the Macro. Example:
Create a Macro workbook with that looks like the following, be sure to Save As .xlsm:
Create the following Macro by going to View -> Macros:
Sub CopyDataBetweenWorkbooks()
' Declare variables
Dim From_Workbook_FilePath As String
Dim From_Workbook_FileName As String
Dim From_Workbook_SheetName As String
Dim To_Workbook_FilePath As String
Dim To_Workbook_FileName As String
Dim To_Workbook_SheetName As String
' Get values from cells
From_Workbook_FilePath = ThisWorkbook.Sheets("Sheet1").Range("B1").Value
From_Workbook_FileName = ThisWorkbook.Sheets("Sheet1").Range("B2").Value
From_Workbook_SheetName = ThisWorkbook.Sheets("Sheet1").Range("B3").Value
To_Workbook_FilePath = ThisWorkbook.Sheets("Sheet1").Range("B4").Value
To_Workbook_FileName = ThisWorkbook.Sheets("Sheet1").Range("B5").Value
To_Workbook_SheetName = ThisWorkbook.Sheets("Sheet1").Range("B6").Value
' Create workbook and worksheet objects
Dim From_Workbook As Workbook
Dim From_Sheet As Worksheet
Dim To_Workbook As Workbook
Dim To_Sheet As Worksheet
' Open the from workbook and get the from sheet
Set From_Workbook = Workbooks.Open(From_Workbook_FilePath & From_Workbook_FileName)
Set From_Sheet = From_Workbook.Sheets(From_Workbook_SheetName)
' Open the to workbook and get the to sheet
Set To_Workbook = Workbooks.Open(To_Workbook_FilePath & To_Workbook_FileName)
Set To_Sheet = To_Workbook.Sheets(To_Workbook_SheetName)
' Clear the To_Sheet
To_Sheet.Cells.Clear
' Copy the entire data from From_Sheet to To_Sheet
From_Sheet.Cells.Copy Destination:=To_Sheet.Cells(1, 1)
' Save and close the workbooks
From_Workbook.Close SaveChanges:=True
To_Workbook.Close SaveChanges:=True
End Sub
When the bot gets to this point, you will have the bot open the workbook, fill out B2 through B6 and then run the Macro. It should run same as if you copied and pasted yourself.
Best of luck!
