How to programmatically create a document with Excel data?

[futureme]

Let's say I have an Excel spreadsheet with 3 columns (Name, Wife, date of death).

Let's say I want to create a Word document that takes those three columns worth of date and prints it to the document like this:

John Doe married Jane Jones and he died 1 Apr 2014.

Sometimes, however, the person in the spreadsheet never married so the Wife cell for this record is blank in which case the output would be:

John Doe died 1 Apr 2014.

What would be the best way to create the document? The output will be 8 pages worth of records. Would the easiest way be to send the Excel data to Word or some other method? I don't know how to reference the Excel data in Word vba so my first question is how do I reference Excel cells in vba in Word?

Or is there an easier way to do this. I can't do it via Mail Merge or similar because there are going to be about 25 columns worth of data and if certain cells are empty then I need to change the way the data is "described" when it's output.

I've used VBA quite a bit over the years, but I'm not an expert. That said, I've never tried to do what I'm wanting to do here so I just don't know where to start.

 

[ParamRay]

Here you go. This macro goes in the same workbook as your data.

IMPORTANT: Make sure you change the line where indicated!

Public Sub CreateWordDoc()

' Assumptions:
' 1) Name of person is column A (mandatory)
' 2) Name of spouse is column B (optional)
' 3) Date deceased is column C (mandatory)
' 4) Headers are row 1, with data starting row 2

  Const wdDoNotSaveChanges = 0
  Const strSHEET_NAME = "Data"  '<--- IMPORTANT: Set name of sheet containing data
  
  Dim strLineOfText As String
  Dim blnNewApp As Boolean
  Dim blnError As Boolean
  Dim wordApp As Object
  Dim wordDoc As Object
  Dim j As Long
  
  On Error Resume Next
  Set wordApp = GetObject(, "Word.Application")
  
  On Error GoTo ErrorHandler
  If wordApp Is Nothing Then
    Set wordApp = CreateObject("Word.Application")
    blnNewApp = True
  End If
  
  Set wordDoc = wordApp.Documents.Add()
  
  With ThisWorkbook.Sheets(strSHEET_NAME)
    For j = 2 To .Cells(.Rows.Count, "A").End(xlUp).Row
      strLineOfText = .Cells(j, "A").Text
      If Not IsEmpty(.Cells(j, "B").Value) Then
        strLineOfText = strLineOfText & " married " & .Cells(j, "B").Text & " and"
      End If
      strLineOfText = strLineOfText & " died on " & Format(.Cells(j, "C").Value, "d mmm yyyy")
      wordApp.Selection.TypeText strLineOfText
      wordApp.Selection.TypeParagraph
    Next j
  End With
  
  wordApp.Visible = True
  AppActivate wordApp.Caption
    
ExitHandler:
  On Error Resume Next
  If blnError Then
    wordDoc.Close wdDoNotSaveChanges
    If blnNewApp Then
      wordApp.Quit
    End If
  End If
  Set wordDoc = Nothing
  Set wordApp = Nothing
  Exit Sub
  
ErrorHandler:
  blnError = True
  MsgBox Err.Description, vbExclamation
  Resume ExitHandler
End Sub

 

[futureme]

Thanks so much posting that! This should get me started.

 

[futureme]

Here you go. This macro goes in the same workbook as your data.

IMPORTANT: Make sure you change the line where indicated!

Everything worked perfectly. Thanks again for posting that code. I changed it to add my multitude of nested if...then...else statements to take care of all possibilities and it outputs to a Word doc perfectly.

If I can get one more thing added I'll be good to go. How can I make the macro ONLY output a specific range of rows from the spreadsheet to Word? I think I mentioned that this is for a bunch of records I'm transcribing for a scholarly journal and it's going to literally take around 5 years to publish everything if the editors run 8 pages per issue of the journal. So, the spreadsheet will contain ALL records that I've transcribed and I need to be able to select, for example, rows 101 through 301 for this issue then rows 302 to 504 for the next issue, etc. Selecting rows with my mouse before running the macro might be the way to go?

 

[futureme]

Never mind, I figured it out this morning.