Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
VBA Code to kick off macro when workbook command to close is initi | Excel Discussion (Misc queries) | |||
close form code | Excel Discussion (Misc queries) | |||
Close a the current workbook and load another specified workbook | Excel Programming | |||
Close VB in Code | Excel Programming | |||
very difficult code that will close original workbook and leave another open | Excel Programming |