philfer
01-06-2008, 10:50 AM
I work in finance and we have a in-house built accounting package.
There are two types of excel macro we use :-
1) for the first you can specify the data you want and the fund i.e. all bond data for a fund. You click a button and it opens an instance of the accounting software and you can see it navigate to the correct page and then copy and paste the data into the excel sheet. I though it might be using SendKeys but it doesnt copy the whole page just particular fields.
This macro can also be used to paste info to the accounting software i.e if there were 20 equity trades in one day instead of posting each individually you can put the info into an excel macro then press a button. It opens an instance of the accounting software navigates to the correct page and posts the trades
2) the second type of excel macro appears to look at the tables that sit behind the GUI we see (I am assuming the accounting package is a huge database and the GUI we see just runs queries on the data).
I have always wondered how to write these macros but can find any practical examples in books or help of this kind.
I have been told I need to knoe the object model but dont know what this is, how to find it and what to do with it once i have it.
I assume I will need to set up a reference in the VBE but to what? Then what terminology would I use in Excel VBA.
Does anyone know where I can get practical xamples of this kind of thing, where to find the object model, what references to use, what terminology to use etc
This has been bugging me for ever. The problem is i have moved employers and cant view the code of these macros!!!
There are two types of excel macro we use :-
1) for the first you can specify the data you want and the fund i.e. all bond data for a fund. You click a button and it opens an instance of the accounting software and you can see it navigate to the correct page and then copy and paste the data into the excel sheet. I though it might be using SendKeys but it doesnt copy the whole page just particular fields.
This macro can also be used to paste info to the accounting software i.e if there were 20 equity trades in one day instead of posting each individually you can put the info into an excel macro then press a button. It opens an instance of the accounting software navigates to the correct page and posts the trades
2) the second type of excel macro appears to look at the tables that sit behind the GUI we see (I am assuming the accounting package is a huge database and the GUI we see just runs queries on the data).
I have always wondered how to write these macros but can find any practical examples in books or help of this kind.
I have been told I need to knoe the object model but dont know what this is, how to find it and what to do with it once i have it.
I assume I will need to set up a reference in the VBE but to what? Then what terminology would I use in Excel VBA.
Does anyone know where I can get practical xamples of this kind of thing, where to find the object model, what references to use, what terminology to use etc
This has been bugging me for ever. The problem is i have moved employers and cant view the code of these macros!!!