Change Hypenlink Automatically
 

I have a macro which does the following:

1.- Save the excel template file (template.xls) as
WO = Worksheets("summary BLR").Range("M10")
myDateTime = Format(Worksheets("summary BLR").Range("M9").Value, "yyyymmdd")
Filename = "" & WO & "_grdprp_" & myDateTime & ""
Progname = "C:\form\" & Filename & ".xls"
ActiveWorkbook.SaveCopyAs Progname
The filename is created from the template cells

2.- Open an word template file (Template.doc)

3.- Save the template word file as the save name given in the step 1
Dim sDocName As String
sDocName = Replace(sPath, ".xls", ".doc")
ActiveDocument.SaveAs sDocName

4.- Change the sources and update the link from the file created in the step 1

5.- Close the Template.xls

However, I have a hyperlink rectangular in the Template.xls linked to the
Template.doc that I want to change the name automatically to the new word
document saved in the step 3 (the path is the same)

Could you please help me to build the statement for this step?

Thanks in advance.
Maperalia

Change Hypenlink Automatically
 

Can you record a macro in MSWord when you change a hyperlink. Then you'll have
the MSWord code that could be added to your excel code.



maperalia wrote:

I have a macro which does the following:

1.- Save the excel template file (template.xls) as
WO = Worksheets("summary BLR").Range("M10")
myDateTime = Format(Worksheets("summary BLR").Range("M9").Value, "yyyymmdd")
Filename = "" & WO & "_grdprp_" & myDateTime & ""
Progname = "C:\form\" & Filename & ".xls"
ActiveWorkbook.SaveCopyAs Progname
The filename is created from the template cells

2.- Open an word template file (Template.doc)

3.- Save the template word file as the save name given in the step 1
Dim sDocName As String
sDocName = Replace(sPath, ".xls", ".doc")
ActiveDocument.SaveAs sDocName

4.- Change the sources and update the link from the file created in the step 1

5.- Close the Template.xls

However, I have a hyperlink rectangular in the Template.xls linked to the
Template.doc that I want to change the name automatically to the new word
document saved in the step 3 (the path is the same)

Could you please help me to build the statement for this step?

Thanks in advance.
Maperalia

--

Dave Peterson