I have a hyperlink to a web site that is 425-450 characters depending on the account. I have issues.
The dates inside the URL change based on month so I cannot hard code the URL in the cell, even though this gets around the function's 255 character limitation when I click on the link.
I hard coded the date to April in the URL and it worked, giving me the April data webpage. However, I then did a find/replace to change the date parameters to March dates then clicked on the link, I still got the April data webpage, even though the URL in the Excel cell had March dates.
If I choose to hard coded URL and find/replace the dates, how can I make the URL in the Excel cell update to the new date parameters? The webpage did update to March when I clicked on the cell then pressed F2 then click on the link, but I don't want to have to do that for 300 lines.
Or, can I use Power Query to help me still use the Hyperlink function, so that the cell only displays the Account number and still work on the ridiculously long URLs?
@magickrhythm
Could you give us an example of such a 'ridiculously long' URL?
@riny hi, yes here is an example. I have changed a few details for privacy. I also bolded the 4 dynamic variables that change with each month and for each account.
I made a table of hardcoded Hyperlinks. The table showing the external account number in one column and the full hyperlink below (hardcoding the Fac ID and Acct ID). The hyperlink, as listed below worked. However, when did a find/replace to change the Begin Date and End Date variables to a different month, the hyperlink did not update, instead still opening the April page.
I know that after I do the find and replace, I can go to each cell with the URL and use F2 then Enter to fix the new dates correctly, however, I have over 300 lines of account numbers and this will take a lot of time each week! How can I change the date and then have the URL update to the corresponding date page?
https://hbweb.mylearning-solutions.com/servlet/ReportRequest?sFormat=hbweb&XMLFile=hb||||reports||||rtoHBWebStatementReportsXML.jsp&XSLFile=hb||||reports||||rtoHBStatementPrintHTMLWeb.xsl&delimitedParmString= beginDate=04-01-2026|endDate=04-30-2026|facilityIDs=237|accountNumber=0|accountIDs=6263657|trackID=learning|country=USA|userName=bobsmith|siteID=0&delimiter=|&mckweb=N&resourceID=0&mckwebFileName=&mckwebPDFFileName=spTrackID=&spCountry=&spFacilityID=0&pdf=1.pdf
Difficult to test without a live URL, but in PQ you can use parameters for the parts that vary. You'll have to connect the the website first and the edit the link in the Source step to something like this:
" https://hbweb.mylearning-solutions.com/servlet/ReportRequest?sFormat=hbweb&XMLFile=hb||||reports||||rtoHBWebStatementReportsXML.jsp&XSLFile=hb||||reports||||rtoHBStatementPrintHTMLWeb.xsl&delimitedParmString= beginDate=" & start & " |endDate= " & end & "|facilityIDs=" & id & "|accountNumber=0|accountIDs=" & accountid & "|trackID=learning|country=USA|userName=bobsmith|siteID=0&delimiter=|&mckweb=N&resourceID=0&mckwebFileName=&mckwebPDFFileName=spTrackID=&spCountry=&spFacilityID=0&pdf=1.pdf"
So you insert the variables into the URL like " & xxxx & " where xxxx is the name of the parameter.
Are you familiar with this technique?
@riny I am still very new at PQ. I have not worked with variables or parameters before.
I mostly just clean up data.
Take a look at this video on using a cell as a parameter.
https://www.youtube.com/watch?v=7NzqndwwlZo
You could also use some code like this to do the replacements:
Sub replaceHlinks()
Dim oldDate As String
oldDate = InputBox("Enter old date", "Old date")
If oldDate = "" Then Exit Sub
Dim newDate As String
newDate = InputBox("Enter new date", "New date")
If newDate = "" Then Exit Sub
Dim ws As Worksheet
For Each ws In ActiveWorkbook.Worksheets
Dim hlink As Hyperlink
For Each hlink In ws.Hyperlinks
Dim newAddress As String
Dim loc As Range
With hlink
newAddress = Replace$(.Address, oldDate, newDate)
Set loc = .Range
.Delete
End With
ws.Hyperlinks.Add loc, newAddress, , , newAddress
Next
Next
End Sub