Announcement

Collapse
No announcement yet.

Pasting Excel Tables into word, automate; 60 USD

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • Pasting Excel Tables into word, automate; 60 USD

    Hi, I am trying to paste data from Excel tables into Word, and getting these tables to display one, below the other. The code attached below, doesn't work( the third table defined as "Table2" seems to be pasted instead of the second, defined as "Table 6"), and I also get an error that there are two few arguments. What am I doing incorrectly? Can I use something like .Paragraphs.Last.Range.InsertAfter.PasteExcelTable to ensure that its pasted after the range of the previous one?
    Also,
    My ultimate goal is to have these word documents/reports that specify information about financial portfolios built every time the corresponding data in excel is refreshed(once a month). While I don't need to automate the data upload process, what I am looking to do is either get
    a) all of the word reports( around 150 in number) published(and ideally saved) everytime the data is refreshed or an ActiveX button is clicked. Each portfolio has a 6 digit code which can be used as a reference key. As such, I would like to have the code block for transferring the tables/formatting the document wrapped under a condition to check if the code matches/exists. The way the excel table is set up is; the code followed by the corresponding info in the very next column.
    OR
    b) have a button for each portfolio/code, which upon being clicked produces the corresponding word report
    How can this be achieved?

    Existing Code :

    Code:
    Sub ExcelRangeToWord()
     Dim wdApp As New Word.Application, wDoc As Word.Document, wdTbl As Word.Table
     Dim r As Long, c As Long, lClr As Long
     
     'create a word document'
    Set wdDoc = wdApp.Documents.Add
     With wdDoc
        .PageSetup.TopMargin = wdApp.InchesToPoints(0.25)
     
     'Adding the Title'
     With wdDoc.Sections(1)
        .Headers(wdHeaderFooterPrimary).Range.Font.Name = "Calibri"
        .Headers(wdHeaderFooterPrimary).Range.Font.Size = 32
        .Headers(wdHeaderFooterPrimary).Range.Font.Color = RGB(100, 149, 237)
        .Headers(wdHeaderFooterPrimary).Range.Paragraphs.Alignment = wdAlignParagraphCenter
        .Headers(wdHeaderFooterPrimary).Range.Text = ThisWorkbook.Worksheets("Summary").Range("D2").Value
     End With
    
    
     
     
     'Copy excel range
        Worksheets("Summary").ListObjects("Table1").Range.Copy
        .Paragraphs.Last.Range.PasteExcelTable LinkedToExcel:=True, WordFormatting:=False, RTF:=False
     
     'Clear the clipboard
     Application.CutCopyMode = False
     
     Set wdTbl = .Tables(.Tables.Count)
     With wdTbl
         .AutoFitBehavior (wdAutoFitWindow)
        .Rows.HorizontalPosition = 5
        .Rows.VerticalPosition = 100
         With .Range.Font
        .Name = "Calibri"
        .Size = 12
        End With
        For r = 1 To .Rows.Count
         For c = 1 To .Columns.Count
             Select Case c
                Case 1: lClr = RGB(176, 196, 222)
                Case 2: lClr = RGB(216, 191, 216)
                Case 3: lClr = RGB(238, 232, 170)
                Case 4: lClr = RGB(222, 184, 135)
                Case 5: lClr = RGB(143, 188, 143)
                Case Else: lClr = RGB(255, 255, 255)
             End Select
            .Cell(r, c).Range.Shading.BackgroundPatternColor = lClr
         Next
        Next
    End With
    
    
    With .Paragraphs.Last.Range
        .ParagraphFormat.Alignment = wdAlignParagraphLeft
        .Font.Size = 14
        .Font.Color = RGB(100, 149, 237)
        .InsertAfter Worksheets("Description").Range("B2").Value & vbCr & vbCr
    End With
    
    
    'Worksheets("IO").ListObjects("Table6").Range.Copy
    '.Paragraphs.Last.Range.PasteExcelTable LinkedToExcel:=True, WordFormatting:=False, RTF:=False
    
    
    Worksheets("IO").ListObjects("Table6").Range.Copy
       .Paragraphs.Last.Range.ParagraphFormat.Alignment = wdAlignParagraphLeft
    Worksheets("IO").ListObjects("Table6").Range.Copy
       .Paragraphs.Last.Range.PasteExcelTable LinkedToExcel:=True, WordFormatting:=False, RTF:=False
    
    
    
    
    'Clear the clipboard
    wdDoc.Styles("Normal").ParagraphFormat.SpaceAfter = 16
    
    
    Application.CutCopyMode = False
    Set wdTbl = .Tables(.Tables.Count)
    With wdTbl
        .AutoFitBehavior (wdAutoFitWindow)
        .Cell(1, 1).SetWidth _
            ColumnWidth:=InchesToPoints(1.5), _
            RulerStyle:=wdAdjustNone
        .Cell(2, 1).SetWidth _
            ColumnWidth = InchesToPoints(1.5), _
            RulerStyle:=wdAdjustNone
            
        With .Range.Font
            .Name = "Arial"
            .Size = 14
            .Color = RGB(100, 149, 237)
        End With
    End With
    
    
    
    
    'Copy excel range of table for Investor Profile
       ' Worksheets("Inv Profile").ListObjects("Table2").Range.Copy
       ' .Paragraphs.Last.Range.InsertAfter.PasteExcelTable LinkedToExcel:=True, WordFormatting:=False, RTF:=False
        
     'clear the clipboard
    'wdDoc.Styles("Normal").ParagraphFormat.SpaceAfter = 16
    ' Application.CutCopyMode = False
     
    
    
    'Set wdTbl = .Tables(.Tables.Count)
    'With wdTbl
     '  .AutoFitBehavior (wdAutoFitWindow)
     ' .Cell(1, 1).SetWidth _
      '      ColumnWidth:=InchesToPoints(1.5), _
       '     RulerStyle:=wdAdjustNone
       ' .Cell(2, 1).SetWidth _
        '   ColumnWidth = InchesToPoints(1.5), _
         '   RulerStyle:=wdAdjustNone
            
       ' With .Range.Font
        '    .Name = "Arial"
         '   .Size = 14
          '  .Color = RGB(100, 149, 237)
        'End With
    End With
    
    
    ' Copy excel range of table for Portfolio Characteristics
       'Worksheets("Port Char").ListObjects("Table3").Range.Copy
        '.Paragraphs.Last.Range.PasteExcelTable LinkedToExcel:=True, WordFormatting:=False, RTF:=False
        
     'clear the clipboard
     'wdDoc.Styles("Normal").ParagraphFormat.SpaceAfter = 16
     'Application.CutCopyMode = False
     
    
    
    'Set wdTbl = .Tables(.Tables.Count)
    'With wdTbl
     '   .AutoFitBehavior (wdAutoFitWindow)
      '  .Cell(1, 1).SetWidth _
       '     ColumnWidth:=InchesToPoints(1.5), _
        '    RulerStyle:=wdAdjustNone
        '.Cell(2, 1).SetWidth _
        '    ColumnWidth = InchesToPoints(1.5), _
        '    RulerStyle:=wdAdjustNone
            
       ' With .Range.Font
       '     .Name = "Arial"
       '     .Size = 14
       '     .Color = RGB(100, 149, 237)
       ' End With
    'End With
    End With
    
    
    With wdApp
        .Visible = True
        .Activate
    End With
    Set wdTbl = Nothing: Set wdDoc = Nothing: Set wdApp = Nothing
    End Sub
    Attached Files
    Last edited by KjBox; December 15th, 2016, 06:51. Reason: current code added

  • #2
    Re: Pasting Excel Tables into word, automate; 60 USD

    I can have a look at this for you
    We now have a reputation system in place. It can be found on the 'Star' icon on the bottom left hand side of the post

    Comment


    • #3
      Re: Pasting Excel Tables into word, automate; 60 USD

      that'd be great; for now, the column with the codes isnt in the excel document; for the purpose of the task, you could create a dummy column if that works. Thank you

      Comment


      • #4
        Re: Pasting Excel Tables into word, automate; 60 USD

        @KjBox, were you able to take a look at this?
        Originally posted by KjBox View Post
        I can have a look at this for you

        Comment


        • #5
          Re: Pasting Excel Tables into word, automate; 60 USD

          Yes, how soon do you need a solution?
          We now have a reputation system in place. It can be found on the 'Star' icon on the bottom left hand side of the post

          Comment


          • #6
            Re: Pasting Excel Tables into word, automate; 60 USD

            ideally in the next 15-16 hours but if needed, I can wait until a day and a half; thanks again!
            Originally posted by KjBox View Post
            Yes, how soon do you need a solution?

            Comment


            • #7
              Re: Pasting Excel Tables into word, automate; 60 USD

              OK, I am tied up with other stuff right now but should be able to make your first deadline.
              We now have a reputation system in place. It can be found on the 'Star' icon on the bottom left hand side of the post

              Comment


              • #8
                Re: Pasting Excel Tables into word, automate; 60 USD

                Can you please manually make a Word Document showing how the macro needs to create the document, and attach the Document sample here.

                There is no Table on the "Port Char" sheet, how many columns should the this missing table have?

                Typically, how many rows of data would there be in each of the 4 tables?
                We now have a reputation system in place. It can be found on the 'Star' icon on the bottom left hand side of the post

                Comment


                • #9
                  Re: Pasting Excel Tables into word, automate; 60 USD

                  I've attached the sample here; the inferences table, the objective, investor profile and portfolio char tables have 1 column each.
                  The table with 5columns has been coded to include color formatting for each row
                  Each of these tables will have at least 150 data points on excel; but each data point will correspond to one word document.
                  As such, each table in one document will have around 4-5 rows
                  Originally posted by KjBox View Post
                  Can you please manually make a Word Document showing how the macro needs to create the document, and attach the Document sample here.

                  There is no Table on the "Port Char" sheet, how many columns should the this missing table have?

                  Typically, how many rows of data would there be in each of the 4 tables?
                  Attached Files

                  Comment


                  • #10
                    Re: Pasting Excel Tables into word, automate; 60 USD

                    Your file is ready. I will PM you with my PayPal details and attach the fie here upon receipt of payment.
                    We now have a reputation system in place. It can be found on the 'Star' icon on the bottom left hand side of the post

                    Comment


                    • #11
                      Re: Pasting Excel Tables into word, automate; 60 USD

                      Payment received, many thanks.

                      File attached.
                      Attached Files
                      We now have a reputation system in place. It can be found on the 'Star' icon on the bottom left hand side of the post

                      Comment

                      Working...
                      X