?
Solved

Copy data from Excel to Word

Posted on 2013-02-01
5
Medium Priority
?
412 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
  • 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 77

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 2000 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

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Question has a verified solution.

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

With its various features, Office 365 can not only help you with your day-to-day business tasks, it can also do wonders for your marketing campaign.
In this article, I will demonstrate that how to do a PST migration from Exchange Server to Office 365. This method allows importing one single PST, or multiple PST's at once.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

578 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