After reading through your discussion with Barb Reinhardt I think maybe the
code below will give you a start. I'm not much on coding Word, so perhaps
you can get help with the Word coding part of it in those forums and make it
better, but the code here shows how to get information from diversely located
cells on a worksheet and put them into a document.
To explain my Word (2003) document some. I had one introductory paragraph
and then there were 3 paragraphs set up initially as:
Number of People: xx
Amount of Money: $ 99.99
Additional Text: xx
What this code does is locate each of those by finding the phrase and then
deleting the rest of the line and inserting the data obtained from the Excel
workbook. All of that stuff is what the folks in the Word support forums can
probably help you better with if you'll show them the code below.
This code was placed into a module in the Word document itself. One thing
you will need to do as you place this code into it is to use Tools |
References and make sure that you have a reference to the 'Microsoft Excel
11.0 Object Library' - that may be 'Microsoft Excel 12.0 Object Library' if
you're using Office 2007 vs 2003. Other references in my list are:
Visual Basic For Applications
Microsoft Word 11.0 Object Library
OLE Automation
Normal
Microsoft Office 11.0 Object Library
Here's the code I came up with - you'll need to change the path to the file,
the sheet name used and cell references within the code for your setup.
Sub ImportFromExcel()
'change this path to point to your Excel file
Const xlFileLoc = "C:\Docs and Sets\uname\My docs\ExcelForWordTest.xls"
Dim xlApp As Excel.Application
Dim xlWB As Excel.Workbook
Dim xlWS As Excel.Worksheet
Dim xlCell As Excel.Range
Dim valFromExcel As Variant ' accepts any type value
Set xlApp = CreateObject("excel.application")
xlApp.Workbooks.Open xlFileLoc
Set xlWB = xlApp.ActiveWorkbook
Set xlWS = xlWB.Worksheets("Sheet1") ' change as needed
valFromExcel = xlWS.Range("C3") ' get a value from Excel
Selection.HomeKey Unit:=wdStory ' go to start of doc
Selection.Find.ClearFormatting
With Selection.Find
.Text = "Number of People:"
.Replacement.Text = ""
.Forward = True
.Wrap = wdFindContinue
.Format = False
.MatchCase = False
.MatchWholeWord = False
.MatchWildcards = False
.MatchSoundsLike = False
.MatchAllWordForms = False
End With
Selection.Find.Execute
Selection.MoveRight Unit:=wdCharacter, Count:=1
Selection.EndKey Unit:=wdLine, Extend:=wdExtend
Selection.MoveLeft Unit:=wdCharacter, Count:=1, Extend:=wdExtend
Selection.Delete Unit:=wdCharacter, Count:=1
Selection.TypeText Text:=" " & valFromExcel
valFromExcel = xlWS.Range("D4") ' get a value from Excel
Selection.HomeKey Unit:=wdStory
Selection.Find.ClearFormatting
With Selection.Find
.Text = "Amount of Money:"
.Replacement.Text = ""
.Forward = True
.Wrap = wdFindContinue
.Format = False
.MatchCase = False
.MatchWholeWord = False
.MatchWildcards = False
.MatchSoundsLike = False
.MatchAllWordForms = False
End With
Selection.Find.Execute
Selection.MoveRight Unit:=wdCharacter, Count:=1
Selection.EndKey Unit:=wdLine, Extend:=wdExtend
Selection.MoveLeft Unit:=wdCharacter, Count:=1, Extend:=wdExtend
Selection.Delete Unit:=wdCharacter, Count:=1
Selection.TypeText Text:=" $ " & valFromExcel
valFromExcel = xlWS.Range("E5") ' get a value from Excel
Selection.HomeKey Unit:=wdStory
Selection.Find.ClearFormatting
With Selection.Find
.Text = "Additional Text:"
.Replacement.Text = ""
.Forward = True
.Wrap = wdFindContinue
.Format = False
.MatchCase = False
.MatchWholeWord = False
.MatchWildcards = False
.MatchSoundsLike = False
.MatchAllWordForms = False
End With
Selection.Find.Execute
Selection.MoveRight Unit:=wdCharacter, Count:=1
Selection.EndKey Unit:=wdLine, Extend:=wdExtend
Selection.MoveLeft Unit:=wdCharacter, Count:=1, Extend:=wdExtend
Selection.Delete Unit:=wdCharacter, Count:=1
Selection.TypeText Text:=" " & valFromExcel
'release Excel resources back to system
Set xlWS = Nothing
xlWB.Close
Set xlWB = Nothing
xlApp.Quit
Set xlApp = Nothing
End Sub
I hope this helps get you started on your way. All you'd have to do to
update your memo is to run the macro! It'll open Excel, open the workbook,
get the info, update the document, close the workbook and close Excel, all
with 'one-click'.
