Solved

Copy data from Excel to Word

Posted on 2013-02-01
5
388 Views
Last Modified: 2013-02-03
Hi,

Please find the attached excel file named ‘Data in Excel’. It has once worksheet which has few data. I am trying to copy this data to word and would like macro to do this for me. There is a button placed in this worksheet and on click of this button the data gets copied to word. Please find the attached work document named ‘Current data in Word.doc’ for your reference.

However, I would like the data copied in a different way. Please find the attached word document named ‘Required data in Word.doc’ I have considered first two records and have shown you as an example. This should be done for all the records in this worksheet.
Please do the needful. It would be of great help if a macro does this in the required format.

Thank you.
Prashanth
Data-in-Excel.xlsx
Current-data-in-Word.doc
Required-data-in-Word.docx
0
Comment
Question by:pg1533
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
5 Comments
 
LVL 43

Expert Comment

by:Saqib Husain, Syed
ID: 38843699
Try this macro. Word must be open when you run it.

Sub CopyWorksheetsToWord()
    Dim wrd As Word.Application
    Dim doc As Document
    Set wrd = GetObject(, "word.application")
    Set doc = wrd.documents.Add
    For Each rw In ActiveSheet.UsedRange.Offset(1, 1).Rows
        strg = ""
        For Each cel In rw.Cells
            strg = strg & cel.Value & " "
        Next cel
        wrd.Selection.TypeText strg & vbCrLf & vbCrLf & vbCrLf
    Next rw
End Sub
0
 
LVL 76

Expert Comment

by:GrahamSkan
ID: 38843704
The current status is that the data is in the Word document as a table, You seem to want all the data from each row to be concatenated into its own text paragraph.

I can't find your macro, but presumably you have an object for the word document.

If it is called objDoc, then add this call after the copy has been done, and :
    '...
    FormatWordDoc objDoc
End Sub

Open in new window

and add this procedure. Note that it uses early binding, so you need to set a reference (Tools/References) to the Microsoft Word Object model.
Sub FormatWordDoc(Doc As Word.Document)
    Dim tbl As Word.Table
    Set tbl = Doc.Tables(1)
    
    tbl.Rows.First.Delete 'deleted unwanted row
    tbl.Columns.First.Delete 'and column
    tbl.ConvertToText " "
End Sub

Open in new window

0
 

Author Comment

by:pg1533
ID: 38848132
Hi Ssaqibh,

The strg value is not getting copied to the word application. I have the word application opened before I run this macro. Please do the needful.

I have attached the file for your reference.

Thank you,
Prashanth
Data-in-Excel.xlsm
0
 
LVL 43

Accepted Solution

by:
Saqib Husain, Syed earned 500 total points
ID: 38849087
Can you tell me what is happening?

When I ran the macro in your file it worked just fine. Except that it additionally copied the word "Strg" which you added yourself.
0
 

Author Comment

by:pg1533
ID: 38849121
My mistake...!! Apologies for it.

Fantastic..... It is really working fine. Thank you for all your time and support.

Have a great day!!!

Regards,
Prashanth
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
This article describes how to import an Outlook PST file to Office 365 using a third party product to avoid Microsoft's Azure command line tool, saving you time.
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

751 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question