Help with Macro

[DavidRocastle]

Hi i was wondering if someone could help me i currently have a macro whereby
a userform will appear. I enter a number in the field on the user form and
click insert. This will then update the textfield in every page of my
document. Please see below what the macro is currently.

Sub Update()

Dim Title As String
Dim frmTitle As UserForm1
Dim oStory As Range

Set frmTitle = New UserForm1

With frmTitle
.Show
ActiveDocument.BuiltInDocumentProperties("Title").Value = .Title.Text
End With

Unload frmTitle

Set frmTitle = Nothing

For Each oStory In ActiveDocument.StoryRanges
oStory.Fields.Update
If oStory.StoryType < wdMainTextStory Then
While Not (oStory.NextStoryRange Is Nothing)
Set oStory = oStory.NextStoryRange
oStory.Fields.Update
Wend
End If
Next oStory
Set oStory = Nothing
End Sub


What i want though is that after the once the value is entered in all the
fields on the document,
I then wish to change path to an excel file on the C:\Data.xls.
In this File there is a sheet1 with two columns.
Column A contains the name of word file that i have opened which has had the
textfield successfully updated thanks to the macro.
Column B will have the name of word files .doc.
I will offset my column A to find it's match in column B, take the cell
value in column B and put it into a search for the C:\Drive. Once it finds
the desired document it will then open it, update the textfield with the same
value entered in the in the original macro update userform and then print out
the .doc file.

Would any1 know how to do this or is a little OTT?

 

[DavidRocastle]

I know i am replying to my own message but i have tried to simplify my last
post. Here is my current code

Sub Update ()

Dim Title As String
Dim frmTitle As UserForm1
Dim oStory As Range

Set frmTitle = New UserForm1

With frmTitle
.Show
ActiveDocument.BuiltInDocumentProperties("Title").Value = .Title.Text
End With

Unload frmTitle

Set frmTitle = Nothing

For Each oStory In ActiveDocument.StoryRanges
oStory.Fields.Update
If oStory.StoryType < wdMainTextStory Then
While Not (oStory.NextStoryRange Is Nothing)
Set oStory = oStory.NextStoryRange
oStory.Fields.Update
Wend
End If
Next oStory
Set oStory = Nothing
End Sub

I am trying to implement my current macro as follows.

-> .LookIn = Change path G:\Forms\
-> .Filename = “Index.xlsâ€
-> Search Worksheet for column A (based on the file name of current
document opened- eg. The current file might be 00308.doc) to offset as to
column B to find correct value. $VariableName
-> Take the cell reference of column B and Open this file =
G:\Forms\$VariableName
-> Enter Title Value into textfield and print.