Printable View
I want to write a macro to open an in house accounting system go to a particular page and copy data into excel i.e. based on an account code list in an excel sheet.
I have been using the Shell function to open the other system and SendKeys to get to the correct page.
This is where I get stuck as I dont know how to copy parts of the page I get to from the accounting system and then paste it to excel
Can anyone help? It would be very gratefully received
Thanks
SendKeys stinks: its unreliable and should be your option of last resort.
The best way (in theory), is that your accounting software should have a built in method of exporting data, if not in XLS then as TXT or comma delimited.
If a program doesn't have any kind of export tools, then your next step is to see if it has an "OLE Automation Interface", also referred to as an "OLE Object Model". I'm sometimes surprised at which applications contain one of these: three OLE Object Models that I've found to be hugely valuable are Acrobat, Business Objects, and the mainframe terminal client that we use here.
Failing that, your only real option is Sendkeys.
That said, I feel your pain. I have a bunch of macros that pull data from this ancient mainframe database we have; before I wrote the macros, the only way to get the data was to cut and paste each value, one at a time, from a terminal client window.
I use OLE Automation to get the data out of the old system: my macro works the terminal client like a puppet, since there is no way to interface directly with the old database. Its kind of slow, but still 1000x faster then doing it manually.
Thanks for that DrK. How do I find out if the application has an OLE Object Model such as the mainfrome terminal client and how do I access it and use it if it does.
Sorry if I sound a bit dim, I'm quite new to this!
You have to dig through whatever documentation you have for the old application... help file, PDF docs, whatever.
I found the object model in the terminal application by accident while I was poking around in the help file. I was lucky that the built in help had thorough documentation for the object model, or else I would have had a hard time tracking down external documentation.
Google is your friend here...
If there isn't an automation object and the application has hot keys and will reliable repond to Sendkeys command, try doing a (select all) copy Sendkeys command and paste the data from the clipboard object (DataObject class of the Microsoft Forms Object Library).