Macro that creates a Word Doc

[EvonS]

Hello everyone. I'd like to create a macro that creates a word document that contains the contents of cells that I specify. Can you suggest any articles or code for me to get started.

Please keep in mind that I am far from being a VBA expert. I know enough to read the code and understand what is happening for the most part but correct syntax isn't my strong point. I've gotten by so far by copying and editing code to suit my needs. Thanks in advance for your help.

 

[NdNoviceHlp]

I'll get U started... google Word references and Word early vs late binding. Also, U need to understand that Word is different than XL. U need to tell it exactly where your going to put information. U can always just place it at the document selection but if U want to put it in a table or some other specific spot.... U have to either have a document (template) with a table or U have to build one on the fly... ie. vba insert a table (again at a certain spot) then place your data in it. Also, only text/strings can be inserted so if U try to place an integer in the document U will error. Anyways, there's lots of code out there. I'm guessing if U searched my threads that U would find some that might even do what U want. HTH. Dave

 

[EvonS]

I'll get U started... google Word references and Word early vs late binding. Also, U need to understand that Word is different than XL. U need to tell it exactly where your going to put information. U can always just place it at the document selection but if U want to put it in a table or some other specific spot.... U have to either have a document (template) with a table or U have to build one on the fly... ie. vba insert a table (again at a certain spot) then place your data in it. Also, only text/strings can be inserted so if U try to place an integer in the document U will error. Anyways, there's lots of code out there. I'm guessing if U searched my threads that U would find some that might even do what U want. HTH. Dave

Thanks Dave. I will do some digging and post my results. Can you help speed up my search by giving me some specific links ?

 

[ParamRay]

Here's one way to do it, with lots of comments for explanation:

Sub CellsToWord()
  Const wdDoNotSaveChanges = 0
  Dim objWordApp As Object
  Dim objWordDoc As Object
  Dim objWordSel As Object
  Dim blnNewApp As Boolean
  Dim blnError As Boolean
  Dim cell As Excel.Range
  
  On Error Resume Next
' Get reference to Word app if already open.
  Set objWordApp = GetObject(, "Word.Application")
  
  On Error GoTo ErrHandler
' Launch Word if it wasn't already open.
  If objWordApp Is Nothing Then
    Set objWordApp = CreateObject("Word.Application")
    blnNewApp = True
  End If
  
' Check that some cells are selected in Excel.
  If Not TypeOf Selection Is Range Then
    MsgBox "Select some cells first.", vbExclamation
    GoTo ExitProc
  End If
  
' Create new Word document and get a
' reference to the insertion point.
  Set objWordDoc = objWordApp.Documents.Add
  Set objWordSel = objWordApp.Selection
  
' Loop through the selected cells in Excel and type
' each cell's contents into a Word paragraph.
  For Each cell In Selection.Cells
    objWordSel.TypeText cell.Text
    objWordSel.TypeParagraph
  Next cell
    
' Make the Word app visible and
' change the focus to its window.
  objWordApp.Visible = True
  AppActivate objWordApp.Caption
  
ExitProc:
  On Error Resume Next
' If we launched a new Word app and an
' error occurred, then close it.
  If blnError Then
    If blnNewApp Then
      objWordApp.Quit wdDoNotSaveChanges
    Else
      objWordDoc.Close wdDoNotSaveChanges
    End If
  End If
  
  Set objWordApp = Nothing
  Set objWordDoc = Nothing
  Set objWordSel = Nothing
  Exit Sub
  
ErrHandler:
' If an error occurred, notify the user.
  MsgBox Err.Description, vbExclamation
  blnError = True
  Resume ExitProc
End Sub

 

[EvonS]

Here's one way to do it, with lots of comments for explanation:

Sub CellsToWord()
  Const wdDoNotSaveChanges = 0
  Dim objWordApp As Object
  Dim objWordDoc As Object
  Dim objWordSel As Object
  Dim blnNewApp As Boolean
  Dim blnError As Boolean
  Dim cell As Excel.Range
  
  On Error Resume Next
' Get reference to Word app if already open.
  Set objWordApp = GetObject(, "Word.Application")
  
  On Error GoTo ErrHandler
' Launch Word if it wasn't already open.
  If objWordApp Is Nothing Then
    Set objWordApp = CreateObject("Word.Application")
    blnNewApp = True
  End If
  
' Check that some cells are selected in Excel.
  If Not TypeOf Selection Is Range Then
    MsgBox "Select some cells first.", vbExclamation
    GoTo ExitProc
  End If
  
' Create new Word document and get a
' reference to the insertion point.
  Set objWordDoc = objWordApp.Documents.Add
  Set objWordSel = objWordApp.Selection
  
' Loop through the selected cells in Excel and type
' each cell's contents into a Word paragraph.
  For Each cell In Selection.Cells
    objWordSel.TypeText cell.Text
    objWordSel.TypeParagraph
  Next cell
  
' Make the Word app visible and
' change the focus to its window.
  objWordApp.Visible = True
  AppActivate objWordApp.Caption
  
ExitProc:
  On Error Resume Next
' If we launched a new Word app and an
' error occurred, then close it.
  If blnError And blnNewApp Then
    objWordApp.Quit wdDoNotSaveChanges
  End If
  
  Set objWordApp = Nothing
  Set objWordDoc = Nothing
  Set objWordSel = Nothing
  Exit Sub
  
ErrHandler:
' If an error occurred, notify the user.
  MsgBox Err.Description, vbExclamation
  blnError = True
  Resume ExitProc
End Sub

Awesome. I will try this first thinking tomorrow. Will it place the contents in a table? Can you give me an example of a line of code that uses a predifined range instead of having to select it oneself?

Thanks again

 

[Macropod]

Depending on what you're trying to achieve and how your data are structured, a mailmerge may be the way to go. Mailmerges can be run manually from Word or automated from Excel. Ultimately, we'd need to know more about how the source cells are to be determined and what row/column relationships they might have from one iteration to the next. Some possible solutions don't require any code at all.

PS: I've moved your thread to the General Excel Discussion & Other Questions forum, which is more appropriate for the subject matter and where you'll find plenty of other threads on automating Word from Excel.

 

[EvonS]

Depending on what you're trying to achieve and how your data are structured, a mailmerge may be the way to go. Mailmerges can be run manually from Word or automated from Excel. Ultimately, we'd need to know more about how the source cells are to be determined and what row/column relationships they might have from one iteration to the next. Some possible solutions don't require any code at all.

PS: I've moved your thread to the General Excel Discussion & Other Questions forum, which is more appropriate for the subject matter and where you'll find plenty of other threads on automating Word from Excel.

Thanks. I'm familiar with mail merge. My goal here is to create a button that will allow users of this file to easily create a word document from a range of cells that is already populated based on dropdown selections from another tab.

My next goal will be to create a button that will creates an email based on the contents of a predefined range of cells but I'm not ready to go there yet.

Thanks again

 

[Macropod]

My goal here is to create a button that will allow users of this file to easily create a word document from a range of cells that is already populated based on dropdown selections from another tab.

If that range of cells is all on one row, an automated mailmerge for that one row might be appropriate. The macro coding, too, could be much less involved and require much less maintenance effort than trying to code a macro to populate different parts of a Word document. For some code you might adapt, see: https://www.mrexcel.com/forum/gener...vidual-pdf-files-post4796480.html#post4796480

 

[EvonS]

If that range of cells is all on one row, an automated mailmerge for that one row might be appropriate. The macro coding, too, could be much less involved and require much less maintenance effort than trying to code a macro to populate different parts of a Word document. For some code you might adapt, see: https://www.mrexcel.com/forum/gener...vidual-pdf-files-post4796480.html#post4796480

Thanks for the code you provided for me works perfectly. Unfortunately, the data spans two columns a nd multiple rows so I'm not sure the link you sent would help. The number of rows will vary day to day so I know I will have to stick an if statement in there somewhere.

Is there any way to keep or set the formatting of the text? (bolds, underline, tables, ect.)?

 

[Macropod]

The only way you can preserve cell formatting from Excel is via either OLE linking or Copy/Paste. Since "the data spans two columns a nd multiple rows", OLE linking might suit. To do that, fist select a nominal two-column by n-row range in Excel and name it. If you then copy that range and paste it into Word using Paste Special>Paste Link, with a suitable paste format, your formatting will be preserved. From then on, simply updating the range the name applies to in Excel, or updating the content of that range, will result in the linked range in Word updating to reflect the Excel updates.