在excel中一次打开所选列中的所有超链接 [英] open all hyperlinks from selected column at once in excel

问题描述

我想一次打开一列中的所有超链接，

我使用的代码是

  Sub OpenLinks()昏暗的HL作为超链接对于选择中的每个HL超链接HL跟随下一个结束子 

当我选择包含

解决方案

我认为您正在寻找以下内容.我对 VBA 宏的上一个答案的后续操作，您需要将所有文本转换为有效的文本网址.在这里， Hyperlink()的输出将不起作用，而直接超链接到该单元格将起作用.到单元格的超链接与到单元格的公式之间有很大的区别.因此，使用某些VBA函数，您必须将所有文本连接为单个字符串，然后使用 ActiveSheet.FollowHyperlink 或 ThisWorkBook.FollowHyperlink 方法启动该超链接.尝试一下下面的内容，然后让我知道您的反馈意见.

  Sub OpenMyLink()昏暗的strURL作为字符串，fixedURL作为字符串调光范围fixedURL ="https://in.tradingview.com/chart/?symbol=NSE:"对于每个选择strURL = fixedURL&Replace(Replace(rng，&"，"_"，1，1)，-"，"_"，1，1)&"1！"ThisWorkbook.FollowHyperlink strURL下一个结束子 

I want to open all the hyperlinks in a column at once,

code i used is

Sub OpenLinks()
Dim HL As Hyperlink
For Each HL In Selection.Hyperlinks
HL.Follow
Next
End Sub

this macro works fine when i select the cell containing www.google.com but dont work when I select cell next to it and run the macro

i have created the links mentioned in green and red column using the formula from this question https://stackoverflow.com/a/65867184/14713550

edit

解决方案

I think you are looking something below. Follow-up from my previous answer for VBA macro you need to convert all text to a valid URL. Here output from Hyperlink() will not work while direct hyperlink to the cell will work. There is big difference between hyperlink to cell and formula to the cell. So, using some VBA functions you have to concatenate all text to a single string then launch that hyperlink using ActiveSheet.FollowHyperlink or ThisWorkBook.FollowHyperlink method. Give a try on my below sub then let me know your feedback.

Sub OpenMyLink()
Dim strURL As String, fixedURL As String
Dim rng As Range
    
    fixedURL = "https://in.tradingview.com/chart/?symbol=NSE:"

    For Each rng In Selection
        strURL = fixedURL & Replace(Replace(rng, "&", "_", 1, 1), "-", "_", 1, 1) & "1!"
        ThisWorkbook.FollowHyperlink strURL
    Next rng

End Sub

这篇关于在excel中一次打开所选列中的所有超链接的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！