Select Workbook with 2 Workbooks open

G

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.
 

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.
Sort by date Sort by votes
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.
 
Upvote 0
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
 
Upvote 0
Thank you both!

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

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


work?
 
Upvote 0
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
 
Upvote 0
gottimd said:
Thank you both!

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

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


work?
Click to expand...
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
 
Upvote 0
How do you know that it is already open? If necessary you could incorporate some sort of check first:
Click to expand...

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

I'll see if that code works! Thanks!
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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?
 
Upvote 0
You must log in or register to reply here.

Similar threads

D
  • Question
Form Control to open a window or form to add a list of numbers, perform a calculation and paste value to a selected cell.
Replies
0
Views
81
DaveCutting
D
A
  • Question
Creating a workbook from a Tracker spreadsheet that links back to cells within the Tracker spreadsheet
Replies
2
Views
163
AndyEd
A
G
  • Question
Open workbooks in folder and run Macro in each book
Replies
7
Views
305
glynn1969
G
G
  • Question
Select an Open workbook and apply a macro1 contained in the new book to the new book
Replies
1
Views
109
LazyBug
LazyBug
J
  • Question
VBA Script - Can't figure out one error...
2
Replies
15
Views
194
Peter_SSs
Peter_SSs

Forum statistics

Threads
1,214,432
Messages
6,119,468
Members
448,900
Latest member
Fairooza

Share this page

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
Back
Top