Loading
Ozgrid Excel Help & Best Practices Forums

Excel Training / Excel Dashboards Reports



Page 1 of 2 1 2 LastLast
Results 1 to 10 of 11

Thread: Pasting Excel Tables into word, automate; 60 USD

  1. #1
    Join Date
    15th December 2016
    Posts
    5

    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 :

    VB:
    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. REMINDER! OzGrid accepts no responsibility for ANY adverse effects as a result from downloading attached files. ALWAYS run an up-to-date virus scan and disable macros.
    Last edited by KjBox; December 15th, 2016 at 06:51. Reason: current code added

  2. #2
    Join Date
    10th January 2007
    Location
    Borneo
    Posts
    3,208

    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

  3. #3
    Join Date
    15th December 2016
    Posts
    5

    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

  4. #4
    Join Date
    15th December 2016
    Posts
    5

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

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

  5. #5
    Join Date
    10th January 2007
    Location
    Borneo
    Posts
    3,208

    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

  6. #6
    Join Date
    15th December 2016
    Posts
    5

    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!
    Quote Originally Posted by KjBox View Post
    Yes, how soon do you need a solution?

  7. #7
    Join Date
    10th January 2007
    Location
    Borneo
    Posts
    3,208

    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

  8. #8
    Join Date
    10th January 2007
    Location
    Borneo
    Posts
    3,208

    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

  9. #9
    Join Date
    15th December 2016
    Posts
    5

    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
    Quote 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. REMINDER! OzGrid accepts no responsibility for ANY adverse effects as a result from downloading attached files. ALWAYS run an up-to-date virus scan and disable macros.

  10. #10
    Join Date
    10th January 2007
    Location
    Borneo
    Posts
    3,208

    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

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Possible Answers

  1. Copying and Pasting formulae between similar Excel tables
    By stratoman in forum Excel General
    Replies: 4
    Last Post: September 10th, 2015, 20:48
  2. Replies: 25
    Last Post: September 14th, 2012, 10:32
  3. Excel to Word, Mail Merge Macro to Automate
    By JSpectrum in forum Excel General
    Replies: 1
    Last Post: April 28th, 2012, 08:14
  4. Automate Pasting To Word
    By vcsush in forum Excel and/or Word Help
    Replies: 3
    Last Post: October 14th, 2007, 19:31

Bookmarks

Posting Permissions

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