Controlling another application with VBA

[Brad]

We are building a new application system with Access 2007. We need to feed
“Product†information into this new system from data that is extracted from
an older purchased package on a daily basis.

Our tests of the Exporting and Importing look good.
Using Access VBA, we have the import function initiated by a single button.

Unfortunately, the Export function in the older purchased package requires
that we go through about 10 steps on several different screens (Windows-based
system).
(There is no "batch" Export option available with this system, and we cannot
access the data through the back door)

Using the screens will work, but we have concerns that our end-users will
eventually make an error because so many little steps are needed.
They would like a “one button method†just like we are able to demonstrate
with the Import side with Access.

Is there a way in VBA to automate the screen processing of another
application system? In other words, use VBA to plug in info, pick choices,
and “Push†the enter key on another Windows-based system.

Thanks for any advice/help that you may be able to provide.

Brad

 

[dymondjack]

The only way to do this that I know of is if the other app has vba as well.
I have another application as well that uses 'proprietary' coding that is
just different enough not to be called vba. I've been able to cut it down to
two steps, and export button and a import button. The only reason I was able
to automate the export of the non-access app was because they gave me a means
to do it. There's a fair amount of softwares that do support vba, in which
case I would assume this is possible.

As a last resort, I believe there are APIs out there that can be used to
select windows, providing you know it's handle (that in itself may prove to
be a task). If you can work that out, you may be able to use SendKeys and
somehow manipulate it.

There may be ways using windows scripting, but I wouldn't know any details
on that.

This does bring to mind a program I came across a while ago called
AutoHotKey. You can apparently use this to create scripts based on recorded
actions (such as an excel macro, only through all of windows) and execute
them from keypress combinations. I downloaded the program but never learned
the scripting required, though it didn't seem too complex. How this would
differ from just going with a windows script I wouldn't know, but it may be
an option. If you can get that to work with your program, create a 'macro'
to open all the windows and click the right key, you could then might be able
to use SendKeys from vba to execute it.

http://www.autohotkey.com/

hth

--
Jack Leach
www.tristatemachine.com

- "Success is the ability to go from one failure to another with no loss of
enthusiasm." - Sir Winston Churchill

 

[Brad]

Jack,

Thanks much for your feedback. I looked at the AutoHotKey website a bit and
this might be the best way for us to approach this problem.

Brad

 

[Brad]

Jack,

I owe you a huge "THANK YOU" !

I downloaded Autohotkey and was able to use it with no training. I comes
with a "recorder" which records the "Script" so that you don't have to learn
the scripting language.

I was able to completely automate the Export process from the old purchased
system, so that our end-users will now be able to simply click on one icon
instead of having to go through several screens and enter various parameters.
This took about 2 minutes to accomplish.

Thanks again, you saved me a ton of time.

Brad

 

[dymondjack]

Glad to hear it. I wish I could say it's exremely reliable and works without
fail across all windows versions, but at this point you've done more with it
than I.

Nothing worse than repetitive work, especially when its easily prone to
someone screwing it up

I downloaded Autohotkey and was able to use it with no training.

I had to chuckle to myself... change the period to a exclamation mark and it
could have come from the opening line of an infomerical <grin>

glad to help

--
Jack Leach
www.tristatemachine.com

- "Success is the ability to go from one failure to another with no loss of
enthusiasm." - Sir Winston Churchill