Code to close workbook

[Mason]

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.

 

[Ron de Bruin]

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

 

[Bob Phillips]

Mason,

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

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


Sub xx()

SetEnvironmentVariable "BookName", Thisworkbook.Name


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]

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]

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]

Gotcha. But the 2 functions?

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)


get

 

[Bob Phillips]

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)

 

[Steph]

Much obliged. I really appreciate the help.

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)

 

[Bob Phillips]

Uh? What happened to Mason <vbg>?

Bob

--

HTH

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

 

[Steph]

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

-Steph

 

[Bob Phillips]

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

Bob