Simple VBA - if worksheet "budget.xlsx" is open, close it

[1992]

Access VBA - if worksheet "budget.xlsx" is open, close it

Hi,

I have a button on a form which the user presses when they're done in access.

How can I make it so that when clicked, this button will close an Excel workbook called "budget.xlsx" only if it's open?

I can close Excel files from an Excel macro using
Windows("budget.xlsx").Activate
ActiveWindow.Close False
but this doesn't work when I paste this into the on click event of my button, and even if it did work, it would produce an error if the file wasn't open.

Any help would be brilliant, thanks

 

[DirMex]

Workbooks("budget.xlsx").Close True

 

[1992]

I put the code into the on click section of the buttoncalled "view":

Private Sub view_Click()
Workbooks("budgets.xls").Close True
End Sub

Clicking gives the error:

"Compile Error:
Sub or Function not defined"

 

[1992]

Maybe I wasn't clear - I want to close the excel file from an Access form..

 

[DJkarl]

This will probably fail if more than one instance of excel is open.

On Error Resume Next
Dim xlApp As Object
Set xlApp = GetObject(, "Excel.Application")
Call xlApp.workbooks("budgets").Close
Set xlApp = Nothing

 

[1992]

This will probably fail if more than one instance of excel is open.

On Error Resume Next
Dim xlApp As Object
Set xlApp = GetObject(, "Excel.Application")
Call xlApp.workbooks("budgets").Close
Set xlApp = Nothing

Seems to do the job with loads of excel files open - thanks

Sorry to be a pain, though - it asks if I want to save when the button is clicked when I really just want it to just disappear without saving.. how can I stop it from asking?

 

[Guus2005]

don't save on close:

    Dim xlApp As New Excel.Application 'now you can use intellisense!
    xlApp.Workbooks("budgets").Close [COLOR="Red"]False[/COLOR]
    Set xlApp = Nothing

in order for this to work, you need to add a reference to the Excel library.

HTH

 

[1992]

don't save on close:

    Dim xlApp As New Excel.Application 'now you can use intellisense!
    xlApp.Workbooks("budgets").Close [COLOR=red]False[/COLOR]
    Set xlApp = Nothing

in order for this to work, you need to add a reference to the Excel library.

HTH

Compile error: User-defined type not defined.

What do you mean by adding a reference to the excel library?

 

[Guus2005]

in your vba window(ide) click in the menu: tools|references
now add the reference to the excel library.

piece of cake.

 

[1992]

in your vba window(ide) click in the menu: tools|references
now add the reference to the excel library.

piece of cake.

OK I added "Microsoft Office Excel 14.0 Object Library" but when I click the button and your code runs, I get the error:

Run Time Error 9: Subscript out of range and it highlights the line xlApp.Workbooks("budgets").Close False

 

[DJkarl]

Seems to do the job with loads of excel files open - thanks

Sorry to be a pain, though - it asks if I want to save when the button is clicked when I really just want it to just disappear without saving.. how can I stop it from asking?

Try adding the following line of code.

On Error Resume Next
Dim xlApp As Object
Set xlApp = GetObject(, "Excel.Application")
[COLOR=red]xlApp.DisplayAlerts = False
[/COLOR]Call xlApp.workbooks("budgets").Close
Set xlApp = Nothing

 

[1992]

Try adding the following line of code.

On Error Resume Next
Dim xlApp As Object
Set xlApp = GetObject(, "Excel.Application")
[COLOR=red]xlApp.DisplayAlerts = False[/COLOR]
Call xlApp.workbooks("budgets").Close
Set xlApp = Nothing

Perfect! Thanks a lot