Code to close workbook
 

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.

Code to close workbook
 

Hi Mason

You can close workbookA in the same macro that open workbookAB

After you open workbookB use this to close workbookA
ThisWorkbook.Close False 'not save

or
ThisWorkbook.Close False 'save the file



--
Regards Ron de Bruin
http://www.rondebruin.nl



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

Code to close workbook
 

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.

Code to close workbook
 

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.

Code to close workbook
 

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.

Code to close workbook
 

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.

Code to close workbook
 

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.

Code to close workbook
 

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.

Code to close workbook
 

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.

Code to close workbook
 

Hi Bob. Co-worker...viewed you rmessage and replied from my laptop during a
meeting! Sorry for the confusion!

-Steph

"Bob Phillips" wrote in message
...
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.

Code to close workbook
 

No confusion Steph, just had a double take as you have also been posting :-)

Bob


"Steph" wrote in message
...
Hi Bob. Co-worker...viewed you rmessage and replied from my laptop during
a
meeting! Sorry for the confusion!

-Steph

"Bob Phillips" wrote in message
...
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.