Consulting

Results 1 to 4 of 4

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

  1. #1

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

    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

  2. #2
    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
    Graham Mayor - MS MVP (Word) 2002-2019
    Visit my web site for more programming tips and ready made processes
    http://www.gmayor.com

  3. #3
    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?

  4. #4
    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.
    Graham Mayor - MS MVP (Word) 2002-2019
    Visit my web site for more programming tips and ready made processes
    http://www.gmayor.com

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •