Inserting Table after the Excel Pasted Table identified as the paragraph.

[structuring]

I want to form a table at the word documents after inserting the exceltable as paragraphs.


This is the code that I used.

Sub MSWord()
    Dim wApp As Word.Application
    Dim wDoc As Word.Document
    Dim wTable As Word.Table
    Dim wb As Workbook, wsh As Worksheet, i As Long
    Dim cel As Cell
    Set wb = ThisWorkbook
    Dim wRange As Range


    Set wApp = CreateObject("word.application")
        wApp.Visible = True
        Set wDoc = wApp.Documents.add
        Set tbRange = ThisWorkbook.Worksheets("Terms and conditions-Zero Call").Range("A1:A12")
        tbRange.Copy
        wDoc.Paragraphs(1).Range.PasteExcelTable False, False, False
        wDoc.Range.InsertAfter vbNewLine
        wDoc.Paragraphs(25).Range.InlineShapes.AddHorizontalLineStandard
        Set tbRange1 = ThisWorkbook.Worksheets("Payoff-Zero Call").Range("A1:F32")
        tbRange1.Copy
        wDoc.Paragraphs(26).Range.PasteExcelTable False, False, False
        intNoofRows = Year(Worksheets("Input").Range("H5")) - Year(Worksheets("Input").Range("G5"))
        intNoofColumns = 4
        Set wRange = wDoc.Range
        wDoc.Tables.add wRange, intNoofRows, intNoofColumns
        Set wTable = wDoc.Tables(1)
        wTable.Borders.Enable = True
        wTable.Cell(1, 1).Range.Text = "j"
        wTable.Cell(1, 2).Range.Text = "Optional Redemption Date"
        wTable.Cell(1, 3).Range.Text = "Optional Redemption Amount"
        wTable.Cell(1, 4).Range.Text = "Optional Redomption Price"
        For i = 2 To intNoofRows
            wTable.Cell(i, 1).Range.Text = i - 1
            wTable.Cell(i, 2).Formula ("=Date(Year('Payoff-Zero Call'!$C$22)+i-1,Month('Payoff-Zero Call'!$C$22),Day('Payoff-Zero Call'!$C$22))")
            wTable.Cell(i, 3).Formula ("=Input!$D$5 & Round((1000000*(1+100*Input!$I$5/100)^(i-1)),2)")
            wTable.Cell(i, 4).Formula ("=ROUND((1000000*(1+100*Input!$I$5/100)^(i-1)),2)/1000000")
        Next i

Just ignore the contents of the table, they just the output of the formula that I entered. ALso, the previous part about the pasteExcelTable you can also ignore it. I just posted it because it might give a better idea on what i really want. So I would want the formed table in the word to be put right after the pasted excel table which refers to `ThisWorkbook.Worksheets("Payoff-Zero Call").Range("A1:F32")`


The problem is, at the For loop, I keep on getting the error: Application defined or object defined error. Or sometimes Type Mismatch when I try to adjust some trivial details.


Maybe is there my misconception in inserting table after the excel paste paragraph? Because if I just separate the tables code from previous code to only form table in the blank word, there is no problem.


Could you please shed some light for this problem? Thanks in advance

[gmayor]

With regard to your loop, wTable is a Word Table not an Excel table, so you have to use the Word VBA command set. Thus for cells 2, 3 & 4 you cannot use 'Formula' to fill the cell as if it was an Excel cell. You need to resolve the values separately and write the results to the appropriate cell's Range.Text.

For i = 2 To intNoofRows
    wTable.Cell(i, 1).Range.Text = i - 1
    wTable.Cell(i, 2).Formula ("=Date(Year('Payoff-Zero Call'!$C$22)+i-1,Month('Payoff-Zero Call'!$C$22),Day('Payoff-Zero Call'!$C$22))")
    wTable.Cell(i, 3).Formula ("=Input!$D$5 & Round((1000000*(1+100*Input!$I$5/100)^(i-1)),2)")
    wTable.Cell(i, 4).Formula ("=ROUND((1000000*(1+100*Input!$I$5/100)^(i-1)),2)/1000000")
Next i

[structuring]

Thank you for your reply.
According to your explanation, do you mean there is no way to make a customized table that will autofill the remaining columns with i loop, and some formula for excel since I have to write all values each cell by cell? Pretty new to vba. Please excuse the ignorance.

Or, if there is, could you point out which part could be amended?
When I start debugging,

wDoc.Tables.add wRange, intNoofRows, intNoofColumns

beginning of this part, there is always an application error. What would be a problem with this case?

[gmayor]

Without the Workbook it is difficult to understand the problems the code is encountering. Can you post the workbook or a link to it?
I didn't say that there was no way to create the table. I said that you cannot use Excel syntax in Word code.You need to derive the excel values to string variables then write the strings to the table cell ranges.