VBA Script to generate Hyperlinks in Excel

[astephanov]

I am working on a project that would generate hyperlinks to reports located in my company's local network. All reports are in the following format:
<table style="border-collapse: collapse; width: 48pt;" width="64" border="0" cellpadding="0" cellspacing="0"><col style="width: 48pt;" width="64"><tr style="height: 12.75pt;" height="17"> <td class="xl65" style="height: 12.75pt; width: 48pt;" width="64" height="17">http://websrv2/~clearweb/thpclr_report/20090403/backoffice/BBVA.OPTFUT.customer.PositionCheck.20090403</td> </tr></table>The only portion of the link that changes is the date. I am using parsing formulas to extract the following strings:
http://websrv2/~clearweb/thpclr_report/
/backoffice/BBVA.OPTFUT.customer.PositionCheck
I also have a parsing formula that takes a user entered date and converts it to text string in yyyymmdd foramt.
To generate a link that updates, I am using "=HYPERLINK(CONCATENATE())
This works flawlessly and I have it implemented in many worksheets.
Now, I want to automate this process. So far, I have written a Macro that asks the user to paste a link in an input box. Text formulas then parse the link and extract the static parts. I now need a script that can generate the following:
HYPERLINK(CONCATENATE("http://websrv2/~clearweb/thpclr_report/",[cell reference for the date],"/backoffice/BBVA.OPTFUT.customer.PositionCheck",[cell reference for the date]),"user Defined name")
It is very important to use static text string in the formula for URL parts and cell reference for the date. This would allow the user to reuse the macro and generate multiple links. All links would update with the change of a single cell that hold the date.
I also need a script that would prompt the user for a link name that would be inserted in the formula.
Ideally, the macro would first create a new TAB that would contain the date reference and then it would prompt the user to start entering URLS and names for the links. The user should be able to stop and restart the process (now I probably want too much).

 

[westconn1]

assuming you want to enter this into a cell as a formula and your date is in cell a1
try like

Range("d6").Formula = "=HYPERLINK(CONCATENATE(""http://websrv2/~clearweb/thpclr_report/""," & Range("A1") & ",""/backoffice/BBVA.OPTFUT.customer.PositionCheck""," & Range("A1") & "),""user Defined name"")"

if your date is not in the required format already change to

Range("d6").Formula = "=HYPERLINK(CONCATENATE(""http://websrv2/~clearweb/thpclr_report/"",text(" & Range("A1") & ",""yyyymmdd""),""/backoffice/BBVA.OPTFUT.customer.PositionCheck"",text(" & Range("A1") & ",""yyyymmdd"")),""user Defined name"")"

 

[astephanov]

Thanks for the help, I really appreciate it. I hadn't thought of user Range ("cell ref").Formula = to tell excel where to put the link.

The problem that I have though is that after a user pastes in a link and my formulas parse it out and extract the URL parts (w/o the dates) I don't know how to write the macro to use the text in the cell holding the URL part, without using the cell ref. Here is an example
Cell B6 = http://websrv2/~clearweb/<wbr>thpclr_report/
Cell B8 = /backoffice/BBVA.OPTFUT.<wbr>customer.PositionCheck.

Now, I need to tell VBA to copy the text from the cells and insert it the formula the way you have it

Range("d6").Formula = "=HYPERLINK(CONCATENATE(""http://websrv2/~clearweb/thpclr_report/""," & Range("A1") & ",""/backoffice/BBVA.OPTFUT.customer.PositionCheck""," & Range("A1") & "),""user Defined name"")

I want to be able to reuse cell B6 and B8 to generate other links.

Thanks again for the help.

Alec

 

[westconn1]

just concatenate the ranges b6, a1, b8, a1,
saves all the double quotes