If workbook2 is open, close it

[LSM1604]

Hi all,

Upon opening Workbook1, I would like to check if Workbook2 is open. If Workbook2 is open, I would like it to close Workbook2.

Could someone tell me how to do this please?

I know it is going to be a simple "if" procedure but I can;t think how to do it.

Thanks
LSM

 

[Domenic]

In your first workbook (Workbook1), place the following code in a regular module (Insert > Module)...

[font=Verdana][color=darkblue]Option[/color] [color=darkblue]Explicit[/color]

[color=darkblue]Private[/color] [color=darkblue]Function[/color] WorkbookIsOpen(wbname) [color=darkblue]As[/color] [color=darkblue]Boolean[/color]
    [color=darkblue]Dim[/color] x [color=darkblue]As[/color] Workbook
    [color=darkblue]On[/color] [color=darkblue]Error[/color] [color=darkblue]Resume[/color] [color=darkblue]Next[/color]
    [color=darkblue]Set[/color] x = Workbooks(wbname)
    [color=darkblue]If[/color] Err.Number = 0 [color=darkblue]Then[/color]
        WorkbookIsOpen = [color=darkblue]True[/color]
    [color=darkblue]Else[/color]
        WorkbookIsOpen = [color=darkblue]False[/color]
    [color=darkblue]End[/color] [color=darkblue]If[/color]
End [color=darkblue]Function[/color]
[/font]

Again in your first workbook, place the following code in the workbook module (double-click on ThisWorkbook in the project explorer window)...

[font=Verdana][color=darkblue]Option[/color] [color=darkblue]Explicit[/color]

[color=darkblue]Private[/color] [color=darkblue]Sub[/color] Workbook_Open()
    [color=darkblue]Dim[/color] strFile [color=darkblue]As[/color] [color=darkblue]String[/color]
    strFile = "Workbook2"  [color=seagreen]'Change the workbook name, accordingly [/color]
    [color=darkblue]If[/color] WorkbookIsOpen(strFile) [color=darkblue]Then[/color]
        Workbooks(strFile).Close savechanges:=[color=darkblue]False[/color]  [color=seagreen]'Set the SaveChanges paramenter, accordingly[/color]
    [color=darkblue]End[/color] [color=darkblue]If[/color]
End [color=darkblue]Sub[/color]
[/font]

Note that you'll need to add the appropriate file extension to Workbook2 if it has already been saved. Now, when Workbook1 is opened, the code within the Workbook_Open event will automatically be executed.

 

[LSM1604]

Hi, thanks for the reply, but I can't get it to work. I already have some code in the open procedure and have added the code which you wrote. I have put the module code in module 3.

Thanks

 

[Domenic]

Try replacing...

Private Function WorkbookIsOpen(wbname) As Boolean

with

[COLOR="Red"]Public[/COLOR] Function WorkbookIsOpen(wbname) As Boolean