ExcelBanter - Creating a Dynamic Link from Excel to Word

Nevermind I was able to work it out.

This is what I came up with:
'________________________________________________
Dim afield as field
Dim counter As Integer

Dim iDay As Integer
Dim iMonth As Integer
Dim dtYesterday As Date
dtYesterday = DateAdd("d", -1, Now)
iDay = DatePart("d", dtYesterday)
iMonth = DatePart("m", dtYesterday)

Dim xMonth As String
Dim lastMonth As String
xMonth = MonthName(iMonth, True)
lastMonth = MonthName(DatePart("m", Date - 20), True)

counter = ActiveDocument.Fields.Count

For i = 286 To counter
Set afield = ActiveDocument.Fields(i)
If InStr(1, afield.Code.Text, xMonth, vbTextCompare) 0 Then
afield.Code.Text = Replace(afield.Code, xMonth, lastMonth, , , _
vbTextCompare)
End If
Next
'This searches through the field.code.text which is what the field code
looks like on my previous post, searches through and replaces it with the
correct month using a dateadd and monthname functions.
'_________________________________________________

"matt" wrote:

Hello,
So I have this word document that creates a report that is almost completely
automated except for one thing.
At the bottom of the document there are three graphs, each of the three
graphs is linked to one of three seperate Excel files. Each of the 3 excel
file has 12 sheets, one for each month.
What I want to see if I can do is create a dynamic link to the graph, in the
correct worksheet, for the correct month. Without having to manually delete
and relink each graph when the month changes.

i.e... So if the month is August I want the graph from the sheet named " Aug
'08 "

Here is an example of the field codes for one of the graphs in the doc.

{ LINK Excel.Sheet.8 "\\\\WCHWF70\\share\\Interactive\\Reporting\\Graph s
Charts 2008\\Sales.xls" "Aug '08![Sales.xls]Aug '08 Chart 1" \a \p }