Using Excel code to update Word getting Error 91 - New to coding for Word 2

[makeitwork09]

I am using Excel and Word 2007.

This is my first time working with code for Word.

The following code errors with error number 91. Originally the error was 462, but after adding wrdDoc, the error changed to 91.

    'format each table
        For wrdTbl = 1 To wrdDoc.Tables.Count
            Set t = wrdDoc.Tables(wrdTbl)
            t.Borders(wdBorderTop).LineStyle = wdLineStyleNone
            t.Borders(wdBorderLeft).LineStyle = wdLineStyleNone
            t.Borders(wdBorderBottom).LineStyle = wdLineStyleNone
            t.Borders(wdBorderRight).LineStyle = wdLineStyleNone
            t.Borders(wdBorderHorizontal).LineStyle = wdLineStyleNone
            t.Borders(wdBorderVertical).LineStyle = wdLineStyleNone
            t.Borders(wdBorderDiagonalDown).LineStyle = wdLineStyleNone
            t.Borders(wdBorderDiagonalUp).LineStyle = wdLineStyleNone
            t.Columns(7).SetWidth ColumnWidth:=71.45, RulerStyle:=wdAdjustNone
        Next wrdTbl

The following is the full code, but only the above seems to fail.

Sub UpdateWordDoc()

    Dim wrdApp As Word.Application
    Dim wrdDoc As Document
    Dim wrdSln As Selection
    Dim i As Integer
    Dim wrdTbl As Long
    Dim t As Table
    
    On Error Resume Next
    
    BkName = ThisWorkbook.Name
    
    On Error Resume Next
        
    Set wrdApp = GetObject(, "Word.Application")
    Set wrdDoc = wrdApp.ActiveDocument
    
        If wrdApp Is Nothing Then
            Set wrdApp = GetObject("", "Word.Application")
            On Error GoTo ErrorHandler
            wrdApp.Documents.Add
            wrdApp.Visible = True
        End If

    
    On Error GoTo 0
    
    On Error GoTo ErrorHandler
    
    wrdApp.Selection.Font.Name = "Arial"
    wrdApp.Selection.Font.Size = 10
    
    For i = 4 To Workbooks(BkName).Worksheets.Count
        With Workbooks(BkName).Worksheets(i)
            Workbooks(BkName).Worksheets(i).Range("C2:I36").Copy
            With wrdApp
                .Visible = True
                .Activate
                .Selection.PasteExcelTable False, True, False
                .Selection.EndKey unit:=wdStory
                .Selection.InsertBreak Type:=7
            End With
        End With
    Next i
    
    'format each table
        For wrdTbl = 1 To wrdDoc.Tables.Count
            Set t = wrdDoc.Tables(wrdTbl)
            t.Borders(wdBorderTop).LineStyle = wdLineStyleNone
            t.Borders(wdBorderLeft).LineStyle = wdLineStyleNone
            t.Borders(wdBorderBottom).LineStyle = wdLineStyleNone
            t.Borders(wdBorderRight).LineStyle = wdLineStyleNone
            t.Borders(wdBorderHorizontal).LineStyle = wdLineStyleNone
            t.Borders(wdBorderVertical).LineStyle = wdLineStyleNone
            t.Borders(wdBorderDiagonalDown).LineStyle = wdLineStyleNone
            t.Borders(wdBorderDiagonalUp).LineStyle = wdLineStyleNone
            t.Columns(7).SetWidth ColumnWidth:=71.45, RulerStyle:=wdAdjustNone
        Next wrdTbl
        
    
     'Release the Word object to save memory and exit macro
ErrorExit:
    Set wrdApp = Nothing
    Exit Sub
     
     'Error Handling routine
ErrorHandler:
    If Err Then
        MsgBox "Error No: " & Err.Number & "; There is a problem"
        If Not wrdApp Is Nothing Then
            wrdApp.Quit False
        End If
        Resume ErrorExit
    End If
    
End Sub

Thanks

 

[PHV]

I'd check the value of wrdDoc.Tables.Count before the For loop.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[macropod]

Do you think maybe you could post formatted code? Your last couple of code posts in this thread and your other one have been a dog's breakfast ...

Cheers
Paul Edstein
[MS MVP - Word]

 

[fumei]

may want to change:

Dim t As Table

to

Dim t As Word.Table

 

[PHV]

Anyway, I'd replace this:
If wrdApp Is Nothing Then
Set wrdApp = GetObject("", "Word.Application")

with this:
If wrdDoc Is Nothing Then

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[makeitwork09]

Thanks to all who tried to help.

PHV was on target when recommending that I reveiw

the value of wrdDoc.Tables.Count before the For loop

I am not sure why Set wrdDoc = wrdApp.ActiveDocument was not working. When I changed to the following it worked:

For wrdTbl = 1 To wrdApp.ActiveDocument.Tables.Count
  Set t = wrdApp.ActiveDocument.Tables(wrdTbl)
  ...

fumei, I also used your suggestion.

Since I am someone who is trying to learn (I mentioned this is my first time working with Word VBA code) and who spends a great deal of time referring to the books that I have purchased and searching for solutions before giving up and asking for help, Paul, it would be appreciated if you were to let me know, specifically, what your were addressing in regards to the "formatting". That would be a lot more beneficial in the learning process than calling what I had done "dog breakfast".

Thanks again everyone

 

[makeitwork09]

I thought I had this working, but when I tested the table portion of the code with the code that copies the information from Excel (the full code can be seen in my initial post), I now get error 462 the remote server machine does not exist or is not available.

I am looking around now, on the web, to try to figure out how to get rid of that error. Currently, I am reviewing

http://support.microsoft.com/kb/319832.

 

[PHV]

the full code can be seen in my initial post
What about my suggestion stamped 11 May 12 20:54 ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[makeitwork09]

PHV - I tried that, however, it did not resolve the problem. I also referred to my VBA and Macros for Microsoft Office Excel 2007 book, after it still was not working with your suggestion to see how it handles the Document object. The book had it the way I originally had it. I appreciate your help. Your suggestions got me pointed in the right direction.

I have since resolved error 462 that I was receiving. The code now looks like:

Sub UpdateWordDoc()

    Dim wrdApp As Word.Application
    Dim wrdDoc As Word.Document
    Dim wrdSln As Word.Selection
    Dim i As Integer
    Dim wrdTbl As Word.Table
    
    
    BkName = ThisWorkbook.Name
    
    On Error Resume Next
        
    Set wrdApp = GetObject(, "Word.Application")
    Set wrdDoc = wrdApp.ActiveDocument
    
        If wrdApp Is Nothing Then
            Set wrdApp = GetObject("", "Word.Application")
            On Error GoTo ErrorHandler
            wrdApp.Documents.Add
            wrdApp.Visible = True
        End If
    
    On Error GoTo 0
    
    On Error GoTo ErrorHandler
    
        wrdApp.Selection.Font.Name = "Arial"
        wrdApp.Selection.Font.Size = 10
    
        For i = 4 To Workbooks(BkName).Worksheets.Count
            With Workbooks(BkName).Worksheets(i)
                Workbooks(BkName).Worksheets(i).Range("C2:I36").Copy
                With wrdApp
                    .Visible = True
                    .Activate
                    .Selection.PasteExcelTable False, False, False
                    .Selection.EndKey unit:=wdStory
                    .Selection.InsertBreak Type:=7
                End With
            End With
        Next i
        
    
        'format each table
        With wrdApp
            .Selection.WholeStory
            For Each wrdTbl In .Selection.Tables
                With wrdTbl
                    .Rows.HeightRule = wdRowHeightExactly
                End With
            Next wrdTbl
        End With

     'Release the Word object to save memory and exit macro
ErrorExit:
    Set wrdApp = Nothing
    Exit Sub
     
     'Error Handling routine
ErrorHandler:
    If Err Then
        MsgBox "Error No: " & Err.Number & "; There is a problem"
        If Not wrdApp Is Nothing Then
            wrdApp.Quit False
        End If
        Resume ErrorExit
    End If
    
    
End Sub