quitting excell and word

[parkfairfax]

I am trying to exit from word using VBA and having some problems doing so. This is my following code which I use several times to create instances of word..

Dim appword As Word.Application, strdoc As String
Set appword = CreateObject("Word.Application&quot
appword.Visible = True
strdoc = "c:\Report\TOC2.doc"
appword.Documents.Open (strdoc)
Set appword = Nothing

I use the following code to quit..

appword.Quit
Set appword = Nothing
DoCmd.Close

When I use this though, I get an error message telling me Object Required.

Does anyone know how I can get my program to exit my program? I'm not very familiar with this type of coding.

Thank you,

Garry

 

[dsi]

Just below your Open statement, you set appword object equal to Nothing. Your code shows you do this twice. Remove the first instance and you should be fine. You should not need the DoCmd.Close statement either.

 

[parkfairfax]

I still get "Object Required". Any other suggestions?

 

[Databaseguy]

Should be appword.application.Quit
Tyrone Lumley
augerinn@gte.net

http://server47.hypermart.net/databaseguy

 

[CodeFish]

Hi parkfairfax & Co.

dsi is right if you use the following it works fine:-

Dim appword As Word.Application, strdoc As String

Set appword = CreateObject("Word.Application&quot
appword.Visible = True
strdoc = "c:\report\toc2.doc"
appword.Documents.Open (strdoc)

Set appword = Nothing

Use Set appword = Nothing as the last line of your code because this releases the reference to the Word Object.

 

[parkfairfax]

Okay, I haven't gotton that to work yet, but now I think its because I'm not closing properly code for the excell application that I have open, and its in the same module as the word coding. Code for that looks like this ..

strwks = "c:\Report\10-11-12_Tables.xls"
Set wks = GetObject(strwks)
wks.Parent.Visible = True
wks.Windows(1).Visible = True
wks.Worksheets("10-11-12_Tables&quot.Select
Set wks = Nothing

strwks = "c:\Report\13.xls"
Set wks = GetObject(strwks)
wks.Parent.Visible = True
wks.Windows(1).Visible = True
wks.Worksheets("Chart13&quot.Select
Set wks = Nothing

This code causes both of these spreadsheets to load up. Can you tell me how to close these out and to exit from Excell? Combined with the code that I have for the Word application, heopefully now, I will be able to close everything out if I can close down excell too.

Thank you,

Garry

 

[CodeFish]

Hi Again,

Try The code added below - I found it on MSDN:


' Declare necessary API routines:
Declare Function FindWindow Lib "user32" Alias _
"FindWindowA" (ByVal lpClassName As String, _
ByVal lpWindowName As Long) As Long

Declare Function SendMessage Lib "user32" Alias _
"SendMessageA" (ByVal hWnd As Long, ByVal wMsg As Long, _
ByVal wParam As Long, _
ByVal lParam As Long) As Long


Sub Codefish()

Call DetectExcel

strwks = "c:\Report\10-11-12_Tables.xls"
Set wks = GetObject(strwks)
wks.Parent.Visible = True
wks.Windows(1).Visible = True
wks.Worksheets("10-11-12_Tables&quot.Select
wks.Application.Quit
Set wks = Nothing

strwks = "c:\Report\13.xls"
Set wks = GetObject(strwks)
wks.Parent.Visible = True
wks.Windows(1).Visible = True
wks.Worksheets("Chart13&quot.Select
wks.Application.Quit
Set wks = Nothing

' If this copy of Microsoft Excel was not running when you
' started, close it using the Application property's Quit method.
' Note that when you try to quit Microsoft Excel, the
' title bar blinks and a message is displayed asking if you
' want to save any loaded files.
If ExcelWasNotRunning = True Then
wks.Application.Quit
End If

Set wks = Nothing ' Release reference to the
' application and spreadsheet.
End Sub

Sub DetectExcel()
' Procedure dectects a running Excel and registers it.
Const WM_USER = 1024
Dim hWnd As Long
' If Excel is running this API call returns its handle.
hWnd = FindWindow("XLMAIN", 0)
If hWnd = 0 Then ' 0 means Excel not running.
Exit Sub
Else
' Excel is running so use the SendMessage API
' function to enter it in the Running Object Table.
SendMessage hWnd, WM_USER + 18, 0, 0
End If
End Sub

This Worked for me but you do get prompts for saving the files.

Regards,

Codefish

 

[parkfairfax]

To all,

Thanks for all your suggestions. I'm still looking at them, but first...I have proven that the following code works fine in opening and closing a word document..

Dim appword As Word.Application, strdoc As String
Set appword = CreateObject("Word.Application&quot
appword.Visible = True
strdoc = "c:\Report\test1.doc"
appword.Documents.Open (strdoc)


appword.Quit
Set appword = Nothing

However; this works fine if I have all this code in one sub procedure, but it doesn't work if I separate the two codes in a Open document button and a close document button. Since appword is not a variable, I can't make it global can I? I would like to close test1 from another VBA statement in a separate procedure.

Thanks again.

 

[dsi]

Yes, you can make any variable global. Just put it at the top of your module, outside of any subroutine. If you declare it as Public, it will be available in the entire project.

Public wdApp As Word.Application

Sub Subroutine1()
    Set wdApp = CreateObject("Word.Application")
    wdApp.Visible = True
    strdoc = "c:\Report\test1.doc"
    wdApp.Documents.Open (strdoc)
    ...
End Sub

Sub Subroutine2()
    ...
    wdApp.Quit
    Set wdApp = Nothing
End Sub

 

[Sawatzky]

Dear parkfairfax,

pardon me for jumping in:

sure you can make any variable global, but I think you shouldn't. (Has got something do with maintainability of code)

What I prefer in such a situation is to hand the variable over as an argument to a sub or retrieve it as return value from a function.

regards Astrid

 

[ide]

It closes to me the all Excel application windows. Works to Word.Application, too.

Function CloseExcel() As Boolean
Dim dm As Object
On Error GoTo EndMyFunct
CloseExcel = False
Set dm = GetObject(, "Excel.Application&quot
dm.Quit
CloseExcel = True
EndMyFunct:
End Function
Sub CloseApps()
mMark:
If CloseExcel = False Then GoTo EndSub
Start = Timer ' Set start time.
Do While Timer < Start + 0.5
DoEvents ' Yield to other processes.
Loop
GoTo mMark
EndSub:
End Sub

you must before quit an application close all changed document for instance with Getobject(&quot;Word.Document&quot

on a word document:
...
Set dm = GetObject(, &quot;Excel.Application&quot
Do While True 'close documents without saving them
On Error Resume Next
Set dk = dm.ActiveDocument
If dk = Null Then GoTo EndDo
dk.Close False
On Error GoTo EndMyFunct
Loop
EndDo:
dm.Quit
CloseWord = True
EndMyFunct:
...

 

[ide]

i missed the example:
on a word document:
...
Set dm = GetObject(, &quot;Word.Application&quot
Do While True 'close documents without saving them
...