Open 2 worksheets; closing first spreadsheet

[keycalinc]

I have created an Excel spreadsheet, sample5a.xlsm with an Worksheet_Open subroutine. This subroutine opens another Excel file.The macro does open the 2nd file, sample5b.xlsm. I would like to close the first file without saving it. I cannot formulate a macro to close this file. Both files stay open.I have tried the macros shown below. I have identified the excel files and macros below.



SAMPLE5A MACROS


ThisWorkbook - 1

Sub Workbook_Open()

Callsecondfile
' Workbooks(1).Close didn't work
' Windows("sample5a.xlsm").Activate
' Windows("sample5b.xlsm").Select
' Windows("sample5a.xlsm").Close didn't work
' Application.Workbooks(1).Close
' ActiveWorkbook.Close
End Sub


Module1 - 1

Sub Callsecondfile()

Dim secondfilename As String

secondfilename = "c:\users\keyca\onedrive\documents\Microsoft Excel\sample5b.xlsm"
MsgBox secondfilename
Workbooks.Open secondfilename
MsgBox "if program displays this message, this macro is still working after opening sample5b"
' Application.Workbooks(1).Close

trialmacro

End Sub

Module2 - 1

Sub trialmacro()

'
' trialmacro Macro
'
'
Windows("SAMPLE5B.xlsm").Activate
Windows("SAMPLE5A.xlsm").Activate
Windows("SAMPLE5B.xlsm").Activate
' ActiveWorkbook.Close
Workbooks("sample5a.xlsm").Close

End Sub

SAMPLE5B MACROS

ThisWorkbook - 1

Sub Workbook_Open()

workonsample5a
MsgBox "workonsample5a should be complete"

End Sub

Module1 - 1

Sub workonsample5a()

Application.Workbooks(1).Activate
Application.Workbooks(1).Close
MsgBox "if sample5b macro gets to here, sample5a workbook should close"

End Sub

 

[RobP]

Hi, do you mean you want to close your original file which has your macro running inside it ?

I don't believe thats possible - as you are still executing your macro from it. If it closed on itself, how would the macro continue ... ?

Maybe others have more knowledge on that.
Rob

 

[keycalinc]

Hi, do you mean you want to close your original file which has your macro running inside it ?

I don't believe thats possible - as you are still executing your macro from it. If it closed on itself, how would the macro continue ... ?

Maybe others have more knowledge on that.
Rob

Thanks, Rob. Good point.

I have revised my request as shown below. Here is the sequence that I would like to accompish:

1.

 

[keycalinc]

Thanks, Rob. Good point.

I have revised my request as shown below. Here is the sequence that I would like to accompish:

1. Start "sample6a.xlsm" program. Workbook_Open macro runs the "DataCopy" subroutine.

This DataCopy subroutine correctly opens a second "sample6b.xlsm" spreadsheet
2. The sample6a program continues with the "CallAnotherMacro" subroutine

This subroutine runs the "closesample6a" macro in sample6b.xlsm spreadsheet

3.

 

[keycalinc]

Hi, do you mean you want to close your original file which has your macro running inside it ?

I don't believe thats possible - as you are still executing your macro from it. If it closed on itself, how would the macro continue ... ?

Maybe others have more knowledge on that.
Rob

I have revised my request as shown below. Here is the sequence that I would like to accomplish:

1. Start "sample6a.xlsm" program. Workbook_Open macro runs the "DataCopy" subroutine.

This DataCopy subroutine correctly opens a second "sample6b.xlsm" spreadsheet

2. The sample6a program continues with the "CallAnotherMacro" subroutine

This subroutine runs the "closesample6a" macro in sample6b.xlsm spreadsheet

3. What I would like to end up at the end of this process is only sample6b to be shown on the screen. sample6a should be closed. Right now, both files are still open.

Thanks for your input, Rob.

ThisWorkbook - 1 (sample6a.xlsm)

Sub Workbook_Open()

DataCopy
CallAnotherMacro
End Sub

Module2 - 1 (sample6a.xlsm)


Sub DataCopy()
Dim filePath As String
Dim mR, cR As Range
Dim masterBook, otherBook As Workbook
'Setup your 2 workbooks
Set masterBook = ActiveWorkbook
MsgBox masterBook.Name
Set otherBook = Workbooks.Open("c:\users\keyca\onedrive\documents\Microsoft Excel\sample6b.xlsm")
'Close your otherbook without saving anything
masterBook.Close False
DoEvents
End Sub

Module3 - 1 (sample6a.xlsm)

Sub CallAnotherMacro()
DoEvents
Application.Run "sample6b.xlsm!closesample6a"
DoEvents
End Sub

Module1 - 1 (sample6b.xlsm)

Sub closesample6a()
DoEvents
MsgBox "running closesample6a macro"
Workbooks("sample6a.xlsm").Close
DoEvents
End Sub

 

[keycalinc]

I have created an Excel spreadsheet, sample5a.xlsm with an Worksheet_Open subroutine. This subroutine opens another Excel file.The macro does open the 2nd file, sample5b.xlsm. I would like to close the first file without saving it. I cannot formulate a macro to close this file. Both files stay open.I have tried the macros shown below. I have identified the excel files and macros below.



SAMPLE5A MACROS


ThisWorkbook - 1

Sub Workbook_Open()

Callsecondfile
' Workbooks(1).Close didn't work
' Windows("sample5a.xlsm").Activate
' Windows("sample5b.xlsm").Select
' Windows("sample5a.xlsm").Close didn't work
' Application.Workbooks(1).Close
' ActiveWorkbook.Close
End Sub


Module1 - 1

Sub Callsecondfile()

Dim secondfilename As String

secondfilename = "c:\users\keyca\onedrive\documents\Microsoft Excel\sample5b.xlsm"
MsgBox secondfilename
Workbooks.Open secondfilename
MsgBox "if program displays this message, this macro is still working after opening sample5b"
' Application.Workbooks(1).Close

trialmacro

End Sub

Module2 - 1

Sub trialmacro()

'
' trialmacro Macro
'
'
Windows("SAMPLE5B.xlsm").Activate
Windows("SAMPLE5A.xlsm").Activate
Windows("SAMPLE5B.xlsm").Activate
' ActiveWorkbook.Close
Workbooks("sample5a.xlsm").Close

End Sub

SAMPLE5B MACROS

ThisWorkbook - 1

Sub Workbook_Open()

workonsample5a
MsgBox "workonsample5a should be complete"

End Sub

Module1 - 1

Sub workonsample5a()

Application.Workbooks(1).Activate
Application.Workbooks(1).Close
MsgBox "if sample5b macro gets to here, sample5a workbook should close"

End Sub

I have created an Excel spreadsheet, sample5a.xlsm with an Worksheet_Open subroutine. This subroutine opens another Excel file.The macro does open the 2nd file, sample5b.xlsm. I would like to close the first file without saving it. I cannot formulate a macro to close this file. Both files stay open.I have tried the macros shown below. I have identified the excel files and macros below.



SAMPLE5A MACROS


ThisWorkbook - 1

Sub Workbook_Open()

Callsecondfile
' Workbooks(1).Close didn't work
' Windows("sample5a.xlsm").Activate
' Windows("sample5b.xlsm").Select
' Windows("sample5a.xlsm").Close didn't work
' Application.Workbooks(1).Close
' ActiveWorkbook.Close
End Sub


Module1 - 1

Sub Callsecondfile()

Dim secondfilename As String

secondfilename = "c:\users\keyca\onedrive\documents\Microsoft Excel\sample5b.xlsm"
MsgBox secondfilename
Workbooks.Open secondfilename
MsgBox "if program displays this message, this macro is still working after opening sample5b"
' Application.Workbooks(1).Close

trialmacro

End Sub

Module2 - 1

Sub trialmacro()

'
' trialmacro Macro
'
'
Windows("SAMPLE5B.xlsm").Activate
Windows("SAMPLE5A.xlsm").Activate
Windows("SAMPLE5B.xlsm").Activate
' ActiveWorkbook.Close
Workbooks("sample5a.xlsm").Close

End Sub

SAMPLE5B MACROS

ThisWorkbook - 1

Sub Workbook_Open()

workonsample5a
MsgBox "workonsample5a should be complete"

End Sub

Module1 - 1

Sub workonsample5a()

Application.Workbooks(1).Activate
Application.Workbooks(1).Close
MsgBox "if sample5b macro gets to here, sample5a workbook should close"

End Sub

After researching the internet for possible solutions to my problem, I found an excel macro on the internet: "How to have vba execute every 10 minutes". Using this routine as a Module in sample5a.xlsm, I as able to:

A. Open sample5b.xlsm after second #1
B. Close sample5a.xlsm after second #2

I also added a routine that checks the type of Laptop or Desktop display and displays 1 of 2 sample5b Excel files as needed. Here is the following macros:

Sample5a Module
Global icount As Integer
Sub my_Procedure()
Application.ScreenUpdating = False
If icount <> 0 Then
GoTo cont1
Else
icount = 0
End If

cont1:
If icount = 1 Then
Call test1_
If fGetChassis = "Desktop" Then
Workbooks.Open ("c:\users\keyca\onedrive\documents\microsoft Excel\BINGO98GOODDESKTOP.xlsm
")
Else
Workbooks.Open ("c:\users\keyca\onedrive\documents\microsoft Excel\BINGO98GOODLAPTOP.xlsm"
)
End If
End If
If icount = 2 Then
Workbooks("sample7a.xlsm").Close
Application.ScreenUpdating = True
End If
' MsgBox "hello world " & icount
Call test ' for starting timer again
End Sub
Sub test()
icount = icount + 1
Application.OnTime Now + TimeValue("00:00:01"), "my_Procedure"
End Sub
Sub test1_()
' strComputerType = fGetChassis()
' MsgBox "This Computer is a " & fGetChassis
End Sub
Function fGetChassis()
Dim objWMIService, colChassis, objChassis, strChassisType
Set objWMIService = GetObject("winmgmts:\\.\root\cimv2")
Set colChassis = objWMIService.ExecQuery("Select * from Win32_SystemEnclosure")
For Each objChassis In colChassis
For Each strChassisType In objChassis.ChassisTypes
Select Case strChassisType
Case 8
fGetChassis = "Laptop" '#Portable
Case 9
fGetChassis = "Laptop" '#Laptop
Case 10
fGetChassis = "Laptop" '#Notebook
Case 11
fGetChassis = "Laptop" '#Hand Held
Case 12
fGetChassis = "Laptop" '#Docking Station
Case 14
fGetChassis = "Laptop" '#Sub Notebook
Case 18
fGetChassis = "Laptop" '#Expansion Chassis
Case 21
fGetChassis = "Laptop" '#Peripheral Chassis
Case Else
fGetChassis = "Desktop"
End Select
Next
Next
End Function
ThisWorkbook - 1
Sub Workbook_Open()
my_Procedure
End Sub

""

 

[Ludwig]

If you store the name of the first workbook in a global variable, you should be able to reference that later in the macros and use

Workbooks(StoredVariable).Close SaveChanges:=False

Not sure if this will need to be issued from a macro in that first workbook or not.

If it is in a macro within the first workbook, then you could also use

ThisWorkbook.close false

 

[keycalinc]

If you store the name of the first workbook in a global variable, you should be able to reference that later in the macros and use

Workbooks(StoredVariable).Close SaveChanges:=False

Not sure if this will need to be issued from a macro in that first workbook or not.

If it is in a macro within the first workbook, then you could also use

ThisWorkbook.close false

 

[keycalinc]

I found a solution to my original problem on the internet. The question asked was to run a macro every 10 minutes. I used this solution and performed an operation every 1 second. After starting sample7a.xlsm, the program, via Workbook_open runs the procedure. When the program iterates to 1 second, it loads sample7b.xlsm. On second 2, it closes sample7a.xlsm leaving only sample7b.xlsm running. The following macros are installed into ThisWorkbook and a Module:

ThisWorkbook

Sub Workbook_Open ()
my_Procedure
End Sub

Module 1

Global icount As Integer
_____________________________________________________________________________________
Sub my_Procedure()
Application.ScreenUpdating = False
If icount <> 0 Then
GoTo cont1
Else
icount = 0
End If
cont1:
If icount = 1 Then
Call test1_
Workbooks.Open ("c:\users\keyca\onedrive\documents\microsoft Excel\sample7b.xlsm")
End If

If icount = 2 Then
Workbooks("sample7a.xlsm").Close SaveChanges:=False
Application.ScreenUpdating = True
End If
Call test ' for starting timer again

End Sub
________________________________________________________________________________________
Sub test()
icount = icount + 1
Application.OnTime Now + TimeValue("00:00:01"), "my_Procedure"
End Sub

I consider this issue closed.