Working within another app via vba

[mtairhead]

I know that you can use vba to open another application. (I don't know how, but I know it can be done - I've seen it)

So... Is it then possible to use vba to work within that application? I'm trying to have vba open Excel and then execute a macro... two macros, actually.

Is this possible?

Andrew

 

[Pat Hartman]

Yes it is. If you search here you may find some examples. Your best bet though is the MSDN library. It has several excellant articles on automation. You also need to find help regarding automation in the help file of the application you are trying to automate. So, if you want to automate Excel, look in Excel help.

 

[mtairhead]

Success! Thanks Pat!

For anyone who has this issue later down the road, here's what I did:
Since I don't have the time to learn the VBA needed to do this, and then troubleshoot it until it works, I took a cheaters way out... Take a look at the advice given Simplycongy in
this thread. (I must admit that I did not search for a solution before posting this problem - Hence, the post identical to mine. Usually I do, but I assumed it would be a rare occurance)

Based in that idea, I used this article to learn where I needed to insert the VBA code from my previously recorded Excel macro, in order to get it to run automatically when I opened the workbook. (I used the "Creating a VBA procedure for the Open event of workbook" part of the article.)

At this point, your problems should be solved. Just open the recorded macro in VB, and copy the code into the "Private Sub Workbook_Open()" procedure. Then have your VBA open that workbook.

This didn't end it for me, however. I'm not sure why (And I didn't want to waste time figuring it out) but I got an "out of range" error when the VBA code went to active a worksheet. (The sheet was opened...so no mistake there.) The macro worked fine as a normal macro, but as soon as I put the code into the "Private Sub Workbook_Open()" procedure it would get the error.

This issue was solved by placing only one line of code into the "Private Sub Workbook_Open()" procedure, instead of the entire macro's code:
Application.Run ("macro_name_here")

So, my entire code looks as such:

Private Sub Workbook_Open()
Application.Run ("update_stocks")
End Sub

Now, just use the following VBA script..

Function functionname()
Application.FollowHyperlink "C:\Path_To_File\excel_file_here.xls"
End Function

Thanks to Pat and every author of the threads I used...

Andrew