none
"appWord.ActiveDocument.Tables.Add" creates an unwanted extra table embedded in the first cell of the intended table RRS feed

  • Question

  • I can't figure out why this is happening. When the following code is run it unexpectedly results in an extra table, partially populated with data, embedded in the intended table.

    Below is the code and a snapshot of a resulting word document.

    Please help as I don't have much hair left to pull out ;)

    Private Sub Command51_Click() Dim rs As dao.Recordset Dim sqlrs As String Dim appWord As Object

    m bWordOpened As Boolean Dim doc As Object Dim oWordTbl As Object Dim iCols As Integer Dim iRecCount As Integer Dim iFldCount As Integer Dim i As Integer Dim j As Integer Dim myRange As Range Const wdWord8TableBehavior = 1 Const wdAutoFitFixed = 1 'Start WordOn Error Resume Next Set appWord = GetObject(, "Word.Application") 'Work on existing instance of Word If Err.Number <> 0 Then Err.Clear On Error GoTo Error_Handler Set appWord = CreateObject("Word.application") bWordOpened = False Else 'Word was already running bWordOpened = True End If On Error GoTo Error_Handler appWord.Visible = False 'Keep Word hidden until we are done working with it Set doc = appWord.Documents.Open("S:\APIDBSup\Support Files\Templates\VacationListCurrent.dot", , True) 'Open our SQL Statement sqlrs = "SELECT vacation.[employee], vacation.[Department], vacation.[StartDate], vacation.[EndDate] from Vacation where EndDate > Date()-1 order by StartDate" Set rs = CurrentDb.OpenRecordset(sqlrs) With rs If .RecordCount <> 0 Then .MoveLast 'Ensure proper count iRecCount = .RecordCount 'Number of records returned by the recordset .MoveFirst iFldCount = .Fields.Count 'Number of fields/columns returned by the recordset Set myRange = appWord.ActiveDocument.Range(0, 0) appWord.ActiveDocument.Tables.Add Range:=myRange, NumRows:=iRecCount, NumColumns:=iFldCount _ , DefaultTableBehavior:=wdWord8TableBehavior, AutoFitBehavior:=wdAutoFitFixed Set oWordTbl = doc.Tables(1) 'Build our Header Row oWordTbl.Cell(1, 1) = "Employee" oWordTbl.Cell(1, 2) = "Department" oWordTbl.Cell(1, 3) = "Start Date" oWordTbl.Cell(1, 4) = "End Date" '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 Set rs = Nothing Set doc = Nothing Set appWord = Nothing Exit Sub Error_Handler_Exit: On Error Resume Next appWord.Visible = True 'Make Word visible to the user rs.CLOSE Set rs = Nothing Set oWordTbl = Nothing Set doc = Nothing Set appWord = Nothing Exit Sub 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 Sub



    Les M.



    • Edited by les2worry Monday, August 14, 2017 1:58 AM
    Monday, August 14, 2017 1:57 AM

Answers

  • Hi les2worry,

    did you try to make changes in line below?

    Set myRange = appWord.ActiveDocument.Range(0, 0)
                appWord.ActiveDocument.Tables.Add 

    instead of using Active document, use the object of newly generated document.

    if you had made any changes then you can again try to post the code, we will try to make a test and try to check it.

    you had mentioned that,"it persists when MS Word app is closed. I did notice something else, which may be related this issue. When I click the button activating the subject code, it doesn't bring up the MS Word app, however "Winword.exe" process starts running."

    you also need to close that document in which you added the table and after that close the word app.

    do you mean when you first time generate the documents it is not showing you.

    when I run the code on my side. the document displayed properly.

    you also check that you are creating the object of Word app but you are not making it visible.

      On Error GoTo Error_Handler
        Set appWord = CreateObject("Word.application")
        bWordOpened = False
    Else 'Word was already running
        bWordOpened = True
    End If
    On Error GoTo Error_Handler
    appWord.Visible = False 'Keep Word hidden until we are done working with it
    

    you can try to set 'appWord.Visible = True' to make it visible.

    you can again try to make a test on your side and if you have any problem then you can post the latest code.

    Regards

    Deepak


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by les2worry Thursday, August 17, 2017 3:03 AM
    Wednesday, August 16, 2017 7:27 AM
    Moderator
  • Hi Deepak,

    Thank you very much for your help. It seems that "appWord.Visible = False" statement, among other intricacies, caused the weird behavior. Attached is a working code for reference. I am still working on formatting issues. 

    Private Sub Command51_Click()
    Dim rs As dao.Recordset
    Dim sqlrs As String
    Dim appWord         As Object
    Dim bWordOpened     As Boolean
    Dim doc             As Object
    Dim oWordTbl        As Object
    Dim iCols           As Integer
    Dim iRecCount       As Integer
    Dim iFldCount       As Integer
    Dim i               As Integer
    Dim j               As Integer
    Dim myRange         As Range
    'Const wdPrintView = 3
    Const wdWord8TableBehavior = 0
    Const wdAutoFitFixed = 1
    
    'Open our SQL Statement
    sqlrs = "SELECT vacation.[employee], vacation.[Department], vacation.[StartDate], vacation.[EndDate] from Vacation where EndDate > Date()-1 order by StartDate"
    Set rs = CurrentDb.OpenRecordset(sqlrs)
    With rs
        
        'Start Word
    On Error Resume Next
    Set appWord = GetObject(, "Word.Application") 'Bind to existing instance of Word
    If Err.Number <> 0 Then
        Err.Clear
        On Error GoTo Error_Handler
        Set appWord = CreateObject("Word.application")
        bWordOpened = False
    Else 'Word was already running
        bWordOpened = True
    End If
    On Error GoTo Error_Handler
    appWord.Visible = True
    
    Set doc = appWord.Documents.Open("S:\APIDBSup\Support Files\Templates\VacationListCurrent.dot", , True)
        
        If .RecordCount <> 0 Then
                .MoveLast   'Ensure proper count
                iRecCount = .RecordCount    'Number of records returned by the recordset
                .MoveFirst
                iFldCount = .Fields.Count   'Number of fields/columns returned by the recordset
                Set myRange = appWord.ActiveDocument.Range(0, 0)
                appWord.ActiveDocument.Tables.Add Range:=myRange, NumRows:=iRecCount + 1, NumColumns:=iFldCount _
                                                , DefaultTableBehavior:=wdWord8TableBehavior, AutoFitBehavior:=wdAutoFitFixed
                Set oWordTbl = doc.Tables(1)
                'Build our Header Row
                    oWordTbl.Cell(1, 1) = "Employee"
                    oWordTbl.Cell(1, 2) = "Department"
                    oWordTbl.Cell(1, 3) = "Start Date"
                    oWordTbl.Cell(1, 4) = "End Date"
                '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
                'Format header row text
                oWordTbl.Rows(1).Range.Font.Bold = True
                'oWordTbl.Rows.Alignment = wdAlignRowCenter
                'oWordTbl.Columns(1).Width = InchesToPoints(2.5)
        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
    
    Set rs = Nothing
    Set doc = Nothing
    Set appWord = Nothing
    Exit Sub
    
    Error_Handler_Exit:
        On Error Resume Next
        appWord.Visible = True   'Make Word visible to the user
        rs.CLOSE
        Set rs = Nothing
        Set oWordTbl = Nothing
        Set doc = Nothing
        Set appWord = Nothing
        Exit Sub
     
    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 Sub
    


    Les M.

    • Marked as answer by les2worry Thursday, August 17, 2017 3:03 AM
    Thursday, August 17, 2017 3:02 AM

All replies

  • Hi les2worry,

    I try to make a test with your code on my side.

    I try to create a word template and also create a table in Access.

    then I run the code.

    I find that when I run the code for the first time, table generated correctly. (but I notice that it skip the third record).

    you can see below is the actual data in access table.

    when first time table generated:

    you can see 3rd record is missing.

    then I again run the code. I got output below.

    because you are not closing the document in code and adding the table to active document.

    so it add the new table to that same document. I hope now you are clear why its adding the new table there.

    Testing result:

    so try to close the document after you add the table and save it somewhere.

    don't add the table to active document. add the table to that particular newly generated document.

    also try to check your logic that why it skips the 3rd record.

    Regards

    Deepak


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.


    Tuesday, August 15, 2017 3:19 AM
    Moderator
  • Hello Deepak,

    Thank you for your reply. Indeed one record is omitted in the Word table and I will try to get this resolved. However, getting back to my primary problem, it persists when MS Word app is closed. I did notice something else, which may be related this issue. When I click the button activating the subject code, it doesn't bring up the MS Word app, however "Winword.exe" process starts running. When I click it the second time the app opens with the embedded table.

    Any ideas?

    Thanks,

    Les 


    Les M.

    Wednesday, August 16, 2017 1:46 AM
  • Hi les2worry,

    did you try to make changes in line below?

    Set myRange = appWord.ActiveDocument.Range(0, 0)
                appWord.ActiveDocument.Tables.Add 

    instead of using Active document, use the object of newly generated document.

    if you had made any changes then you can again try to post the code, we will try to make a test and try to check it.

    you had mentioned that,"it persists when MS Word app is closed. I did notice something else, which may be related this issue. When I click the button activating the subject code, it doesn't bring up the MS Word app, however "Winword.exe" process starts running."

    you also need to close that document in which you added the table and after that close the word app.

    do you mean when you first time generate the documents it is not showing you.

    when I run the code on my side. the document displayed properly.

    you also check that you are creating the object of Word app but you are not making it visible.

      On Error GoTo Error_Handler
        Set appWord = CreateObject("Word.application")
        bWordOpened = False
    Else 'Word was already running
        bWordOpened = True
    End If
    On Error GoTo Error_Handler
    appWord.Visible = False 'Keep Word hidden until we are done working with it
    

    you can try to set 'appWord.Visible = True' to make it visible.

    you can again try to make a test on your side and if you have any problem then you can post the latest code.

    Regards

    Deepak


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by les2worry Thursday, August 17, 2017 3:03 AM
    Wednesday, August 16, 2017 7:27 AM
    Moderator
  • Hi Deepak,

    Thank you very much for your help. It seems that "appWord.Visible = False" statement, among other intricacies, caused the weird behavior. Attached is a working code for reference. I am still working on formatting issues. 

    Private Sub Command51_Click()
    Dim rs As dao.Recordset
    Dim sqlrs As String
    Dim appWord         As Object
    Dim bWordOpened     As Boolean
    Dim doc             As Object
    Dim oWordTbl        As Object
    Dim iCols           As Integer
    Dim iRecCount       As Integer
    Dim iFldCount       As Integer
    Dim i               As Integer
    Dim j               As Integer
    Dim myRange         As Range
    'Const wdPrintView = 3
    Const wdWord8TableBehavior = 0
    Const wdAutoFitFixed = 1
    
    'Open our SQL Statement
    sqlrs = "SELECT vacation.[employee], vacation.[Department], vacation.[StartDate], vacation.[EndDate] from Vacation where EndDate > Date()-1 order by StartDate"
    Set rs = CurrentDb.OpenRecordset(sqlrs)
    With rs
        
        'Start Word
    On Error Resume Next
    Set appWord = GetObject(, "Word.Application") 'Bind to existing instance of Word
    If Err.Number <> 0 Then
        Err.Clear
        On Error GoTo Error_Handler
        Set appWord = CreateObject("Word.application")
        bWordOpened = False
    Else 'Word was already running
        bWordOpened = True
    End If
    On Error GoTo Error_Handler
    appWord.Visible = True
    
    Set doc = appWord.Documents.Open("S:\APIDBSup\Support Files\Templates\VacationListCurrent.dot", , True)
        
        If .RecordCount <> 0 Then
                .MoveLast   'Ensure proper count
                iRecCount = .RecordCount    'Number of records returned by the recordset
                .MoveFirst
                iFldCount = .Fields.Count   'Number of fields/columns returned by the recordset
                Set myRange = appWord.ActiveDocument.Range(0, 0)
                appWord.ActiveDocument.Tables.Add Range:=myRange, NumRows:=iRecCount + 1, NumColumns:=iFldCount _
                                                , DefaultTableBehavior:=wdWord8TableBehavior, AutoFitBehavior:=wdAutoFitFixed
                Set oWordTbl = doc.Tables(1)
                'Build our Header Row
                    oWordTbl.Cell(1, 1) = "Employee"
                    oWordTbl.Cell(1, 2) = "Department"
                    oWordTbl.Cell(1, 3) = "Start Date"
                    oWordTbl.Cell(1, 4) = "End Date"
                '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
                'Format header row text
                oWordTbl.Rows(1).Range.Font.Bold = True
                'oWordTbl.Rows.Alignment = wdAlignRowCenter
                'oWordTbl.Columns(1).Width = InchesToPoints(2.5)
        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
    
    Set rs = Nothing
    Set doc = Nothing
    Set appWord = Nothing
    Exit Sub
    
    Error_Handler_Exit:
        On Error Resume Next
        appWord.Visible = True   'Make Word visible to the user
        rs.CLOSE
        Set rs = Nothing
        Set oWordTbl = Nothing
        Set doc = Nothing
        Set appWord = Nothing
        Exit Sub
     
    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 Sub
    


    Les M.

    • Marked as answer by les2worry Thursday, August 17, 2017 3:03 AM
    Thursday, August 17, 2017 3:02 AM