Select Workbook with 2 Workbooks open

gottimd

Well-known Member
Joined
Jul 29, 2002
Messages
501
At one point in my macro, I will have a workbook called "x" (this workbook name will change often), and another workbook called "y" (name will remain constant).

At a a point in my macro, it only has "x" open, then saves it, and opens up "y". I then want it to close "x" but I can't figure out how to get it to close "x":
Code:
    ActiveWorkbook.Save      (workbook "x")
    Workbooks.Open Filename:="E:\Groups\Hyperion\Acterna\Corp\FY2004\" & Month & "\Entity Pack Template.xls"   (workbook "y")

    ActiveWorkbook.Close   (want this to be Workbook "x", but it closes "y")

When the last part of the code is executed, it closes "y" because that is the active workbook since it was just opened. How do I get it to select workbook "X" and close it. It would be easy if "X" had a constant name but it is always changing. Also, this will be part of a looped code, where "Y" is a template.
 

Some videos you may like

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.

Mudface

MrExcel MVP
Joined
Feb 18, 2002
Messages
3,339
You can do this either by referring explicitly to the workbook itself, or (more useful in your case if the name will change) by using object variables. As an example: -

Code:
Dim wbX As Workbook
Dim wbY As Workbook

Set wbX = ActiveWorkbook
wbX.Save
Set wbY = Workbooks.Open("C:\My Documents\Test")
wbX.Close

By setting the object variables, you can then refer to the workbooks you want to.
 

Richie(UK)

MrExcel MVP
Joined
May 17, 2002
Messages
3,329
Hi g,

Try to get away from referring to the ActiveWorkbook. Instead, use workbook variables for the workbooks that you are referencing.

For example:
Code:
Sub Test()
    Dim x As Workbook, y As Workbook
    
    Set x = Workbooks.Open(FileName:="A filename")
    With x
    'do stuff with x
    '
        .Save
        .Close
    End With
    
    Set y = Workbooks.Open(FileName:="E:\Groups\Hyperion\Acterna\Corp\FY2004\" _
        & Month & "\Entity Pack Template.xls")

End Sub
HTH
 

gottimd

Well-known Member
Joined
Jul 29, 2002
Messages
501
Thank you both!

Richie, if the x workbook is already open, will the

Code:
Set x = Workbooks.Open(FileName:="A filename")


work?
 

gottimd

Well-known Member
Joined
Jul 29, 2002
Messages
501

ADVERTISEMENT

Here comes a massive code, I don't need help on it, unless there are some things that I am doing the long way, I just wanted to know where to put the workbook codes posted in the prior posts:


Code:
    Dim x As Workbook
    Dim y As Workbook
    Dim Month As String
    Dim Entity
    Dim underscore
    Dim Hypname
    Dim Fullnme
    Dim Mnth
    Dim Year
    Dim Consolidation
    Dim Response As Integer
    Dim Snd
    Dim Flder
    Dim dte
    Dim rsp
    Dim ws As Worksheet
    Dim I As Integer

     
    
    Application.DisplayAlerts = False
    Application.ScreenUpdating = False
   
    Set x = Workbooks.Open
   
    Sheets("TITLEPAGE").Activate
    ActiveSheet.Calculate
    Sheets("Data").Activate
    ActiveSheet.Calculate
    Sheets("Companies").Select
    ActiveWindow.ScrollWorkbookTabs Position:=xlLast
    Sheets(Array("Companies", "TITLEPAGE", "Data", "Assets", "Liabilities_Equity", _
        "Income_Statement", "Intercompany", "Inventory", "PPE-MONTH", "Intangibles", _
        "Headcount", "Debt", "Debt Worksheet", "Bonus", "Statistics", "Check")).Select
    Calculate
    
    Mnth = Sheets("TITLEPAGE").Range("a52")
    Year = Sheets("TITLEPAGE").Range("a53")
    Consolidation = Sheets("TITLEPAGE").Range("a54")
    Fldr = Sheets("TITLEPAGE").Range("a58")
    Month = Mnth & " " & Year & " " & Consolidation
    Entity = Sheets("TITLEPAGE").Range("A55")
    underscore = Sheets("TITLEPAGE").Range("A56")
    Hypname = Sheets("TITLEPAGE").Range("A57")
    Snd = Sheets("TITLEPAGE").Range("a58")
    dte = Sheets("TITLEPAGE").Range("a59")
    Fullnme = Entity & underscore & Hypname & Snd & underscore & dte
    
    ActiveWorkbook.Saveas Filename:="E:\Groups\Hyperion\Acterna\Corp\FY2004\" & Month & "\Packs to load\Send\" & Fullnme & ".xls"
    Sheets("TITLEPAGE").Visible = False
        Sheets("Assets").Select
    Range("D13:D124").Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Sheets("Liabilities_Equity").Select
    Range("D12:D93").Select
    Application.CutCopyMode = False
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Sheets("Income_Statement").Select
    Range("D12:D140").Select
    Application.CutCopyMode = False
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Sheets("Statistics").Select
    Range("D17:D20").Select
    Application.CutCopyMode = False
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("D22:D27").Select
    Application.CutCopyMode = False
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("D33").Select
    Range("D33:D35").Select
    Application.CutCopyMode = False
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("D39:D41").Select
    Application.CutCopyMode = False
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    ActiveWindow.ScrollWorkbookTabs Position:=xlFirst
    Sheets("Data").Select
    
    
    rsp = InputBox("Enter Password", "Password Protect", Sheets("Data").Range("B112").Value)

    
    For I = 1 To Worksheets.Count
            Application.ScreenUpdating = False
            Worksheets(I).Protect rsp
            Sheets("Data").Select
            Sheets("data").unprotect rsp
            Rows("111:117").Select
            Selection.FormulaHidden = True
            Selection.EntireRow.Hidden = True
            Selection.Locked = True
            Sheets("data").Protect rsp
                
       Next
       
    Sheets("Inventory").Select
    Sheets("Inventory").unprotect rsp
    Sheets("Inventory").Select
    ActiveSheet.Protect DrawingObjects:=False, Contents:=True, Scenarios:= _
        False, AllowUsingPivotTables:=True
    Sheets("Data").Select
    Range("B2").Select
    
    
    ActiveWorkbook.Save
    Workbooks.Open Filename:="E:\Groups\Hyperion\Acterna\Corp\FY2004\" & Month & "\Entity Pack Template.xls"
  
    
    Application.ScreenUpdating = True
    Application.DisplayAlerts = True
    
End Sub
 

Richie(UK)

MrExcel MVP
Joined
May 17, 2002
Messages
3,329
gottimd said:
Thank you both!

Richie, if the x workbook is already open, will the

Code:
Set x = Workbooks.Open(FileName:="A filename")


work?
Hi, if its already open you should use:
Code:
Set x = Workbooks("x")
How do you know that it is already open? If necessary you could incorporate some sort of check first:
Code:
Sub Test()
    Dim x As Workbook
    
    If IsWorkbookOpen("x") Then
        Set x = Workbooks("x")
    Else
        Set x = Workbooks.Open(FileName:="A filename")
    End If
    
    With x
    'do stuff with x
    '
        .Save
        .Close
    End With

End Sub

Function IsWorkbookOpen(stName As String) As Boolean
    Dim Wkb As Workbook
    On Error Resume Next
    Set Wkb = Workbooks(stName)
    If Not Wkb Is Nothing Then IsWorkbookOpen = True
End Function
HTH
 

gottimd

Well-known Member
Joined
Jul 29, 2002
Messages
501

ADVERTISEMENT

How do you know that it is already open? If necessary you could incorporate some sort of check first:

It has to be open already because the macro is stored in the template.

I'll see if that code works! Thanks!
 

Richie(UK)

MrExcel MVP
Joined
May 17, 2002
Messages
3,329
OK, one more thing. To refer to the workbook that contains the code that you are running use the "ThisWorkbook" object reference.

For example:
Code:
Sub Test()
    MsgBox ThisWorkbook.Name
End Sub
 

gottimd

Well-known Member
Joined
Jul 29, 2002
Messages
501
ok, I mus tbe doing something wrong here, because it reopens and closes the "x" workbook still.


Code:
Dim x As Workbook
    Dim Month As String
    Dim Entity
    Dim underscore
    Dim Hypname
    Dim Fullnme
    Dim Mnth
    Dim Year
    Dim Consolidation
    Dim Response As Integer
    Dim Snd
    Dim Flder
    Dim dte
    Dim rsp
    Dim ws As Worksheet
    Dim I As Integer

     
    
    Application.DisplayAlerts = False
    Application.ScreenUpdating = False
   
    'calculates and refreshes open workbook
    
   
    Sheets("TITLEPAGE").Activate
    ActiveSheet.Calculate
    Sheets("Data").Activate
    ActiveSheet.Calculate
    Sheets("Companies").Select
    ActiveWindow.ScrollWorkbookTabs Position:=xlLast
    Sheets(Array("Companies", "TITLEPAGE", "Data", "Assets", "Liabilities_Equity", _
        "Income_Statement", "Intercompany", "Inventory", "PPE-MONTH", "Intangibles", _
        "Headcount", "Debt", "Debt Worksheet", "Bonus", "Statistics", "Check")).Select
    Calculate
    
    'sets open workbook hyperion name and saves into send file
    
    Mnth = Sheets("TITLEPAGE").Range("a52")
    Year = Sheets("TITLEPAGE").Range("a53")
    Consolidation = Sheets("TITLEPAGE").Range("a54")
    Fldr = Sheets("TITLEPAGE").Range("a58")
    Month = Mnth & " " & Year & " " & Consolidation
    Entity = Sheets("TITLEPAGE").Range("A55")
    underscore = Sheets("TITLEPAGE").Range("A56")
    Hypname = Sheets("TITLEPAGE").Range("A57")
    Snd = Sheets("TITLEPAGE").Range("a58")
    dte = Sheets("TITLEPAGE").Range("a59")
    Fullnme = Entity & underscore & Hypname & Snd & underscore & dte
          
    ActiveWorkbook.Saveas Filename:="E:\Groups\Hyperion\Acterna\Corp\FY2004\" & Month & "\Packs to load\Send\" & Fullnme & ".xls"
    Set x = Workbooks.Open(Filename:="E:\Groups\Hyperion\Acterna\Corp\FY2004\" & Month & "\Packs to load\Send\" & Fullnme & ".xls")
    
    'Formats and copy and paste values so enduser can see values instead of Hyperion links
    
    
    Sheets("TITLEPAGE").Visible = False
        Sheets("Assets").Select
    Range("D13:D124").Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Sheets("Liabilities_Equity").Select
    Range("D12:D93").Select
    Application.CutCopyMode = False
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Sheets("Income_Statement").Select
    Range("D12:D140").Select
    Application.CutCopyMode = False
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Sheets("Statistics").Select
    Range("D17:D20").Select
    Application.CutCopyMode = False
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("D22:D27").Select
    Application.CutCopyMode = False
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("D33").Select
    Range("D33:D35").Select
    Application.CutCopyMode = False
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("D39:D41").Select
    Application.CutCopyMode = False
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    ActiveWindow.ScrollWorkbookTabs Position:=xlFirst
    Sheets("Data").Select
    
    
    'Protects all sheets so user cannot alter
    
    rsp = InputBox("Enter Password", "Password Protect", Sheets("Data").Range("B112").Value)

    
    For I = 1 To Worksheets.Count
            Application.ScreenUpdating = False
            Worksheets(I).Protect rsp
            Sheets("Data").Select
            Sheets("data").unprotect rsp
            Rows("111:117").Select
            Selection.FormulaHidden = True
            Selection.EntireRow.Hidden = True
            Selection.Locked = True
            Sheets("data").Protect rsp
                
       Next
       
    'formats inventory page so end user can enter data
    
    Sheets("Inventory").Select
    Sheets("Inventory").unprotect rsp
    Sheets("Inventory").Select
    ActiveSheet.Protect DrawingObjects:=False, Contents:=True, Scenarios:= _
        False, AllowUsingPivotTables:=True
    Sheets("Data").Select
    Range("B2").Select
        
    'Saves active workbook to be sent and opens up template to start for new entity and closes finished template
        
    ActiveWorkbook.Save
    Workbooks.Open Filename:="E:\Groups\Hyperion\Acterna\Corp\FY2004\" & Month & "\Entity Pack Template.xls"
    Workbooks.Close x
    
    
    Application.ScreenUpdating = True
    Application.DisplayAlerts = True
    
End Sub
 

gottimd

Well-known Member
Joined
Jul 29, 2002
Messages
501
When I try to Set X I have used

Set x= Workbooks(Fullnme)
Set x= Workbooks("Fullnme")
Set x= Workbooks(Filename:="E:\Groups\Hyperion\Acterna\Corp\FY2004\" & Month & "\Packs to load\Send\" & Fullnme & ".xls")

and nothing seems to work.

Suggestion?
 

Watch MrExcel Video

Forum statistics

Threads
1,122,567
Messages
5,596,908
Members
414,110
Latest member
docops

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top