asked on

Access Runtime Eror

I using the code below and I a get a automation error in line:

Set oWord = GetObject("Word.Application") 'Bind to existing instance of Word

Function Export2DOC(sQuery As String)
    Dim oWord           As Object
    Dim oWordDoc        As Object
    Dim oWordTbl        As Object
    Dim bWordOpened     As Boolean
    Dim db              As DAO.Database
    Dim rs              As DAO.Recordset
    Dim iCols           As Integer
    Dim iRecCount       As Integer
    Dim iFldCount       As Integer
    Dim i               As Integer
    Dim j               As Integer
    Const wdPrintView = 3
    Const wdWord9TableBehavior = 1
    Const wdAutoFitFixed = 0
 
 
    'Start Word
    'On Error Resume Next
    Set oWord = GetObject("Word.Application")    'Bind to existing instance of Word
 
    If Err.Number <> 0 Then    'Could not get instance of Word, so create a new one
        Err.Clear
        On Error GoTo Error_Handler
        Set oWord = CreateObject("Word.application")
        bWordOpened = False
    Else    'Word was already running
        bWordOpened = True
    End If
    On Error GoTo Error_Handler
    oWord.Visible = False   'Keep Word hidden until we are done with our manipulation
    Set oWordDoc = oWord.Documents.Add   'Start a new document
 
    'Open our SQL Statement, Table, Query
    Set db = CurrentDb
    Set rs = db.OpenRecordset("qryAppendixMultiple")
    With rs
        If .RecordCount <> 0 Then
            .MoveLast   'Ensure proper count
            iRecCount = .RecordCount    'Number of records returned by the table/query
            .MoveFirst
            iFldCount = .Fields.Count   'Number of fields/columns returned by the table/query
 
            oWord.ActiveWindow.View.Type = wdPrintView 'Switch to print preview mode (not req&#39;d just a personal preference)
            oWord.ActiveDocument.Tables.Add Range:=oWord.Selection.Range, NumRows:=iRecCount, NumColumns:= _
                                            iFldCount, DefaultTableBehavior:=wdWord9TableBehavior, AutoFitBehavior:= _
                                            wdAutoFitFixed
 
            Set oWordTbl = oWordDoc.Tables(1)
            'Build our Header Row
            For i = 0 To iFldCount - 1
                oWordTbl.Cell(1, i + 1) = rs.Fields(i).Name
            Next i
            'Build our data rows
            For i = 1 To iRecCount
                For j = 0 To iFldCount - 1
                    oWordTbl.Cell(i + 1, j + 1) = Nz(rs.Fields(j).Value, "")
                Next j
                .MoveNext
            Next i
        Else
            MsgBox "There are no records returned by the specified queries/SQL statement.", vbCritical + vbOKOnly, "No data to generate an Word spreadsheet with"
            GoTo Error_Handler_Exit
        End If
    End With
 
    '    oWordDoc.Close True, sFileName 'Save and close
 
    'Close Word if is wasn't originally running
    '    If bWordOpened = False Then
    '        oWord.Quit
    '    End If
 
Error_Handler_Exit:
    On Error Resume Next
    oWord.Visible = True   'Make Word visible to the user
    rs.Close
    Set rs = Nothing
    Set db = Nothing
    Set oWordTbl = Nothing
    Set oWordDoc = Nothing
    Set oWord = Nothing
    Exit Function
 
Error_Handler:
    MsgBox "The following error has occured" & vbCrLf & vbCrLf & _
           "Error Number: " & Err.Number & vbCrLf & _
           "Error Source: Export2DOC" & vbCrLf & _
           "Error Description: " & Err.Description _
           , vbOKOnly + vbCritical, "An Error has Occured!"
    Resume Error_Handler_Exit
End Function

Open in new window

PatHartman

I'm going to guess that Word.Application does not belong inside quotes.

shieldsco

ASKER

Tried and didn't work

ASKER CERTIFIED SOLUTION

Gustav Brock

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Scott McDaniel (EE MVE )

Has this worked previously?

Is the full version of Word installed on the machine?

You should leave the quotes ... that's valid syntax. Perhaps try the syntax shown here:

https://support.microsoft.com/en-us/help/288902/getobject-and-createobject-behavior-of-office-automation-servers

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

Dale Fye

you have remarked out the line:

'On Error Resume Next

so when Access encounters that error, an error is raised, and the code that follows (in-line error handling) is never reached.

I prefer a syntax that looks like the following, where the error handling is all done in the error handler.

    On Error goto ProcError 
    bWordOpened = True
    Set oWord = GetObject( , "Word.Application")    'Bind to existing instance of Word
 
    'Add other code here

ProcExit:
    on error resume next
    oWord.Visible = True   'Make Word visible to the user
    rs.Close
    Set rs = Nothing
    Set db = Nothing
    Set oWordTbl = Nothing
    Set oWordDoc = Nothing
'   If bWordOpened = False Then oWord.Quit
    Set oWord = Nothing
    Exit Function    

ProcError:

    Select Case Err.Number
        Case -2147221020, 429
            'No instance of Word already open, open new instance
            Set oWord = CreateObject("Word.application")
            bWordOpened = False
            Resume Next
        Case Else
           'add other error handler code here
       End Select

End Function

Open in new window

shieldsco

ASKER

Thanks