Hello,

I've got a problem to destroy word and excel objects generated via VBA: I would like to destroy these objects after I saved the result (some kind of queries from the database being written to a word document ...), but I don't have a clue how to do this.

Here are abstracts of the code I use:

1) The word problem:

...

On Error Resume Next

    Set wapp = GetObject(, "Word.Application")

On Error GoTo 0


    If wapp Is Nothing Then
        Set wapp = CreateObject("Word.Application")
    End If


    'open document
    wapp.Documents.Add

    'show document
    With wapp
        .Application.Visible = True
        .Application.Activate
    End With

    wapp.ActiveWindow.ActivePane.View.SeekView = wdSeekCurrentPageHeader

    With wapp.Selection.Font
        .name = "Arial Narrow"
        .Size = 11
        .Bold = False
    End With

    With wapp.Selection
        .ParagraphFormat.Alignment = wdAlignParagraphCenter
        .TypeText ("text")
        .InsertDateTime DateTimeFormat:="dd.MM.yyyy HH:mm", InsertAsField _
            :=False, DateLanguage:=wdGerman, CalendarType:=wdCalendarWestern, _
            InsertAsFullWidth:=False
        .TypeText (")")
        If .HeaderFooter.IsHeader = True Then
            wapp.ActiveWindow.ActivePane.View.SeekView = wdSeekCurrentPageFooter
        Else
            wapp.ActiveWindow.ActivePane.View.SeekView = wdSeekCurrentPageHeader
        End If

        .ParagraphFormat.Alignment = wdAlignParagraphCenter
        'Format Text
        With .Font
            .name = "Arial Narrow"
            .Size = 11
            .Bold = False
        End With

        .TypeText "As private and confidential"

    End With

    'Main document
    wapp.ActiveWindow.ActivePane.View.SeekView = wdSeekMainDocument

    With wapp.Selection
            .Style = ActiveDocument.Styles("Überschrift 1")
            .TypeText Text:="text"
            .TypeParagraph
        End With

    Dim str_name_doc As String
    str_name_doc = Now
    str_name_doc = Replace(str_name_doc, ":", "-")
    wapp.ActiveDocument.SaveAs Application.CurrentProject.Path & "\Ergebnisse\Aktueller Stand Eingabe " & str_name_doc & ".doc"
    'wapp.ActiveDocument.Close


The problem is, that if use that code for more than one time, and shutdown the word document before I use the code again, Access tells me, that there is no remote-server-host (Error 462).
Has anybody ever had this problem before or can tell me where I am doing wrong?

My second problem is how to destroy Excel objects. Here's the code I am using:

...

Private xlApp As Excel.Application
Private xlBook As Excel.Workbook
Private xlSheet As Excel.Worksheet

Private Sub Class_Initialize()

[...]

    On Error GoTo ERR_create_excel_object

        Set xlApp = GetObject(, "Excel.Application")
        Set xlBook = Excel.Workbooks.Open(Application.CurrentProject.Pa th & "\vorlage.xls")

    Exit Sub

ERR_create_excel_object:

        Set xlApp = CreateObject("Excel.Application")
        Set xlBook = Excel.Workbooks.Open(Application.CurrentProject.Pa th & "\vorlage.xls")

End Sub

And the destructor ...

Private Sub Class_Terminate()

    xlBook.Close
    xlApp.Quit


End Sub

Thank you very much in advance and sorry for my bad English!

Henning

Have you tried, "set wapp = nothing"? same goes with excel objects???

Sam Gharnagh
Jr. Programmer Analyst at MOH
Comp Sci at UofW

Have you tried:

   Set wapp = nothing

:)


--- Tom

Also:

   Set xlApp = nothing


--- Tom