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.