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.

 

[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.