close all office files

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

colt - 25 Aug 2006 03:56 GMT

Could anyone help with the code to close all office applications that are
currently open as I close an office docement.
Thanks in anticipation
Colt

Reply to this Message

Jezebel - 25 Aug 2006 04:22 GMT

1. Use GetObject() to retrieve any existing instance of the Office
application. You'll need to do this repeatedly for apps that might have more
than one running instance.

2. Use the app's methods (different in each case) to check for open and
unsaved data.

eg, for Excel

Dim pobjApp as object
Do
on error resume next
set pobjApp = GetObject(,"Excel.Application")
on error goto 0
if pobjApp is nothing then
exit do
end if
Do until pobjApp.Workbooks.Count = 0
... prompt to save, close
Loop
pobjApp.Quit
set pobjApp = Nothing
Loop

> Could anyone help with the code to close all office applications that are
> currently open as I close an office docement.
> Thanks in anticipation
> Colt

Reply to this Message

colt - 25 Aug 2006 04:42 GMT

Hi Jezebel,

I have used the code below to close excel documents as I exit an Excel
workbook. Is there an equivalent method in word which closes excel and word
files? What I have is a number of files that are opened using hyperlinks and
would like to close all files opened as I exit main program.

Thanks Colt
Public Sub Closeallworkbooks()
Dim Wkb As Workbook
For Each Wkb In Workbooks
If Wkb.Name <> ThisWorkbook.Name Then
Wkb.Saved = True
Wkb.Close
End If
Next Wkb
With ThisWorkbook
.Saved = True
.Close
End With

End Sub
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Call Closeallworkbooks
End Sub

> 1. Use GetObject() to retrieve any existing instance of the Office
> application. You'll need to do this repeatedly for apps that might have more
[quoted text clipped - 24 lines]
> > Thanks in anticipation
> > Colt

Reply to this Message

Jezebel - 25 Aug 2006 05:03 GMT

The code I posted will work from Word. Your code will also, if you qualify
the object references. The code for Word is similar, but you iterate the
Documents collection instead of the Workbooks collection. Do you really mean
to discard all changes made to everything?

> Hi Jezebel,
>
[quoted text clipped - 54 lines]
>> > Thanks in anticipation
>> > Colt

Reply to this Message

colt - 25 Aug 2006 05:48 GMT

There is no need to save changes as all files are information read only files.
This code fails what's wrong?

Public Sub Closeallworkbooks()
Dim Wkb As Workbook
For Each Wkb In Workbooks
If Wkb.Name <> ThisWorkbook.Name Then
Wkb.Saved = True
Wkb.Close
End If
Next Wkb

With ThisWorkbook
.Saved = True
.Close
End With
End Sub

Public Sub Closealldocs()
Dim oDoc As Document
For Each oDoc In Documents
If oDoc.Name <> ThisDocument.Name Then
oDoc.Saved = True
oDoc.Close
End If
Next oDoc

With ThisDocument
.Saved = True
.Close
End With

End Sub

Private Sub Document_Close()
Stop
Call Closeallworkbooks
Call Closealldocs

End Sub

> The code I posted will work from Word. Your code will also, if you qualify
> the object references. The code for Word is similar, but you iterate the
[quoted text clipped - 59 lines]
> >> > Thanks in anticipation
> >> > Colt

Reply to this Message

Jezebel - 25 Aug 2006 06:22 GMT

If you're trying to run that in Word, it's because you haven't qualified the
object references -- you have to tell VBA that these objects belong to the
Excel application object, which you have to instantiate. Have a look at the
code I posted.

Separately, there's no need to handle ThisWorkbook and ThisDocument
separately from the other workbooks and documents. In fact, if you're
running from Word, I'm not sure that ThisWorkbook will be valid at all,
since that's a reference to the workbook that contains the running code.

> There is no need to save changes as all files are information read only
> files.
[quoted text clipped - 107 lines]
>> >> > Thanks in anticipation
>> >> > Colt

Reply to this Message

colt - 25 Aug 2006 07:34 GMT

Sorry for not responding quickly - had to get my extensions maths group a
lesson thanks for all your help I much appreciate it. I'll have a go with the
code you suggested. thanks again
colt

> If you're trying to run that in Word, it's because you haven't qualified the
> object references -- you have to tell VBA that these objects belong to the
[quoted text clipped - 117 lines]
> >> >> > Thanks in anticipation
> >> >> > Colt

Reply to this Message