vba - 使用 ActiveWorkbook.FollowHyperlink 在 excel 中打开网站

Question

所以，这基本上就是我想要做的。我在从 MSSQL 生成的文件中有一列员工编号。我想在 URL 所在的单元格中创建一个函数，http://www.someplace.com/employee.php?ID=Employee#FromCell

到目前为止，我发现的所有示例都不够详细，我无法弄清楚如何处理它。我知道这是不正确的，但这是我到目前为止的结果

Function openurl(strSKU As String)
    ActiveWorkbook.FollowHyperlink Address:="http://www.someplace.com/employee.php?ID=?strSKU=" & strSKU, NewWindow:=True
End Function

我想我把方法和函数混在一起了，但我不知道该去哪里。我基本上想将它作为一个函数添加，以便更容易插入到列中。

Answer 1

我看到有人为您提供了解决此问题的方法，但我会给您您要求的方法(以防万一)。仅供引用，智能感知在引用 OLE 对象时会吸收 VBA(即，某些方法可能看起来不属于按钮对象，但它们确实属于)。

下面的脚本将自动为您创建按钮，并在单击时将用户发送到您指定的站点。 **我包括解释每行做什么的注释。

这将在 B 列中创建按钮并从 A 列获取 URL 参数:

Sub CreateButtons()


Dim btn As Button 'Create a variable for our button
Application.ScreenUpdating = False 'Speed up the process by disabling ScreenUpdating
ActiveSheet.Buttons.Delete 'Delete existing buttons.
Dim Report As Worksheet 'Create our worksheet variable.
Set Report = Excel.ActiveSheet 'Set our worksheet to the worksheet variable.
Dim t As Range 'Create a variable for the cells we will reference.

For i = 1 To Report.UsedRange.Rows.Count 'This will loop through each row in the used range of our worksheet.
    If Report.Cells(i, 1).Value <> "" Then 'If the value of the first cell is not empty, then do the following...
        Set t = Report.Range(Cells(i, 2), Cells(i, 2)) 'Assign the cell in the second column of the current row to the cell variable.
        Set btn = Report.Buttons.Add(t.Left, t.Top, t.Width, t.Height) 'Create a button and place it in the cell in the second column.

        With btn
          .OnAction = "openurl" 'Set the button to trigger the openurl sub-routine when it is clicked.
          .Caption = Report.Cells(i, 1).Value 'Set the caption of the button to equal the value of the cell in the first column.
          .Name = i 'Set the name of the button to equal the row on which it resides. This name will be used in the openurl sub; So don't change it.
        End With
   End If
Next i


End Sub

这是用户单击按钮时执行的宏:

Sub openurl()

Dim Report As Worksheet 'Create a variable for the worksheet
Set Report = Excel.ActiveSheet 'Assign the worksheet to our variable
Dim i As Integer 'Create a variable for our row number
i = Application.Caller 'Assign name of the button to our row number.

Dim address As String 'Create a variable for our address
address = "http://www.someplace.com/employee.php?ID=?strSKU=" & Report.Cells(i, 1).Value 'Assign the URL to our address variable.

ActiveWorkbook.FollowHyperlink address:=address, NewWindow:=True 'Send the user to the URL you specified (with the URL parameter at the end).

End Sub

奖金信息:

按照下一步自动为您完成整个过程:

当您说当前数据是从 MSSQL 数据库填充时，您可能意味着您正在使用另一个 VBA 子或函数将数据拉入 Excel。如果是这样，那么如果您在提取数据的脚本之后放置一个脚本来调用“CreateButtons()”子例程，整个过程将自动为您完成。例子:

Sub getEmployeeData() 'This represents your sub that pulls your data from MSSQL
'================================================================
'This represents your script to get your information into Excel.
'================================================================

Call CreateButtons 'This runs the CreateButtons() subroutine.

End Sub

享受!