Mail Merge?

[G-Matriix]

Hello All,

What I am trying to do is simular to a mail merge. I want to pull data from a excel sheet and push the data to a word document. For each record create a new word document. I have it working with a excel to excel document but I am not sure to to push the data from excel to word and then save the word for each record. (if this is making any sense)

Here is the code I started with:

Code:
Sub Create_SteFiles()
Dim SiteName, SaveAsName As String
Dim SiteID, LAT, LON, StreetAddress, City, State, ZIPCode As String
Dim CounterRange, MaxValueRange As Integer
MaxValueRange = 69
For CounterRange = 2 To MaxValueRange
Workbooks.Open Filename:="C:\Site Survey Form.xls"
'****Clears out all the variable Data for the next Cycle *****
SiteName = ""
SiteID = ""
LAT = ""
LON = ""
StreetAddress = ""
City = ""
State = ""
ZIPCode = ""
SaveAsName = ""
TransferString = ""
'*****************************************************
Windows("HI Market Tracker.xls").Activate
'************Pull Data From the spreadsheet Here ******
SiteName = Range("A" + Trim(Str(CounterRange))).Value
SiteID = Range("B" + Trim(Str(CounterRange))).Value
LAT = Range("F" + Trim(Str(CounterRange))).Value
LON = Range("G" + Trim(Str(CounterRange))).Value
StreetAddress = Range("K" + Trim(Str(CounterRange))).Value
City = Range("L" + Trim(Str(CounterRange))).Value
State = Range("M" + Trim(Str(CounterRange))).Value
ZIPCode = Range("N" + Trim(Str(CounterRange))).Value
'*****************************************************
Windows("Site Survey Form.xls").Activate
'************Push Data to the spreadsheet Here ******
Range("F10").Value = Trim(SiteName)
Range("C10").Value = Trim(SiteID)
Range("F13").Value = Trim(LAT)
Range("H13").Value = Trim(LON)
Range("C12").Value = Trim(StreetAddress)
Range("H12").Value = Trim(City)
Range("J12").Value = Trim(State)
Range("C13").Value = Trim(ZIPCode)
'*****************************************************
SaveAsName = "C:\_LC_Sites\" + SiteID + ".xls"
ActiveWorkbook.SaveAs Filename:=SaveAsName
ActiveWorkbook.Close
'Workbooks.Open Filename:=SaveAsName
'Windows(Sitename + ".xls").Activate
'Range("C8").Value = Sitename
'ActiveWorkbook.Save
Next CounterRange
'ActiveWorkbook.SaveAs Filename:=SaveAsName
End Sub

Any Ideas

Thanks!!

 

[Kenneth Hobson]

It is a bit easier to merge all or some records to create one DOC file.

However, if you want to do one DOC per one XLS row, then the formfield method might be best. See:
http://www.mrexcel.com/forum/showthread.php?p=1639696
http://www.mrexcel.com/forum/showthread.php?t=333200

 

[G-Matriix]

Hello Kenneth,

I have been trying to get this to work and I keep getting "out of subscript" Here is the code...what is wrong?

Private Sub CommandButton2_Click()
Dim wdApp As Object, wd As Object, ac As Long, ws As Worksheet
Set ws = Worksheets("HI Market Tracker.xls")
On Error Resume Next
Set wdApp = GetObject(, "Word.Application")
If Err.Number <> 0 Then
Set wdApp = CreateObject("Word.Application")
End If
On Error GoTo 0
Set wd = wdApp.Documents.Open("C:\Final Mile Site Survey.doc")
wdApp.Visible = True
With wd
.formfields("Text39").Result = ws.Range("A2").Value
.formfields("SiteID").Result = ws.Range("B2").Value
.formfields("Text14").Result = ws.Range("C2").Value
.formfields("Text15").Result = ws.Range("D2").Value
.formfields("Text33").Result = ws.Range("E2").Value
.formfields("Text34").Result = ws.Range("F2").Value
.formfields("Text35").Result = ws.Range("G2").Value
.formfields("Text13").Result = ws.Range("H2").Value

End With
Set wd = Nothing
Set wdApp = Nothing
End Sub

 

[Kenneth Hobson]

You did not set the worksheet object reference properly.

Set ws = Worksheets("Set ws = Worksheets("HI Market Tracker.xls")
")

Should go something like:

Set ws = ThisWorkbook.Worksheets("Sheet1")

Or,

Set ws = Workbooks("HI Market Tracker.xls").Worksheets("YourSheetNameHere")

 

[G-Matriix]

Hey...thanks alot!

that worked!! Now I need it so loop through each row. I will try at the end of each loop save the document...close it and loop again until it is finished.

still alittle green behind the ears with this...
If you have any suggestions I am all hears....

thanks again

 

[Kenneth Hobson]

If you want to create multiple DOC files, I guess that would be fine. You might use FileCopy() to copy the master DOC.

The easier method is to create one DOC using the traditional mail merge in the DOC's vba from Word. Of course we can do the mail merge from Excel. It just takes a bit more work to set the Word references.