Create a Dynamic Link

[matt]

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 that are each linked to
three different excel files. Each 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.

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

When I view the field codes for the graphs it looks like this for the first
graph:

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

 

[matt]

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

I still have to clean it up a bit but thats the gist of it.