Run program on save/close

[Vincent Fatica]

I wrote a VC EXE to set environment variables (HKEY_CURRENT_USER\Environment).
I'd like to call that app when closing/saving (either would be OK) a particular
Excel file, supplying, as command line parameters, the values in two (formulaic)
cells. Can a script/macro be associated with closing/saving a document and can
I do that with a script/macro? I'm no good at VB so some code would be
appreciated. Thanks.

 

[joel]

I assume you wrote the VC program that excepts two aruguents arg(1) and
arg(2) with arg(0) being the file name. You would use a shell method in
VBA. the sheell method will not search the PATH envirnoment variable (
I can modify the code to search the path easily) so yo must include the
path name of your VC EXE


Shell("c:\temp\MyVC arg1 arg2")

to get the parameters into the command do this

arg1 = range("A1")
arg2 = range("B1")
Shell("c:\temp\MyVC " & arg1 & " " & arg2)
Notice I put two spaces in the line above (one after MYVC)


Now you just need to use a Close Workbook macro to run the code.


Private Sub Workbook_BeforeClose(Cancel as Boolean)
with Sheets("Sheet1")
arg1 = .range("A1")
arg2 = .range("B1")
end with
Shell("c:\temp\MyVC " & arg1 & " " & arg2)
End Sub

 

[Vincent Fatica]

That sounds perfect. But I have never programmed for Excel (or in VB at all).
Please be specific on where these things go and whether they require any
"wrapping". Thanks.

 

[Vincent Fatica]

Following your example and the instructions in
http://support.microsoft.com/kb/213639 I put the following code into
"ThisWorkbook".

Private Sub Workbook_BeforeClose(Cancel As Boolean)
With Sheets("Sheet1")
arg1 = .Range("D5")
arg2 = .Range("E5")
End With
Shell ("d:\uty\setslope.exe " & arg1 & " " & arg2)
End Sub

When I close the file, nothing happens. Mt EXE doesn't run (I'd hear a beep if
it did) and there are no error messages. I'm in completely new territory here.
Please help further. Thanks.

 

[Vincent Fatica]

Following your example and the instructions in
http://support.microsoft.com/kb/213639 I put the following code into
"ThisWorkbook".

Private Sub Workbook_BeforeClose(Cancel As Boolean)
With Sheets("Sheet1")
arg1 = .Range("D5")
arg2 = .Range("E5")
End With
Shell ("d:\uty\setslope.exe " & arg1 & " " & arg2)
End Sub

When I close the file, nothing happens. Mt EXE doesn't run (I'd hear a beep if
it did) and there are no error messages. I'm in completely new territory here.
Please help further. Thanks.

OK. Thanks. I got it working.

I had to (1) allow macros (what a pain!) and (2) change "Sheet1" (above) to the
actual given name ("2009-0") of the worksheet.

In the VB environment, I see, in the project pane, "Sheet1 (2009-0)". It is not
actually Sheet1. Is this expected? If I have given names to sheets, I must use
those names ... right?

Thanks again.