Run program on save/close
 

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

Run program on save/close
 

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


--
joel
------------------------------------------------------------------------
joel's Profile: 229
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=165210

Microsoft Office Help

Run program on save/close
 

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.


On Sun, 27 Dec 2009 13:42:13 +0000, joel wrote:

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

Run program on save/close
 

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.

--
- Vince

Run program on save/close
 

On Sun, 27 Dec 2009 23:15:22 -0500, Vincent Fatica <abuse@localhost wrote:

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