Set environment variable on closing file

[Vincent Fatica]

I'd like to set a global environment variable (in HKEY_CURRENT_USER\Environment)
upon closing a sprcific file. So (1) is there a way to associate an action
(macro?) with closing a file, and (2) will someone please help with the VB
script necessary to do it. Thanks.

 

[Vincent Fatica]

I'd like to set a global environment variable (in HKEY_CURRENT_USER\Environment)
upon closing a sprcific file. So (1) is there a way to associate an action
(macro?) with closing a file, and (2) will someone please help with the VB
script necessary to do it. Thanks.

P.S. The data for the registry entry will come from a worksheet cell. That cell
contains a formula, but I want the value.

 

[Vincent Fatica]

P.S. The data for the registry entry will come from a worksheet cell. That cell
contains a formula, but I want the value.

I've changed the problem a little by writing a VC EXE to set the registry values
and announce the changes to the system. So now I need to associate with closing
(saving) the file with the action of calling my EXE and providing, as command
line parameters, the values in a couple of worksheet cells. Any help? Thanks.

 

[Dana DeLouis]

I'd like to set a global environment variable
I know it's not what you want, but would the SaveSetting statement work
for you as well?

SaveSetting Statement

"Saves or creates an application entry in the application's entry in the
Windows registry or (on the Macintosh) information in the application's
initialization file."

= = = = = = =
Dana DeLouis

 

[Vincent Fatica]

To run a program before closing a Excel workbook, insert this code into
ThisWorkbook:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Shell ("CALC.EXE")
End Sub

Can I pass to that program cell values as command line parameters? ... how?

 

[Vincent Fatica]

I know it's not what you want, but would the SaveSetting statement work
for you as well?

SaveSetting Statement

"Saves or creates an application entry in the application's entry in the
Windows registry or (on the Macintosh) information in the application's
initialization file."

I could certainly make that work if the saved settings (two of them) can be the
current values in cells (containing formulas) ... possible?

 

[Dana DeLouis]

I could certainly make that work if the saved settings (two of them)
can be the

current values in cells (containing formulas) ... possible?

Hi. Here's something quick n dirty.
When the workbook is closed, it writes data to the Registry, and clear
the data.
When the workbook is opened, the data is put in C1 & C2. Maybe
something here will give you some ideas.


Private Sub Workbook_BeforeClose(Cancel As Boolean)
'// Save A1 & A2
SaveSetting "MainApp", "Records", "First", [A1]
SaveSetting "MainApp", "Records", "Second", [A2]
[A1:A2].Clear
End Sub

Private Sub Workbook_Open()
'// Put Reg. Stuff in C1 & C2
Const strDefault As String = "xxx"
[C1] = GetSetting("MainApp", "Records", "First", strDefault)
[C2] = GetSetting("MainApp", "Records", "Second", strDefault)
End Sub

= = = = = = = = = = = =
HTH :>)
Dana DeLouis

 

[Vincent Fatica]

Sure:
Shell ("cmd.exe /k dir C:\")

I don't see any cell values there.

Anyway, I accomplished my goal with a little help from Joel in the Excel
programming newsgroup. Basically, ThisWorkbook\BeforeClose,

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

Allowing macro execution (and figuring out that I needed to do so) was a pain! I
had to allow running **all** macros, and that setting goes beyong the Excel file
in question. Is there no middle ground here? Can't I say it's OK to run **my**
macros ... somehow sign my own macros?

 

[Michael Bednarek]

I don't see any cell values there.

I thought it would be obvious from here.

Anyway, I accomplished my goal with a little help from Joel in the Excel
programming newsgroup. Basically, ThisWorkbook\BeforeClose,

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

Allowing macro execution (and figuring out that I needed to do so) was a pain! I
had to allow running **all** macros, and that setting goes beyong the Excel file
in question. Is there no middle ground here? Can't I say it's OK to run **my**
macros ... somehow sign my own macros?

Indeed: run selfcert.exe, usually found in %ProgramFiles%\Microsoft
Office\OFFICEnn\. Or see http://support.microsoft.com/kb/217221 .