Macro to change filename of the Hyperlink

M

maperalia

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top