Set environment variable on closing file

  • Thread starter Thread starter Vincent Fatica
  • Start date Start date
V

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.
 
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.
Click to expand...

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

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.
 
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
 
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
Click to expand...

Can I pass to that program cell values as command line parameters? ... how?
 
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."
Click to expand...

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


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
 
Sure:
Shell ("cmd.exe /k dir C:\")
Click to expand...

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?
 
I don't see any cell values there.
Click to expand...

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?
Click to expand...

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