Import Data into Word from Excel Using VBA


How we can import data into Word from Excel using VBA quickly and easily.

The first thing we need to do is open the MS Word application. So there are four situations to be taken care of while working wit MS Word from Excel:

  • The MS Word application may not be open
  • MS Word may be open but the document we wish to access may not be open
  • The Word file or document that we wish to access may be open
  • The Word file that we wish to open may have been deleted, renamed or moved to another folder and therefore not ‘exist’ for our VBA code
  • If the MS Word file is not found we need to tell the user that the file is not available

Now once we open the Word file we can paste the copied range into it. After that the Word document should be saved and finally the application should be closed. Also we need to take care of error handling otherwise the process of working wit Word from Excel to import data between the Microsoft applications causes lot of pain. Often you will get messages that create more confusion than clarity.

Watch the training video before you dive into the VBA code:


Watch the video on YouTube.

Sub ActivateWordTransferData()
Worksheets(“Sheet1”).Range(“A1:D4”).Copy
‘Declare Object variables for the Word application and file or document
Dim wdapp As Object, wddoc As Object
‘Declare a String variable for the file name and folder path
Dim strdocname As String
‘error handling is extremely important in making Excel work with Word
On Error Resume Next
‘GetObject needs two parameters. The first is optional
Set wdapp = GetObject(, “Word.Application”)
If Err.Number = 429 Then
Err.Clear
‘we create a new instance of MS Word
Set wdapp = CreateObject(“Word.Application”)
End If
‘Our application is made visible
wdapp.Visible = True
strdocname = “C:\our-inventory\inventory-report.docx”
‘we create a friendly message for the user
If Dir(strdocname) = “” Then
MsgBox “The file ” & strdocname & vbCrLf & “was not found ” & vbCrLf & “C:\our-inventory\.”, vbExclamation, “The document does not exist.”
Exit Sub
End If
‘we activate our MS Word instance
wdapp.Activate
Set wddoc = wdapp.Documents(strdocname)
‘we open MS Word file if it is not open
If wddoc Is Nothing Then Set wddoc = wdapp.Documents.Open(strdocname)
wddoc.Activate
wddoc.Range.Paste
wddoc.Save
wdapp.Quit
‘Here we free memory allocated to our two object variables
Set wddoc = Nothing
Set wdapp = Nothing
‘The following line of code removes the selection of range A1:D4 in Excel
Application.CutCopyMode = False
End Sub

Download sample file by clicking on the Excel:

12 thoughts on “Import Data into Word from Excel Using VBA

  1. JOYDEV SARDAR

    HII. I WANT LIVE DATA TO BE DOWNLOADED FROM SAP INTO MY EXCEL DIRECTLY IN MY OFFICE THROUGH VBA. I DON’T KNOW VBA PROGRAMMING. I WANT TO DOWNLOAD DATA FROM SAP (HAVING SPECIFIC TRANSACTION CODES HAVING SPECIFIC COLUMNS DATA) BACK INTO EXCEL OPERATING ONLY FROM EXCEL SHEET VIA VBA PROGRAMMING. SO THAT I CAN CREATE LATEST REPORT QUICKLY. KINDLY HELP ME.

    Reply
  2. A K Choudhary

    Hello,
    I have seen your video yesterday, it is really very useful for all of us who is working on word and excel in daily routine.
    Please advice, If i have one doc file in my drive then how can i search and replace text or number from the excel file cell text or number.

    Reply
  3. Hansa

    hi

    Your post is very interesting. i’ve tried it and it works perfectly. but one thing I want to insert my excel table to a specific page of an existing word document. Please advise.

    Thanks

    Reply
  4. Sakshi

    hi,

    i have created tables in my excel sheet… now i want to transfer that data into text document… that is whole table data get copied into notepad using a button. Thanks in advance.

    Reply
  5. Gary Crenshaw

    Hello Dinesh, I work in a Data Center in New England, and we have a process of creating Standard Operating Procedures called Standard Work Documents. They are the actual procedures our staff uses to complete daily tasks. I wanted to ask if there was a way to create a simple intake form that would export the data to MS Word? Once these Word documents “Standard Work Documents” are created we upload them into our SharePoint library with different titles. The problem we are having is there is no uniformity in the way people enter their data and although they try to follow the format as best they can, we usually end up with many kinds of “Standard Work Documents” that are harder on the eyes to follow. What I am trying to achieve is create a simple input form from either Excel or if possible from MS Word that can be a template that asks the users the basic questions almost like a survey would do and continues to through all the steps the user feels are necessary to complete this “Standard Work Document” till the user is done and hits the submit button. My hope is that like a survey it will format the data into a MS Word Document table with perfect uniformity so that I can published the”Standard Work Documents” into SharePoint. All of these forms should be identical and have the same wording with the exception of the the different steps (text entries) and objectives that change from procedure to procedure. I have a copy of a blank Standard Work item that I would like to send you if you can assist me and or put me on the right track to create the input form. Please advise I really admire your work and the way you explain your videos.

    Sincerely,
    Gary

    Reply
  6. Pingback: Import Data from Excel into Word Automatically Using VBA

  7. Pingback: Import Data from Excel into Word Automatically Using VBA - Online make jobs

  8. Pingback: Import Data from Excel into Word Automatically Using VBA | Coding Tweaks

  9. Rabea Shafique

    Dear Mr. Takyar,
    I´m writing to you from Germany with an excel problem using VBA. is it possible to transfer active data (multiple sheets) in which say “Technical Data” has to be updated periodically, into a word document ( that looks like a users manual) using a button (created through macros) “Export to Word.”
    The problem we are facing is that the formatting isnt as we would like it to be, as soon as that excel button generates the document into word, the whole formatting in the word template just looks like a mush of words.
    can this problem be solved, i desperately need guidance as I have taken over a project from an ex- colleague and I am not exactly a programmer.
    your help might save my job 🙂

    with kind regards,
    Rabea Shafique

    Reply

Leave a Reply

Your email address will not be published. Required fields are marked *