CODEWITHSUNDEEP
excelvbams-word
Mavorike
Mavorike

Reputation: 3

Having issues exiting table in Word when referencing it from Excel using VBA

I am trying to basically build a Word document from an Excel document. The purpose of this is to load it up into another program that likes Word files.

My main issue is a real simple one. I am having troubles exiting a table in Word, while running from my Excel Macro. I get through my first loop, but on my second loop it wants to stay in the table and just add information to the table, then fails when it tries to add a table to a table.

Here is some of my code '''

Sub GetSummaDatSweetDocumentToUpload()


'Declarations
Dim Word As Word.Application
Dim WordDocumentToUpload As Word.Document
Dim lngExcelCounter As Long
Dim lngWordCounter As Long


'Build Word File
Set Word = New Word.Application
Set WordDocumentToUpload = Word.Documents.Add
Word.Visible = True

'Set my starting points for the counters
lngWordCounter = 1
lngExcelCounter = 65





While lngExcelCounter < 500 'Loop until end of document
    If Worksheets("DocumentToUpload Main").Cells(lngExcelCounter, 1).Value <> "" Then 'Look for populated field
        If Worksheets("DocumentToUpload Main").Cells(lngExcelCounter + 1, 2).Value = "Requirements" Then 'Parse out record I want to add to Word
            WordDocumentToUpload.Paragraphs(lngWordCounter).Range = Worksheets("DocumentToUpload Main").Cells(lngExcelCounter, 1).Value
            'Create Table in Word
            WordDocumentToUpload.Tables.Add Range:=WordDocumentToUpload.Paragraphs(lngWordCounter).Range, NumRows:=2, NumColumns:=10
            With WordDocumentToUpload.Tables(lngWordCounter)
                If .Style <> "Table Grid" Then
                    .Style = "Table Grid"
                End If
                .ApplyStyleHeadingRows = True
                .ApplyStyleLastRow = False
                .ApplyStyleFirstColumn = True
                .ApplyStyleLastColumn = False
                .ApplyStyleRowBands = True
                .ApplyStyleColumnBands = False
            End With

            'Format the table
            WordDocumentToUpload.Tables(1).Cell(1, 1).Range.Text = "Requirement Label"
            WordDocumentToUpload.Tables(1).Cell(1, 2).Range.Text = "Customer Req?"
            WordDocumentToUpload.Tables(1).Cell(1, 3).Range.Text = "Type"
            WordDocumentToUpload.Tables(1).Cell(1, 4).Range.Text = "Circuit Parameter"
            WordDocumentToUpload.Tables(1).Cell(1, 5).Range.Text = "Sym"
            WordDocumentToUpload.Tables(1).Cell(1, 6).Range.Text = "Min"
            WordDocumentToUpload.Tables(1).Cell(1, 7).Range.Text = "Typ"
            WordDocumentToUpload.Tables(1).Cell(1, 8).Range.Text = "Max"
            WordDocumentToUpload.Tables(1).Cell(1, 9).Range.Text = "Units"
            WordDocumentToUpload.Tables(1).Cell(1, 10).Range.Text = "Comments and/Or Conditions"
            WordDocumentToUpload.Tables(1).Cell(2, 10).Select
            Selection.MoveDown Unit:=wdScreen, Count:=1
            lngWordCounter = lngWordCounter + 1 'Increment the counter that is used to track which paragraph is being used.  The purpose is to use paragraphs to work my way through Word
        End If
    End If





    lngExcelCounter = lngExcelCounter + 1 'Increment through Excel
Wend

WordDocumentToUpload.SaveAs2 Filename:="Brady Test.docx", _
    FileFormat:=wdFormatDocumentDefault


End Sub

'''

Its my first post, so I am sorry for formatting.

======================================================================

Edit one, adding Cindy Meister's fix

'Declarations
Dim WordApp As Word.Application
Dim WordDocumentToUpload As Word.Document
Dim lngExcelCounter As Long
Dim lngWordCounter As Long
Dim WordTbl As Word.Table
Dim rngTable As Word.Range


'Build Word File
Set WordApp = New Word.Application
Set WordDocumentToUpload = WordApp.Documents.Add
WordApp.Visible = True

'Set my starting points for the counters
lngWordCounter = 1
lngExcelCounter = 65




WordDocumentToUpload.Paragraphs.Add
While lngExcelCounter < 500 'Loop until end of document
    If Worksheets("DocumentToUpload Main").Cells(lngExcelCounter, 1).Value <> "" Then 'Look for populated field
        If Worksheets("DocumentToUpload Main").Cells(lngExcelCounter + 1, 2).Value = "Requirements" Then 'Parse out record I want to add to Word
            WordDocumentToUpload.Paragraphs(lngWordCounter).Range = Worksheets("DocumentToUpload Main").Cells(lngExcelCounter, 1).Value
            'Create Table in Word
            WordDocumentToUpload.Paragraphs.Add
'           WordDocumentToUpload.Paragraphs (lngWordCounter)
            lngWordCounter = lngWordCounter + 1
            Set WordTbl = WordDocumentToUpload.Tables.Add(Range:=WordDocumentToUpload.Paragraphs(lngWordCounter).Range, _
                               NumRows:=2, NumColumns:=10)
            With WordTbl
                If .Style <> "Table Grid" Then
                    .Style = "Table Grid"
               End If
                .ApplyStyleHeadingRows = True
                .ApplyStyleLastRow = False
                .ApplyStyleFirstColumn = True
                .ApplyStyleLastColumn = False
                .ApplyStyleRowBands = True
                .ApplyStyleColumnBands = False
                .Cell(1, 1).Range.Text = "Requirement Label"
                .Cell(1, 2).Range.Text = "Customer Req?"
                .Cell(1, 3).Range.Text = "Type"
                .Cell(1, 4).Range.Text = "Circuit Parameter"
                .Cell(1, 5).Range.Text = "Sym"
                .Cell(1, 6).Range.Text = "Min"
                .Cell(1, 7).Range.Text = "Typ"
                .Cell(1, 8).Range.Text = "Max"
                .Cell(1, 9).Range.Text = "Units"
                .Cell(1, 10).Range.Text = "Comments and/Or Conditions"
'                .Cell(2, 10).Select

            End With


            Set rngTable = WordTbl.Range
            rngTable.Collapse Word.WdCollapseDirection.wdCollapseEnd
            WordDocumentToUpload.Paragraphs.Add
            lngWordCounter = lngWordCounter + 1 'Increment the counter that is used to track which paragraph is being used.  The purpose is to use paragraphs to work my way through Word


        End If
    End If





    lngExcelCounter = lngExcelCounter + 1 'Increment through Excel
Wend

WordDocumentToUpload.SaveAs2 Filename:="Brady Test.docx", _
    FileFormat:=wdFormatDocumentDefault

The result is I am getting a table within a table.

Table within a table

Upvotes: 0

Views: 425

Answers (1)

Cindy Meister
Cindy Meister

Reputation: 25693

It will help to work with a specific Table object, rather than using Tables([index]) to refer to the table. Instantiate the object when creating the table, then use it for everything to do with that table. For example 

Dim tbl As Word.Table
Set tbl = WordDocumentToUpload.Tables.Add(Range:=WordDocumentToUpload.Paragraphs(lngWordCounter).Range, _
                               NumRows:=2, NumColumns:=10)

With tbl

In order to move "outside" (below) the table, use a Range object and "collapse" it (think of it like pressing right-arrow when you have a selection, to move the focus to the right and get a blinking cursor):

Dim rngTable as Word.Range
Set rngTable = tbl.Range
rngTable.Collapse Word.WdCollapseDirection.wdCollapseEnd

At this point rngTable will be just beyond/outside the table, so anything added to it will be outside the table.

The other reason the code in the question is not working is because every Word table cell contains at least one Paragraph. So incrementing a counter in order to identify a specific paragraph is not going to put the focus in the expected place.

There are various ways to go about getting to the end of the document. My preference is below. I work solely with one (in this case, but it could be more) Range object(s) as the target, right from the start. vbCr is the character for a paragraph, so can be appended to text.

Using a Range object is essentially:

  • assign content
  • format the content
  • collapse the Range
  • repeat the steps

The modified code, based on this principle:

Dim WordApp As Word.Application
Dim WordDocumentToUpload As Word.Document
Dim lngExcelCounter As Long
Dim lngWordCounter As Long
Dim WordTbl As Word.Table
Dim rngTable As Word.Range    

'Build Word File
Set WordApp = New Word.Application
Set WordDocumentToUpload = Word.Documents.Add
WordApp.Visible = True

'Set my starting points for the counters
lngWordCounter = 1
lngExcelCounter = 1

Set rngTable = WordDocumentToUpload.Content
rngTable.InsertAfter vbCr
rngTable.Collapse wdCollapseEnd
While lngExcelCounter < 4 'Loop until end of document
    If Worksheets("DocumentToUpload Main").Cells(lngExcelCounter, 1).value <> "" Then 'Look for populated field
        If Worksheets("DocumentToUpload Main").Cells(lngExcelCounter + 1, 2).value = "Requirements" Then 'Parse out record I want to add to Word
            rngTable = "Test " & lngExcelCounter & vbCr
            rngTable.Collapse wdCollapseEnd
            'Create Table in Word
            Set WordTbl = WordDocumentToUpload.Tables.Add(Range:=rngTable, _
                               NumRows:=2, NumColumns:=10)
            With WordTbl
                If .Style <> "Table Grid" Then
                    .Style = "Table Grid"
               End If
                .ApplyStyleHeadingRows = True
                .ApplyStyleLastRow = False
                .ApplyStyleFirstColumn = True
                .ApplyStyleLastColumn = False
                .ApplyStyleRowBands = True
                .ApplyStyleColumnBands = False
                .Cell(1, 1).Range.Text = "Requirement Label"
                .Cell(1, 2).Range.Text = "Customer Req?"
                .Cell(1, 3).Range.Text = "Type"
                .Cell(1, 4).Range.Text = "Circuit Parameter"
                .Cell(1, 5).Range.Text = "Sym"
                .Cell(1, 6).Range.Text = "Min"
                .Cell(1, 7).Range.Text = "Typ"
                .Cell(1, 8).Range.Text = "Max"
                .Cell(1, 9).Range.Text = "Units"
                .Cell(1, 10).Range.Text = "Comments and/Or Conditions"
'                .Cell(2, 10).Select    
            End With

            Set rngTable = WordTbl.Range
            rngTable.Collapse Word.WdCollapseDirection.wdCollapseEnd

        End If
   End If

   lngExcelCounter = lngExcelCounter + 1 'Increment through Excel
Wend

WordDocumentToUpload.SaveAs2 fileName:="Brady Test.docx", _
    FileFormat:=wdFormatDocumentDefault

Upvotes: 2

Related Questions