Hyperlink Problem

[Maggie Barr]

Hello and thank you in advance if you can help,
I am using excel 2019 64 bit on a PC.

I have a VBA macro that I have used to create hyperlinks in the past. It has always worked fine, and it "sort of" is working now, but it is not keeping the absolute hyperlink I need, so it will not open the full link now. I do not know if there was an update in the MS software that is now making not work, but it always worked before. When I paste the sheet into Google sheets, it opens up the full link. As well, when I copy the cell contents and paste it into Google Chrome, it opens it fine. I even tried adding in the www. like it used to be, to no avail. I have searched online, and tried MANY things, but none of the solutions are helping. It seems foolish that it doesn't work now. If anyone could please help me get excel to recognize and open the whole link (absolute hyperlink) in the document, I would appreciate it. This file is getting sent to, and shared with, others, so it needs to be independent within the workbook and subsequent copies. The VBA I have always used is below.
Any help or advice would be greatly appreciated.
Thank you and best wishes,
Maggie

Sub ConvertToHyperlinks()
'Updateby20140318
Dim Rng As Range
Dim WorkRng As Range
On Error Resume Next
xTitleId = "KutoolsforExcel"
Set WorkRng = Application.Selection
Set WorkRng = Application.InputBox("Range", xTitleId, WorkRng.Address, Type:=8)
For Each Rng In WorkRng
Application.ActiveSheet.Hyperlinks.Add Rng, Rng.Value
Next
End Sub

 

[Yongle]

Re: Hyperlink Problem, please help.

I cannot test on (64bit) Excel but your code works perfectly for me with (32 bit) Excel 365
It may be that the workbook itself has a problem (particularly if it was originally created on a much earlier version of Excel) but that creating the same links a NEW workbook would behave differently.

Test your code in a NEW workbook created in Excel 2019
- copy a range of link text from original workbook and Paste Values into the NEW workbook
- run code against range in NEW workbook

 

[Maggie Barr]

Re: Hyperlink Problem, please help.

Yongle,
Thank you for getting back to me. The workbook was created in excel 2019, and I already created another workbook and pasted the link address, re-activated it, and it did the same thing. I have tried the: =HYPERLINK formula to create and activate the link, to no avail. I have tried setting the Hyperlink base in the: file, Info, Properties, Advanced Properties, set it to C:\ or even R:\ (where the workbook is stored). I have tried altering the Web Options in the: file, options, advanced, general, web options, files, check and unchecked the update links on save. I have tried everything I could find on the web. As I said it used to work flawlessly. Most of the posts on this issue on the internet are very old, but I can see where it has been a problem for others in the past, most recently 2018, though, as noted above, none of their ultimate solutions have helped.
Any other advice?
Thank you again for your time, it is very kind of you.
Maggie

 

[Yongle]

Re: Hyperlink Problem, please help.

it is not keeping the absolute hyperlink I need

Q1 Exactly what is going wrong?


Q2 Is it possible to narrow down the problem to specific types of links? Which links work and do not work?

1. Link to local file on your PC hard drive
2. Link to file on local server
3. Link to file on remote server
4. Link to typical website eg Google
etc

 

[Maggie Barr]

Re: Hyperlink Problem, please help.

Yongle,
Thank you for sticking with me. It is a typical website I want to open, actually A LOT of them, but one is https://ebird.org/atlasme/block/47068A8NW but instead of opening it all the way, it only goes to the homepage https://ebird.org/home . I have tried everything I can find, and none of it is working. I have other files with hyperlinks that are working, and I have tried to create a new document, paste the hyperlinks column as values, create the hyperlink using excel styles, and it still won't work. I am cursed by this issue and at a total loss. The links work through the Google sheets, so it isn't my link in regards to text.
Thanks if you can help,
Maggie