Link to home
Start Free TrialLog in
Avatar of f19l
f19l

asked on

What is the VB code to update a hyperlink in a spreadsheet?

Hello,

I have a spreadsheet that contains a hyperlink to another spreadsheet. Each day I have to manually update this hyperlink because it has to point to a new file that has its creation date in its title. I then run a macro that takes a copy of this hyperlink and along with other data emails it. What I would like is to know if there is any VB code that can automate the updating of the hyperlink?

Thanks.
ASKER CERTIFIED SOLUTION
Avatar of Theo Kouwenhoven
Theo Kouwenhoven
Flag of Netherlands image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of f19l
f19l

ASKER

The problem that I am having is that when I create the hyperlink in excel (\\emea\root\shared2\ECPT\Middle Office\Spreadsheet\Risk_report\Valuations\BondReport11062012.xls) and then run the macro that creates the email with the link copied in it trys to open the spreadsheet via internet explorer as http://Valuations/BondReport11062012.xls.
Hmm. Try the attached see if it helps.
hyperlinks.xlsm
Avatar of f19l

ASKER

Below is the emailing code that I am currently using. As mentioned, once the hyperlink is updated in the spreadsheet I run the code below that copies the entire sheet and dumps it in an email. When I try to open the hyperlink that is when the problem appears.

Sub Final_Email()


'References needed :
'Microsoft Outlook Object Library
'Microsoft Scripting Runtime

'Dimension variables

Dim olApp As Outlook.Application, olMail As Outlook.MailItem
Dim FSObj As Scripting.FileSystemObject, TStream As Scripting.TextStream
Dim rngeSend As Range, strHTMLBody As String

Sheets("Final Email").Select

ActiveSheet.Calculate


'Set the range to be sent
Set rngeSend = Sheets("Final Email").Range("A1:Z50")

'Now create the HTML file
ActiveWorkbook.PublishObjects.Add(xlSourceRange, "L:\ECPT\Middle Office\Spreadsheet\Risk_report\sht.htm", rngeSend.Parent.Name, rngeSend.Address, xlHtmlStatic).Publish True

'Create an instance of Outlook (or use existing instance if it already exists
Set olApp = CreateObject("Outlook.Application")

'Create a mail item
Set olMail = olApp.CreateItem(olMailItem)

'Open the HTML file using the FilesystemObject into a TextStream object
Set FSObj = New Scripting.FileSystemObject
Set TStream = FSObj.OpenTextFile("L:\ECPT\Middle Office\Spreadsheet\Risk_report\sht.htm", ForReading)

'Now set the HTMLBody property of the message to the text contained in the TextStream object
strHTMLBody = TStream.ReadAll

olMail.HTMLBody = strHTMLBody

olMail.To = "Distribution List 1"
olMail.CC = "Distribution List 2"
olMail.Subject = "Final Risk Report " & Format(Sheets("Final Email").Range("B4").Value, "mm/dd/yyyy") & " CDS + CASH"
olMail.Display
Sheets("SUM").Select

End Sub
Avatar of f19l

ASKER

When I check the file address in the email by hovering over it with the pointer it does not show it full address, which is why it is trying to open the file via windows explorer.
Ah, could be a different issue then.
Does the hyperlink get cut off half way through? We had a similar problem due to spaces in the file name. Can only be solved by using quotations or escaping string.
Avatar of f19l

ASKER

The exact message states: "Valuations/BondReport11062012.xls Ctrl+ Click to follow link"

As you can see the first part of the file path is lost.
What should the full path to the file read as ?

I suspect it was proceeded by a space and that is causing the issue.
Avatar of f19l

ASKER

\\emea\root\shared2\ECPT\Middle Office\Spreadsheet\Risk_report\Valuations\BondReport11082012.xls      

There is a space in between Middle and Office.
Avatar of f19l

ASKER

I have tried to change the file path so that there are no spaces present but the result is still the same, when I try to open the link it attempts to open the file via internet explorer
f19l,

You like to change an HyperLink, that is (afaik) always starting a Hypertext session (Http).
So now I understand that you like to open the excel sheet directly?

BTW try to replace the space in your path by %20 "..../Middle%20Office/..."
Avatar of f19l

ASKER

Correct, the hyperlink should open up the excel file but it does not. When I add the "20%" into the code again the full path is not displayed when I hover over the link with the mouse pointer.
Avatar of f19l

ASKER

Perhaps a way around this would be to insert the hyperlink separately using other another piece of code. Would you know how to do this? I was thinking that the data could be posted to the email in sections rather than all in one go.
Maybe you have to replace the hyperlink by a button so that you can use an "Open" command to open the remote sheet.
Avatar of f19l

ASKER

How would I go about adding that to an email? Sounds quite complicated.
Mail????

No you question was:

I have a spreadsheet that contains a hyperlink to another spreadsheet.
so replace the hyperlink to the second spreadsheet. that's all you asked for.
Avatar of f19l

ASKER

The full text of my question states that I have to update a hyperlink and then email it. If I use a button then that creates other problems, which I would like to avoid.
I've requested that this question be deleted for the following reason:

The question has either no comments or not enough useful information to be called an "answer".
@ MartinLiss

My answer was the 100% solution to the original question.

also the answers  ID: 38575413 and ID: 38575490 contained the right information.