''''''''''''''''''''''''''''' ENGLISHSPACEDOG '''''''''''''''''''''''''''''''''''''''''''
Option Explicit '''''''''' THIS IS VBA IN EXCEL 2010 ''''''''''''''''''''''''''''''''''''''''''
Option Base 1 ' means that arrays start from 1, not 0.
Sub ExportExcelDataToWordDocument()
'Dim wdExcelApp As Application 'Excel is the default library (optional)
Dim wdWordApp As Word.Application 'Word app
Dim theString As String
Dim theObject As Object
' Creating a new instance of Word
Set wdWordApp = New Word.Application 'instantiate a new instance of Word 2010
With wdWordApp
' Making Word Visible on the screen
.Visible = True 'iff false, document is invisible.
.Activate ' make it the top pane, bring it to the front.
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' create a new Word Document based on the specified template
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
.Documents.Add "C:\SHTUFF\MY FAVORITE PLAYER.dotm"
Sheet2.Activate
'''''''''''''''''''''''''''''''''''''''''''''''''''''
'put the value of cell B2 into Bookmark "b1".
theString = Range("B2").Value 'cast to string
Set theObject = CreateObject("new:{1C3B4210-F441-11CE-B9EA-00AA006B1A69}")
theObject.SetText theString
theObject.PutInClipboard 'copy from cell B2 to clipboard.
Set theObject = Nothing
.Selection.GoTo what:=-1, Name:="b1" ' -1 means "wdgotobookmark"
.Selection.Paste 'paste from the clipboard to the Word Doc.
'''''''''''''''''''''''''''''''''''''''''''''''''''''
'put the value of cell C2 into Bookmark "b2".
theString = Range("C2").Value 'cast to string
Set theObject = CreateObject("new:{1C3B4210-F441-11CE-B9EA-00AA006B1A69}")
theObject.SetText theString
theObject.PutInClipboard 'copy from cell to clipboard.
Set theObject = Nothing
.Selection.GoTo what:=-1, Name:="b2" ' -1 means "wdgotobookmark"
.Selection.Paste 'paste from the clipboard to the Word Doc.
'''''''''''''''''''''''''''''''''''''''''''''''''''''
'put the value of cell A2 into Bookmark "b3".
theString = Range("A2").Value 'cast to string
Set theObject = CreateObject("new:{1C3B4210-F441-11CE-B9EA-00AA006B1A69}")
theObject.SetText theString
theObject.PutInClipboard 'copy from cell to clipboard.
Set theObject = Nothing
.Selection.GoTo what:=-1, Name:="b3" ' -1 means "wdgotobookmark"
.Selection.Paste 'paste from the clipboard to the Word Doc.
'''''''''''''''''''''''''''''''''''''''''''''''''''''''
' Save WORD Document
'''''''''''''''''''''''''''''''''''''''''''''''''''''''
Dim TheFileName As String
TheFileName = "C:\SHTUFF\MY FAVORITE PLAYER.docx"
'(SaveAs is for Office 2003 and earlier)
.ActiveDocument.SaveAs2 TheFileName
'replaces existing .doc iff exists
' Close Documents and Quit Word
.ActiveDocument.Close 'close .DOCx
.Quit 'exit Word
End With
'MEMORY CLEANUP
Set wdWordApp = Nothing 'garbage collection
'Set wdExcelApp = Nothing 'OPTIONAL
End Sub
'''''''''''''''''''''''''''''''''''
