I have a question regarding excel automation.
I currently have an excel file where bank balance for different banks is stored in different sheets, what I wanted to know how to do is, how can I consolidate the balances of each bank based on current date into a new excel file and add the name of the bank as well.
My files look like this: first image is the consolidate file the other two are the sheets.
Hi @ramon.gonzalez,
Use Read Range to a DataTable of the excel the use Filter Data Table to filter the info in the Data Table based on the date. Finally just use a Write Range to paste the information in any excel you like.
Thank you!
Now I have a question that is not relevant to the excel thing. I am getting the following output: “Process finished due to no more transaction data” even though I do have transaction item as 1 so it is just ending the workflow without ever going into the process workflow.
It is a linear process.
I think you are using the REframework right?
There are a couple of similar issues other people faced like that one. Take a look at these posts:
Process finished due to no more transaction data - Assignment 1
Level 3 Assignment 2 process finished due to no more transaction data
Process finished due to no more transaction data Level 3 Client Security Hash
Yes, I am using the REF Workflow however this is a linear process so I need to do the following: “For a linear process (not repetitive), set out_TransactionItem only for in_TransactionNumber 1 - first and only transaction.”
However, I am unsure how to do this and found 1 solution here in the forums which were similar to this:
I do get the message from write line but right after that, I get the process finished message.
Try in_Transaction is greater than 0
Didn’t work, I still get the following logs
Hi, I have tried that way but I am not getting the desired result, I have attached the Excel files plus the workflow so that it’s better to help with putting it into practice, thank you for helping me out.
Is it possible to add a new column to the consolidated file that says from what bank it is?
SaldoBancosIndividual.xlsx (9.4 KB)
SaldoBancosAppend.xlsx (8.9 KB)
Consolidation_Helper.xaml (10.1 KB)
@ClaytonM can you please help me out with this predicament?
Don’t you want to output each individual Bank to a new sheet?
You need to loop through each Bank, then use that as your sheet name. And, filter the datatable by both the Bank and the Current Date. But, correct me, if I’m understanding this wrong.
Let’s say your data from the Read Range is dt1. You can take the unique Banks to loop through with an Assign activity.
banks = dt1.AsEnumerable.Select(Function(r) r("Bank").ToString ).Distinct.ToArray
Then, use that in a For each and filter by each bank and current date
For each bank in banks
Filter Data Table // column "Bank" = bank, column "Date" = Today
Write Range // bank as sheet, dt_out
Here is your workflow with those changes: Consolidation_Helper.xaml (11.3 KB)
When you output dt_out after the Filter Data Table, it should contain all columns that your original data you got from the Read Range, including the “Bank” column. You can also use the Output Column property in the Filter Data Table to only get certain columns.
If you have problem using the Date in the filter, the alternative way to filter is using .net inside an Assign activity like this:
dt_out = dt1.AsEnumerable.Where(Function(r) r("Bank").ToString=bank And Convert.ToDateTime(r("Date").ToString)=Today).CopyToDataTable
However, with the possiblity of there being no rows for today, you would want to store this to an array first to check the count.
dt_rows = dt1.AsEnumerable.Where(Function(r) r("Bank").ToString=bank And Convert.ToDateTime(r("Date").ToString)=Today).ToArray
If dt_rows.Count>0
dt_out = dt_rows.CopyToDataTable
Like I said, that is just an alternative to the Filter Data Table, which gives you more .net freedom.
Note: I didn’t look at your excel files and was basing these ideas on your original post and the .xaml you provided in your previous post.
Regards.
Thank you for your response, what I need to do is get the bank name, account number, date and balance for each bank filtering on date for every sheet in Excel file 1 then that output needs to be written into Excel file 2 that will have every bank with the account numbers, date and balance, not a new sheet in Excel file 1, that is then sent to x person via email. All values in Excel file 2 are deleted every time the bot is ran (this is done after init all settings in the REF).
Any chance you are able to get into a call with me and share a screen to help me with it? It might be best to see and I can explain the problem better and that way we can figure out a solution.
I appreciate your help and time.
I’m pretty busy this week. I might be able to screen share like Thursday at specific times, though.
Did that .xaml I uploaded not include most of what you needed to do?
It reads in your sheet with the Bank information (assuming the filepath used is that file), then takes the data and outputs each bank to a file using the bank name as the sheet name, and only for the current day. The only thing you would need is to use the correct filenames for your Read Range and Write Range. The output will include all columns in your input file which should have the Bank column and everything.
Please refer to my last post for more details too, and let me know if there is something not done correctly, and maybe I can point out the problem.
Regards.
Oh, I didn’t see that you attached a workflow. It is exactly what I wanted the only thing I had to change was add an excel application scope activity and pass the filtering in that scope and then use an append range rather than write range because I need the values in 1 sheet together and the write range activity overwrote the data. Thank you so much for your help.
This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.
ashokkarale
Anil_G
ppr
shyamala_shyamu
Josh_James
m-takeda1
Shahabaz
harshika.10732998
sharazkm32