Close another workbook upon closing the workbook

Question

I have a code that opens another workbook (source.xlsx) when I open (triggers on Workbook_Open event) a template workbook (template.xlsm).

The code:

Private Sub Workbook_Open()
 Application.Screenupdating= False
 Set w = workbooks
 w.open filename:="link", Updatelinks:=true , readonly:=true
 activewindow.visible=false
 thisworkbook.activate
 application.screenupdating=True
end sub

However, I want the source workbook to just run on background upon opening and close it when I close the template file.

Private sub workbook_aftersave()
 Workbook("source.xlsx").Close SaveChanges:=False
End Sub

You can try what was done [here](https://stackoverflow.com/a/41058450/2685412). — L42, Aug 23 '18 at 07:31
You might want to clarify the question slightly - you have had both a comment and an answer about *hiding* the workbook, which your code should already do, instead of about *closing* it which is where I think your error actually is - perhaps you could add a paragraph on "This is what I expect/want, this is what I actually get"? — Chronocidal, Aug 23 '18 at 08:53

Chronocidal · Answer 1 · 2018-08-23T08:50:32.247

You want to use the Workbooks collection (Workbooks("source.xlsx")), rather than a Workbook object (Workbook("source.xlsx"), which will throw an error). Also, rather than trying to close it on the Workbook_AfterSave event, you could try using the Workbook_BeforeClose event:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    On Error Resume Next 'In case the Workbook is already closed
    Workbooks("source.xlsx").Close SaveChanges:=False
End Sub

{EDIT} And, because I can, here's a tidier version of your Workbook_Open code too:

Private Sub Workbook_Open()
    Application.ScreenUpdating = False
    Dim wsSource As Workbook
    Set wsSource = Workbooks.Open(Filename:="SomeDirectory\source.xlsx", UpdateLinks:=True, ReadOnly:=True) 'Change the filename to where your "source.xlsx" is stored
    DoEvents 'Wait for it to finish opening
    wsSource.Windows(1).Visible = False
    ThisWorkbook.Activate
    Application.ScreenUpdating = True
End Sub

score -1 · Answer 2 · answered Aug 23 '18 at 08:22

-1

Try using

Application.Visible = False

Place this in your Workbook_Open code on the workbook you want to hide, and then use True to bring it back to close.

answered Aug 23 '18 at 08:22

RazorKillBen

561
2
20

Close another workbook upon closing the workbook

2 Answers2