Hello i want to build an automation that would let me import data from another file
The file is a bank report, so it has several column
First i need to filter what records to i mport: looking at the money amount i should import all records that have a - before the amount. Like -100. -120, -70 ecc
Then i must be able to decide to what sheet import and if there are already data to append them at the end
Is that possible?
thank you
Look up Power Query. It is part of Excel. It is easy to implement and it does all the things you want it to do.
The best way to use it is to save the bank report to the same directory with the same name every time. Overwrite the old data. This is important, because once you set up Power Query, it will always look for the same file in the same directory all the time, so the connection will always be valid.
One of the things you can also set up is to have Power Query automatically refresh every time you open the file. So you can download the bank statement and then open the workbook and the new data will be there.
Information comes in as an Excel table. One of the benefits of an Excel Table is that any information you put in immediately to the right of the table becomes part of the table and it will "remember" formulas, formats and validations.
Tables also know how big they are, so if the bank statement has 100 lines this month and 95 the next, you will import exactly the amount of data you need.
One spreadsheet to rule them all. One spreadsheet to find them. One spreadsheet to bring them all and at corporate, bind them.
A picture is worth a thousand words, but a sample spreadsheet is more likely to be worked on.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks