Solved

Copy data from Excel to Word

Posted on 2013-02-01
5
376 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 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

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

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

Recently Microsoft released a brand new function called CONCAT. It's supposed to replace its predecessor CONCATENATE. But how does it work? And what's new? In this article, we take a closer look at all of this - we even included an exercise file for…
My experience with Windows 10 over a one year period and suggestions for smooth operation
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

809 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