Excel bank statement to Sage Automation - Advice required!

I am working in Excel 2003 (for compatibility purposes when switching between 2003 and 2007).

The idea to create a banking report in excel for upload into Sage 50 and post transactions based on the narratives in an excel bank statement.

My initial ideas:

Step one:

Bank statement is pasted into sheet one of the excel workbook (we’ll call ‘the importer’ for the purposes of this example). Using VBA I would like the importer to read each transaction line by line and prompt me to confirm the nature of each transaction (using userbox, combobox, or similar).

The combo box should display the transaction and prompt the user for the following: transaction type (bank payment/supplier payment,), VAT Rate (code), VAT amount, nominal account to post the transaction to (if transaction type is a bank payment), or supplier account (if transaction type is a supplier payment), and whether or not to save these selections for next time the same bank narrative is on the statement.

If the option is chosen to save the selections for next time, I want the importer to save the narrative and the chosen criteria to the questions on a separate tab, “Datatab”. This tab will be used to build a database of bank narratives and the corresponding criteria. The idea being that next time this transaction narrative appears on the bank statement, the importer recognises that this transaction type has previously been processed and uses the previous selections to allocate the transaction to the output sheet.

Which brings me to the 3 desired outputs of the programme:

Output 1 – Excel file containing transactions as confirmed by the user in step one for upload into Sage compatible format. This should also contain the transactions allocated automatically using the historic information on the data tab.

Output 2 – Excel file containing unallocated transactions for manual entry into Sage.

Output 3 – Excel file containing a list of all transactions allocated as supplier payments this month.



What I need to know, initially is this possible and what is the difficulty in creating this workbook. What kinds of functions would it entail from a technical perspective? Is there anything I should look out for, and any other advice would be brilliant!

Thank you.