Uh? What happened to Mason <vbg?

Bob

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Steph" wrote in message
...
Much obliged. I really appreciate the help.

"Bob Phillips" wrote in message
...
Mason,

Sorry, I was mis-reading your question :-).

Yes, the two functions can go in the same module as the Workbook_Open,
the
ThisWorkbook module.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Mason" wrote in message
...
Gotcha. But the 2 functions?

"Bob Phillips" wrote in message
...
Oh no, the set environment variable must be in the other workbook,
before
you execute the code to open workbook B.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Mason" wrote in message
...
Thank you. Is it safe to assume that the 2 functions also belong
in
the
'workbook ' code section along with the open event?

"Bob Phillips" wrote in
message
...
Mason,

You could set an environment variable before closing the first,
and
get
it
in the second. Here's a demo

Book1

Private Declare Function SetEnvironmentVariable Lib "kernel32" _
Alias "SetEnvironmentVariableA" _
(ByVal lpName As String, _
ByVal lpValue As String) As Long


Sub xx()

SetEnvironmentVariable "BookName", Thisworkbook.Name


Book2

Private Sub Workbook_Open()
Workbooks.Close(GetEnvironmentVar("BookName")
End Sub

Function GetEnvironmentVar(Name As String) As String
GetEnvironmentVar = String(255, 0)
GetEnvironmentVariable Name, GetEnvironmentVar,
Len(GetEnvironmentVar)
GetEnvironmentVar = TrimNull(GetEnvironmentVar)
End Function

Private Function TrimNull(item As String)
Dim iPos As Long
iPos = InStr(item, vbNullChar)
TrimNull = IIf(iPos 0, Left$(item, iPos - 1), item)
End Function






--

HTH

RP
(remove nothere from the email address if mailing direct)


"Mason" wrote in message
...
I have code that opens an excel file. Can I have an Open
event
on
the
new
opened Excel file that closes the original file?

So workbookA code opens workbookB. The open event in
workbookB
closes
workbookA.
But I won't know the name of workbookA, and other workbooks
may
be
open
also
and we don't want them closed.