I need to produce a document (preferably in Word) that has fields that pull
data from an excel spreadsheet.

I'm producing a report in memo format that remains essentially the same
except for some data, such as number of people, some dollar amounts, etc.

Is there a way to have a running link in word, where I can change the name
of the file linked, and it pulls the data from the right cells, kind of like
a paste link in excel?

I've not done it in a while, but do a search for MAIL MERGE. That should get
you what you want.
--
HTH,
Barb Reinhardt



"Patrick" wrote:

I need to produce a document (preferably in Word) that has fields that pull
data from an excel spreadsheet.

I'm producing a report in memo format that remains essentially the same
except for some data, such as number of people, some dollar amounts, etc.

Is there a way to have a running link in word, where I can change the name
of the file linked, and it pulls the data from the right cells, kind of like
a paste link in excel?

I tried that, but the merge has to come from a SQL or other DB control. I
need to get the data from Excel.

"Barb Reinhardt" wrote:

I've not done it in a while, but do a search for MAIL MERGE. That should get
you what you want.
--
HTH,
Barb Reinhardt



"Patrick" wrote:

I need to produce a document (preferably in Word) that has fields that pull
data from an excel spreadsheet.

I'm producing a report in memo format that remains essentially the same
except for some data, such as number of people, some dollar amounts, etc.

Is there a way to have a running link in word, where I can change the name
of the file linked, and it pulls the data from the right cells, kind of like
a paste link in excel?

http://www.mvps.org/dmcritchie/excel/mailmerg.htm
--
HTH,
Barb Reinhardt



"Patrick" wrote:

I tried that, but the merge has to come from a SQL or other DB control. I
need to get the data from Excel.

"Barb Reinhardt" wrote:

I've not done it in a while, but do a search for MAIL MERGE. That should get
you what you want.
--
HTH,
Barb Reinhardt



"Patrick" wrote:

I need to produce a document (preferably in Word) that has fields that pull
data from an excel spreadsheet.

I'm producing a report in memo format that remains essentially the same
except for some data, such as number of people, some dollar amounts, etc.

Is there a way to have a running link in word, where I can change the name
of the file linked, and it pulls the data from the right cells, kind of like
a paste link in excel?

Thanks, but this document is very outdated. I'm using 2003 at work and 2007
at home. The mail merge has changed since these directions were printed.

"Barb Reinhardt" wrote:

http://www.mvps.org/dmcritchie/excel/mailmerg.htm
--
HTH,
Barb Reinhardt



"Patrick" wrote:

I tried that, but the merge has to come from a SQL or other DB control. I
need to get the data from Excel.

"Barb Reinhardt" wrote:

I've not done it in a while, but do a search for MAIL MERGE. That should get
you what you want.
--
HTH,
Barb Reinhardt



"Patrick" wrote:

I need to produce a document (preferably in Word) that has fields that pull
data from an excel spreadsheet.

I'm producing a report in memo format that remains essentially the same
except for some data, such as number of people, some dollar amounts, etc.

Is there a way to have a running link in word, where I can change the name
of the file linked, and it pulls the data from the right cells, kind of like
a paste link in excel?

I've done it with 2003. I don't yet have 2007 so can't help you with that.




"Patrick" wrote:

Thanks, but this document is very outdated. I'm using 2003 at work and 2007
at home. The mail merge has changed since these directions were printed.

"Barb Reinhardt" wrote:

http://www.mvps.org/dmcritchie/excel/mailmerg.htm
--
HTH,
Barb Reinhardt



"Patrick" wrote:

I tried that, but the merge has to come from a SQL or other DB control. I
need to get the data from Excel.

"Barb Reinhardt" wrote:

I've not done it in a while, but do a search for MAIL MERGE. That should get
you what you want.
--
HTH,
Barb Reinhardt



"Patrick" wrote:

I need to produce a document (preferably in Word) that has fields that pull
data from an excel spreadsheet.

I'm producing a report in memo format that remains essentially the same
except for some data, such as number of people, some dollar amounts, etc.

Is there a way to have a running link in word, where I can change the name
of the file linked, and it pulls the data from the right cells, kind of like
a paste link in excel?

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 a
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'.



"Patrick" wrote:

I need to produce a document (preferably in Word) that has fields that pull
data from an excel spreadsheet.

I'm producing a report in memo format that remains essentially the same
except for some data, such as number of people, some dollar amounts, etc.

Is there a way to have a running link in word, where I can change the name
of the file linked, and it pulls the data from the right cells, kind of like
a paste link in excel?