1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Populate Word Doc from Excel (Multiple Rows, Select Columns)

Discussion in 'Business Applications' started by mbryanr, Mar 21, 2012.

Thread Status:
Not open for further replies.
Advertisement
  1. mbryanr

    mbryanr Thread Starter

    Joined:
    Mar 21, 2012
    Messages:
    8
    Well..glad I found this forum. I would like to be able to populate a word document from an excel worksheet, with the ability to select (highlight) multiple rows in excel and have those rows transferred to word, but only certain columns

    I found almost the right macro here:
    http://forums.techguy.org/business-applications/732913-solved-populate-word-document-excel-2.html

    Except you can only select (1) row.

    Any coding solutions on how to copy a range of rows? This will also require the .Text in the word paste to change....
    I see some hope with this code as well, but figuring it out may take me longer than someone that does this everyday.
    http://stackoverflow.com/questions/1725768/excel-macro-to-select-multiple-row
    Thank you.
     
  2. mbryanr

    mbryanr Thread Starter

    Joined:
    Mar 21, 2012
    Messages:
    8
    Using Excel and Word 2003.

    Edit: Attached sample spreadsheet (source data)
    Attached Sample Report (word)
     

    Attached Files:

  3. kumar1111

    kumar1111

    Joined:
    Mar 6, 2012
    Messages:
    50
    What do you need exactly, rows to be copied or certain columns to be copied or the highlighted range to be copied? Just let me know the exact requirement and I'll write the code for you :)
     
  4. mbryanr

    mbryanr Thread Starter

    Joined:
    Mar 21, 2012
    Messages:
    8
    Incredible, and thank you!

    I need the highlighted range to be copied; but then only specific columns input into the word document.

    Example Usage:
    User will highlight the area they want to export to word: See attached thumbnails.
    Select "Export to Word" Macro Button

    Then the macro would create a word document only pasting specific cells from each row. See attached word doc.
    Also sum total DT hrs for each specific "Line"
     

    Attached Files:

  5. mbryanr

    mbryanr Thread Starter

    Joined:
    Mar 21, 2012
    Messages:
    8
    Since they are looking primarily for dates...they could highlight the dates the user wants, which the macro would then select the rows from the highlighted date.
     
  6. mbryanr

    mbryanr Thread Starter

    Joined:
    Mar 21, 2012
    Messages:
    8
    I'm still stuck if anyone would like to provide a hint. Thanks in advance.
     
  7. kumar1111

    kumar1111

    Joined:
    Mar 6, 2012
    Messages:
    50
    I'll reply you by tomorrow :), I had my papers in between. Sorry, for the delay.
     
  8. kumar1111

    kumar1111

    Joined:
    Mar 6, 2012
    Messages:
    50
    Hi,

    Please find the attachment, also make sure to create a folder with a word doc which will launch every time you run the macro to paste the excel data on the word document. All you need to do follow the steps below

    ALT + F11
    Go to Module 1 and open it
    change your word doc path
    temp_path = "H:\Documents and Settings\Dragon\Desktop\New Folder\a.doc"
    Run the macro by ALT + F8

    Let me know, if you need further assistance on this macro...

    Thanks!
     

    Attached Files:

  9. mbryanr

    mbryanr Thread Starter

    Joined:
    Mar 21, 2012
    Messages:
    8
    Hi Kumar,
    Is there any way to only copy and paste the user highlighted rows and not all rows?

    This document is over 1500 rows, and they only need to report on a daily basis. So therefore, they will highlight a range of rows to export.
     
  10. mbryanr

    mbryanr Thread Starter

    Joined:
    Mar 21, 2012
    Messages:
    8
    Like this...except for multiple rows-

    ub export2Word(row)
    Dim oWD As Object
    Dim wdDoc As Object
    Dim TWB As Workbook, wsh, wsh1 As Worksheet
    Set TWB = ThisWorkbook
    On Error Resume Next
    Set oWD = GetObject(, "Word.Application")
    If Err.Number <> 0 Then Set oWD = CreateObject("Word.Application")
    Err.Clear

    Set wdDoc = oWD.documents.Add
    With wdDoc
    With .Range.paragraphs(1).Range
    .Text = "SFF Daily Production Report" & Chr(10)
    .Font.Size = 16
    .Font.Name = "Times New Roman"
    .Font.Bold = True
    .Font.Underline = True
    .ParagraphFormat.Alignment = 0
    End With
    With .Range.paragraphs(2).Range
    .Text = "Summary Comments" & Chr(10) & Chr(10) & Chr(10) & Chr(10)
    .Font.Size = 14
    .Font.Underline = True
    .ParagraphFormat.TabStops.ClearAll
    .DefaultTabStop = Application.CentimetersToPoints(1)
    .ParagraphFormat.TabStops.Add Position:=Application.CentimetersToPoints(4), _
    Alignment:=0, Leader:=0
    End With
    With .Range.paragraphs(5).Range
    .Text = Rows(iLastRow).EntireRow
    .Font.Size = 13
    .Font.Name = "Times New Roman"
    .Font.Bold = True
    .ParagraphFormat.Alignment = 0
    End With
    End With

    oWD.Visible = True
    wdDoc.Activate

    End Sub

    Private Sub cmdExport2Word_Click()
    Dim iLastRow As Integer
    If Selection.Value = "" Then
    MsgBox "There is nothing to export!" & Chr(10) & "Select a cell with data from column B," & Chr(10) & "and try again"
    Exit Sub
    End If

    If MsgBox("Data from the range A" & row & ": H" & row & " will be exported to Word!" & Chr(10) & _
    "Do you wanna continue", vbYesNo) = vbYes Then
    Call export2Word(row)
    End If
    iLastRow = ActiveSheet.UsedRange.Rows.Count
    End Sub
     
  11. mbryanr

    mbryanr Thread Starter

    Joined:
    Mar 21, 2012
    Messages:
    8
    Alright...I changed my approach and created a pivot table that the end-user will utilize to create their initial report. Then I copy and paste the pivot table to a word bookmark, which allows me to fix the location of the pivot table in word.

    I used the code here, and modified it for my usage:
    http://www.vbaexpress.com/kb/getarticle.php?kb_id=133

    Many thanks to Kumar for providing the code. You should be commended for doing the work you do. Impressive, and I have put your code to use in another report I needed to create.
     
  12. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/1046083

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice