Code behind excel form help

AccessShell

New Member
Joined
Sep 5, 2015
Messages
22
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
 

Some videos you may like

Excel Facts

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
40,791
Office Version
365
Platform
Windows
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

New Member
Joined
Sep 5, 2015
Messages
22
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

New Member
Joined
Sep 5, 2015
Messages
22
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:p></o:p>If TaskKill("YourExecutable.exe")=0Then MsgBox "Terminated"Else MsgBox "Failed"<o:p></o:p>EndSub<o:p></o:p><o:p> </o:p>Function TaskKill(sTaskName)<o:p></o:p> TaskKill = CreateObject("WScript.Shell").Run("taskkill /f /im "& sTaskName,0,True)<o:p></o:p>EndFunction<o:p></o:p>"

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!
 
Last edited:

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
40,791
Office Version
365
Platform
Windows
Thanks for letting us now that it's sorted.
We don't mark threads as solved here.
 

Watch MrExcel Video

Forum statistics

Threads
1,099,254
Messages
5,467,565
Members
406,543
Latest member
semoredhawk

This Week's Hot Topics

Top