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

Why not just mail merge?

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.