Workbook close when Hidden not working - dynamic file location/filename

[hensleyj]

Hey everyone,

This has been bustin my balls all day.

I have a workbook which when opened, also opens another workbook which has visible = false set.

When i exit the main workbook, the other workbook does not close. I have tried multiple things to get it to close without luck.

I need a workbook close event which gets the file path and file name from;

Sheet: Ranges
File Path:I387
File name: L387

And then tells it to close even when it is hidden.

The file that opens hidden, will vary and the data held in "Ranges" confirms the file which is open.

Does anyone know how i could do this on a workbook close event? Or is there another way?

I tried quit application, which didn't work. I did try Application.Run with the location of the macro on the hidden workbook, but this had problems also.

 

[AlphaFrog]

Try something like this. The blue code is the file name.

[COLOR=darkblue]Private[/COLOR] [COLOR=darkblue]Sub[/COLOR] Workbook_BeforeClose(Cancel As Boolean)
    Application.Workbooks([COLOR=#0000ff]Sheets("Ranges").Range("L387").Value[/COLOR]).Close SaveChanges:=[COLOR=darkblue]False[/COLOR]
[COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Sub[/COLOR]

 

[hensleyj]

That worked great! thanks heaps mate. I haven't worked too much with dynamic references like this so this will greatly help me moving forward.

There was a problem with the code in instances where there wasn't a workbook open in L387, so i set it up like this which works perfect for anyone who might also wonder if they read this,

Private Sub Workbook_BeforeClose(Cancel As Boolean)

    nowbs = Application.WORKBOOKS.Count
     
    If nowbs >= 2 Then
         
        Application.ScreenUpdating = False
        Application.WORKBOOKS(Sheets("Ranges").Range("L387").Value).Close SaveChanges:=False
        Application.ThisWorkbook.Saved = True 'Tells Excel that the file has already been saved (this prevents Excel from requesting that you save the file when you close it)


         
    Else
    Application.ThisWorkbook.Saved = True 'Tells Excel that the file has already been saved (this prevents Excel from requesting that you save the file when you close it)
    End If
End Sub

 

[AlphaFrog]

You're welcome.

This could work as well. It just ignores the error if there is no other workbook with the L387 name.

[color=darkblue]Private[/color] [color=darkblue]Sub[/color] Workbook_BeforeClose(Cancel [color=darkblue]As[/color] [color=darkblue]Boolean[/color])
    [color=darkblue]On[/color] [color=darkblue]Error[/color] [color=darkblue]Resume[/color] [color=darkblue]Next[/color]
    Application.Workbooks(Sheets("Ranges").Range("L387").Value).Close SaveChanges:=[color=darkblue]False[/color]
    Application.ThisWorkbook.Saved = [color=darkblue]True[/color]
[color=darkblue]End[/color] [color=darkblue]Sub[/color]