VBA : how to import excel data to a word document?

  • ok here´s the scenario, i have an excel data sheet with 750 rows, i need to export each row to a unique word document (per row);preferably the word documents must be created on the fly; the word document have a design, so the information must be placed in the right position, i dont know if this has to be done with vba , i tried the field-link. excel.sheet 8 stuff, but i think this wont be all that i need to do this, any help is greatly appreciated, :)

  • Hi


    I have some code which uses a word document template with bookmarks enabled. An excel range is pasted into the word document at the declared bookmark. You must have refrenced Microsoft Word Object library in the VBA editor and the bookmarks are enabled in your word template.


    Sub createWordReportTSGI()
    On Error GoTo errorHandler
    Dim wdApp As Word.Application
    Dim myDoc As Word.Document
    Dim mywdRange As Word.Range


    Dim IncidentsRaisedLW As Excel.Range
    Dim IncidentsRaisedTW As Excel.Range
    Dim IncidentsClosedLW As Excel.Range
    Dim IncidentsClosedTW As Excel.Range
    Dim IncidentsOutstandingLW As Excel.Range
    Dim IncidentsOutstandingTW As Excel.Range


    Set wdApp = New Word.Application
    With wdApp
    .Visible = True
    .WindowState = wdWindowStateMaximize
    End With


    Set myDoc = wdApp.Documents.Add(Template:="G:\BG Reports\Weekly Team Report\Templates\WTSG Impact weekly summary.doc")


    Set IncidentsRaisedLW = Sheets("WTSG IMPACT").Range("B3")
    Set IncidentsRaisedTW = Sheets("WTSG IMPACT").Range("C3")
    Set IncidentsClosedLW = Sheets("WTSG IMPACT").Range("B4")
    Set IncidentsClosedTW = Sheets("WTSG IMPACT").Range("C4")
    Set IncidentsOutstandingLW = Sheets("WTSG IMPACT").Range("B5")
    Set IncidentsOutstandingTW = Sheets("WTSG IMPACT").Range("C5")


    With myDoc.Bookmarks


    .Item("IncidentsRLW").Range.InsertAfter IncidentsRaisedLW
    .Item("IncidentsRTW").Range.InsertAfter IncidentsRaisedTW
    .Item("IncidentsCLW").Range.InsertAfter IncidentsClosedLW
    .Item("IncidentsCTW").Range.InsertAfter IncidentsClosedTW
    .Item("IncidentsOLW").Range.InsertAfter IncidentsOutstandingLW
    .Item("IncidentsOTW").Range.InsertAfter IncidentsOutstandingTW


    End With



    errorHandler:
    Set wdApp = Nothing
    Set myDoc = Nothing
    Set mywdRange = Nothing


    End Sub


    Hope this may be of some use!


    Dani

  • Re: VBA : how to import excel data to a word document?


    Dani,


    I know your post is years old but waayyy to go! This problem has been wrecking my head for days - your code is so elegant and works to perfection. Nice one!


    Zarquon

  • Re: VBA : how to import excel data to a word document?


    Hi DaniB,


    I am using your script and it does work perfect, however i have 2 questions:
    -1 Is it possible when saving the .doc to give it a name from a cell?
    -2 Is it possible to make a loop for creating and saving the letters, meaning I have 60 rows and then I would like to see 60 letters and saved under 60 different file names from 60 different cell value?


    I hope you can help as I am struggling for days now, many thanks

  • Re: VBA : how to import excel data to a word document?


    Perhaps you didn't see this thread is 10 years old...?


    Regardless, the policy on this board is you do not post questions in other members threads. Start your own thread, give it an accurate and concise title that summarises your problem and explain your issue fully.


    If you think this, or any other, thread can help clarify your issue you can include a link to it by copying the URL from the address bar of your browser and pasting into your message.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!