Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Copy data from Excel to Word

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

Office 365 Training for IT Pros

Learn how to provision tenants, synchronize on-premise Active Directory, implement Single Sign-On, customize Office deployment, and protect your organization with eDiscovery and DLP policies.  Only from Platform Scholar.

Question has a verified solution.

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

Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
Ever visit a website where you spotted a really cool looking Font, yet couldn't figure out which font family it belonged to, or how to get a copy of it for your own use? This article explains the process of doing exactly that, as well as showing how…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
If you’ve ever visited a web page and noticed a cool font that you really liked the look of, but couldn’t figure out which font it was so that you could use it for your own work, then this video is for you! In this Micro Tutorial, you'll learn yo…

671 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