Closing workbooks programatically

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

teepee - 20 Feb 2008 21:22 GMT

My problem is I want to use VBA to close 2 workbooks if they're open and do
nothing if they're not open.

Put it another way, if I put

Workbooks("book1.csv").Close SaveChanges:=False
Workbooks("book2.csv").Close SaveChanges:=False

and then book 1 and book 2 aren't already open, it creates a runtime error
which I don't want.

I thought this might work:

Dim wb As Workbook
If wb.Name = "book1.csv" Then
Workbooks("book1").Close SaveChanges:=False
'End If
'If wb.Name = "book2" Then
Workbooks("book2").Close SaveChanges:=False
'End If

But it doesn't. Anyone got any ideas?

Reply to this Message

teepee - 20 Feb 2008 21:47 GMT

> My problem is I want to use VBA to close 2 workbooks if they're open and
> do nothing if they're not open.

btw I found another way of doing it so that I didn't need to do this but I'd
still be interested to know if anyone knows how it might be done.

Reply to this Message

PCLIVE - 20 Feb 2008 21:55 GMT

Here is a short example to determine if a workbook is open. You should be
able to apply this to what you're doing.

Sub test()
On Error GoTo Skip
If Len(Application.Workbooks(Range("A1").Value).Name) > 0 _
Then
MsgBox ("workbook is open")
Exit Sub
Else
End If

Skip:
MsgBox ("workbook is not open")
End Sub

HTH,
Paul

>> My problem is I want to use VBA to close 2 workbooks if they're open and
>> do nothing if they're not open.
>
> btw I found another way of doing it so that I didn't need to do this but
> I'd still be interested to know if anyone knows how it might be done.

Reply to this Message

teepee - 20 Feb 2008 22:24 GMT

> Here is a short example to determine if a workbook is open. You should be
> able to apply this to what you're doing.

Many thanks

Reply to this Message

Tyro - 20 Feb 2008 22:26 GMT

This might do what you want

Sub CloseThem()
Dim wb As Workbook
Application.ScreenUpdating = False
Application.DisplayAlerts = False
For Each wb In Application.Workbooks
If wb.Name = "book1.csv" or wb.Name = "book2.csv" Then
' wb.Save
wb.Close
End If
Next wb
Application.ScreenUpdating = True
Application.DisplayAlerts = True
End Sub

Tyro

> My problem is I want to use VBA to close 2 workbooks if they're open and
> do nothing if they're not open.
[quoted text clipped - 18 lines]
>
> But it doesn't. Anyone got any ideas?

Reply to this Message

Dave Peterson - 20 Feb 2008 22:51 GMT

Is there a reason you can't ignore the error:

On error resume next
Workbooks("book1.csv").Close SaveChanges:=False
Workbooks("book2.csv").Close SaveChanges:=False
on error goto 0

Since you're discarding any changes, it doesn't seem like this would cause a
problem.

> My problem is I want to use VBA to close 2 workbooks if they're open and do
> nothing if they're not open.
[quoted text clipped - 18 lines]
>
> But it doesn't. Anyone got any ideas?

Signature

Dave Peterson

Reply to this Message