Code behind excel form help

[AccessShell]

NOTE: Originally posed elsewhere. I do not believe I will get an answer.
I hope I can post it here.

I have written some code in Sub Workbook_Open. It works fine. However, I am trying to do two more things.
1. I don't know if I should use Workbook_BeforeClose, Workbook_Deactivate, or Workbook_BeforeSave. I am trying to Shell to a VB6 program and exit the worksheet. All three subroutines activate the three sub-routines mentioned above.
Here is an example of what I tried
"
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim MD as String
MD = Shell("C:\My VB6 Executables\Data Display\DataDisplay.exe", 1)
end
End Sub
"

In addition, when I tried two of the three (BeforeClose and Deactivate), the shell command just kept repeating until I ran out of memory. Or, maybe the routine kept repeating

2. In the Workbook_open routine I need to place some code to gracefully close the VB6 program that I shelled to in the close of this Excel spreadsheet.

Thanks

 

[Fluff]

NOTE: Originally posed elsewhere. I do not believe I will get an answer.
I hope I can post it here.

Posting the question here is fine, all we ask is that you supply a link to any other sites where you have asked this question.
Thanks

 

[AccessShell]

I originally posted this thread on www.excelforum.com.

Since I wrote the post I figured out the conceptual problem. I now have to put into practice. I was sleeping and it struck me what I did.
The Workbook_BeforeClose routine is the place for the extra code. My problem was that the VB6 code I was shelling to had to read the data in the excel spreadsheet where the shell statement was written. As a result I caused and infinite loop. Programming 101 - I should have known better.
Now using the shell statement, I will pass the data I need to the VB6 program si I don't have to access the excel spreadsheet from VB6. I know how to pass the data from excel. I have to learn how to get the data in the VB6 program. I think that is a question for the VBV6 forum.

The other question about gracefully closing the VB6 program from Excel is still unanswered.

Thanks

 

[AccessShell]

I have resolved all issues related to this post.

To terminate an VB6 executable from Excel VBA, the following code works great

"
Sub Test()<o></o>If TaskKill("YourExecutable.exe")=0Then MsgBox "Terminated"Else MsgBox "Failed"<o></o>EndSub<o></o><o> </o>Function TaskKill(sTaskName)<o></o> TaskKill = CreateObject("WScript.Shell").Run("taskkill /f /im "& sTaskName,0,True)<o></o>EndFunction<o></o>"

For me, the easiest way to pass the data to the VB6 program was to write a file from the VBA code behind, then read it when opening the VB6 program.

Thanks

Now, if only I can figure out how mark this thread solved!

 

[Fluff]

Thanks for letting us now that it's sorted.
We don't mark threads as solved here.