Excel 中的自动超链接?

发布于 2024-08-19 15:06:00 字数 360 浏览 11 评论 0原文

我正在公开来自 SSAS 服务器的数据,用户可以通过他们喜欢的任何工具(实际上通常是 Excel 2007)连接到该服务器。我们希望在某些单元格中提供 URL,并希望 Excel 将这些识别为超链接并相应地激活和设置格式。

当我们将 URL 放入单元格时,它只会呈现为纯文本。如果您触摸该单元格并再次单击,Excel 会将其激活为链接。

如果直接从 SQL 或 CSV 文件获取数据,也会发生相同的行为,我们在这里使用 SSAS 没有什么特别之处。

有谁知道如何使这些自动呈现为链接?我们无法控制电子表格的生成,因此宏已被淘汰。

编辑:

刚刚收到梅夫的回复。好点,但我忘了提及我们显示 URL 的位置位于钻取操作的目标中。

I am exposing data from a SSAS server to which users can connect from any tool they like, (in practise usually Excel 2007). We would like to provide URLs in some cells and would like Excel to recognise these as hyperlinks and make active and format accordingly.

When we put a URL into a cell, it is just rendered as plain text. If you touch the cell and click out again then Excel activates it as a link.

The same behaviour occurs if you source the data direct from SQL, or from a CSV file, there's nothing special about our use of SSAS here.

Does anyone know a way to make these render as links automatically? We have no control over the generation of the spreadsheet so macros are out.

EDIT:

Just received the response from Meff. Good point, however I forgot to mention that the place we are showing the URLs is in the target of a drillthough action.

如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。

扫码二维码加入Web技术交流群

发布评论

需要 登录 才能够评论, 你可以免费 注册 一个本站的账号。

评论(4

我们只是彼此的过ke 2024-08-26 15:06:00

我不相信如果没有宏,这是不可能的。

Excel 单元格中的超链接与单元格的值无关。它是与单元关联的单独对象。向单元格添加超链接的完整功能方法是通过 Excel 菜单项插入 -> 。超级链接。

事实上,当用户输入“http://...”、“https://...”或“ftp://...”等值时,就会添加一个超链接,这只是一个 GUI 快捷方式,与Insert -> 效果相同超级链接。在您的示例中,当用户“触摸”单元格时,他们会有效地将“http://...”值输入到单元格和“插入”->“GUI 快捷方式”中。超链接被调用。但是,当通过 VBA 或内置 Excel 功能(例如数据 -> 导入外部数据)以编程方式将值输入到单元格时,不会调用该快捷方式。

因此,让这些任意数据导入在没有某种形式的宏的情况下将适当的值呈现为超链接确实是一个挑战。如果您可以说服用户安装一个简单的 Excel 加载项,您可以提供一个运行以下简单代码的菜单项:-

Dim cell as Range : For Each cell in Selection.Cells // could also use Range("A1").CurrentRegion.Cells or similar
    If Left(cell.Value, 7) = "http://" Or Left(cell.Value, 8) = "https://" Or Left(cell.Value, 6) = "ftp://" Then
        Call cell.HyperLinks.Add(cell, cell.Value)
    End If
Next cell

用户可以在导入/刷新数据后调用此菜单项。显然,这远非理想,因为它依赖于用户采取额外的步骤来呈现超链接。

I don't believe this will be possible without a macro.

A hyperlink in an Excel cell has nothing to do with the value of the cell. It is a seperate object that is associated with the cell. The fully functional way to add a hyperlink to a cell is via the Excel menu item Insert -> Hyperlink.

The fact that a hyperlink is added when a user enters a value such as 'http://...', 'https://...' or 'ftp://...' is simply a GUI shortcut which has the same effect as Insert -> Hyperlink. In your example, when the user 'touches' the cell, they effectively enter the 'http://...' value into the cell and GUI shortcut for Insert -> Hyperlink is invoked. The shortcut is, however, NOT invoked when values are entered into cells programtically, either via VBA or built in Excel features (e.g. Data -> Import External Data).

For this reason, it is indeed a challenge to make these arbitrary data imports render appropriate values as hyperlinks without some form of macro. If you can persuade your users to install a simple Excel add-in, you could provide a menu item which runs the following simple code:-

Dim cell as Range : For Each cell in Selection.Cells // could also use Range("A1").CurrentRegion.Cells or similar
    If Left(cell.Value, 7) = "http://" Or Left(cell.Value, 8) = "https://" Or Left(cell.Value, 6) = "ftp://" Then
        Call cell.HyperLinks.Add(cell, cell.Value)
    End If
Next cell

The user could invoke this after importing/refreshing data. Obviously it's far from ideal because it relies on the user taking an extra step in order to have the hyperlinks rendered.

一紙繁鸢 2024-08-26 15:06:00

看一下 SSAS 中的 URL 操作,这允许他们右键单击单元格并能够转到右键单击菜单中的链接:

http://timlaqua.com/2009/03/ssas-cube-action-cells -目标类型-url-操作类型-示例/

Take a look at URL actions in SSAS, this allows them to right-click the cell and be offered the ability to go to a link in the right-click-menu:

http://timlaqua.com/2009/03/ssas-cube-action-cells-target-type-url-action-type-example/

他是夢罘是命 2024-08-26 15:06:00

没有 VBA ....我发现最简单的方法就是记录一个宏&将快捷键(如 ctrl -L)应用于宏。最终用户知道,如果他们想要激活“链接”,则可以单击 CTRL-L。
我的宏被记录为......突出显示该列,单击数据,文本到列,下一个,下一个,下一个,完成)然后单击左上角的单元格 a1 只是为了更好的测量。 2013 年我必须将文件保存为 xlsm 文件(不知道为什么)

without VBA....I found the easiest way to do this is just to record a macro & apply the shortcut key ( like ctrl -L) to the macro. The end users know that if they want to active the "links" they click CTRL-L.
My macro was recorded as..... highlight the column, click data,text to columns, next,next,next,finish) then click off the top left cell a1 just for good measure. I had to save the file as an xlsm file (not sure why) in 2013

慕烟庭风 2024-08-26 15:06:00

手动更新许多链接的一种简单方法是首先将单元格设置为超链接,然后仅将链接的值复制到已格式化的单元格。也就是说,在您的目标中,创建一个活动的超链接单元格并将其复制到该范围。验证单元格现在是否作为 html 链接处于活动状态。然后,获取“文本”超链接,并将新链接作为值粘贴到这些单元格的顶部,它们应该会激活。

An easy way to manually update many links is to use set the cell to a hyperlink to start with, then copy just the value of the links to the already-formatted cells. That is, in your destination, create an active hyperlink cell and copy it to the range. Verify that the cells are now active as html links. Then, take your "text" hyperlinks, and paste the new links AS VALUES on top of those cells, and they should activate.

~没有更多了~
我们使用 Cookies 和其他技术来定制您的体验包括您的登录状态等。通过阅读我们的 隐私政策 了解更多相关信息。 单击 接受 或继续使用网站,即表示您同意使用 Cookies 和您的相关数据。
原文