Solved

Copy data from Excel to Word

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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

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

Microsoft Office Picture Manager was included in Office 2003, 2007, and 2010, but not in Office 2013. Users had hopes that it would be in Office 2016/Office 365, but it is not. Fortunately, the same zero-cost technique that works to install it with …
Entering a date in Microsoft Access can be tricky. A typo can cause month and day to be shuffled, entering the day only causes an error, as does entering, say, day 31 in June. This article shows how an inputmask supported by code can help the user a…
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

820 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