Function Reference

_Excel_RangeLinkAddRemove

Adds or removes a hyperlink to/from a specified range

#include <Excel.au3>
_Excel_RangeLinkAddRemove ( $oWorkbook, $vWorksheet, $vRange, $sAddress [, $sSubAddress = Default [, $sScreenTip = Default [, $sTextToDisplay = Default]]] )

Parameters

$oWorkbook Excel workbook object
$vWorksheet Name, index or worksheet object to be used. If set to keyword Default the active sheet will be used
$vRange Either a range object or an A1 range to be set to a hyperlink
$sAddress The address for the specified link. The address can be an E-mail address, an Internet
    address or a file name. "" removes an existing hyperlink
$sSubAddress [optional] The name of a location within the destination file, such as a bookmark, named range
    or slide number (default = keyword Default = None)
$sScreenTip [optional] The text that appears as a ScreenTip when the mouse pointer is positioned over the
    specified hyperlink (default = keyword Default = Uses value of $sAddress)
$sTextToDisplay [optional] The text to be displayed for the hyperlink (default = keyword Default = None)

Return Value

Success: a hyperlinks object when a link is set or 1 when a link is removed.
Failure: 0 and sets @error.
@error: 1 - $oWorkbook is not an object or not a workbook object
2 - $vWorksheet name or index are invalid or $vWorksheet is not a worksheet object. @extended is set to the COM error code
3 - $vRange is invalid. @extended is set to the COM error code
4 - Error occurred when adding/removing the hyperlink. @extended is set to the COM error code

Remarks

Looks like Excel only writes the $sTextToDisplay to the upper left cell of a range (if the cell is empty). The remaining cells of the range remain untouced.

Example

Example 1

#include <Excel.au3>
#include <MsgBoxConstants.au3>

; Create application object and open an example workbook
Local $oExcel = _Excel_Open()
If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_RangeLinkAddRemove Example", "Error creating the Excel application object." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
Local $oWorkbook = _Excel_BookOpen($oExcel, @ScriptDir & "\Extras\_Excel1.xls")
If @error Then
        MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_RangeLinkAddRemove Example", "Error opening workbook '" & @ScriptDir & "\Extras\_Excel1.xls'." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
        _Excel_Close($oExcel)
        Exit
EndIf

; Add a link to cells A1:C1
_Excel_RangeLinkAddRemove($oWorkbook, $oWorkbook.Activesheet, "A1:C1", "http://www.autoitscript.com", Default, "AutoIt Homepage")
If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_RangeLinkAddRemove Example 1", "Error setting hyperlink." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_RangeLinkAddRemove Example 1", "Links set to cells 'A1:C3'.")

; Remove the links from cells A1:C1
_Excel_RangeLinkAddRemove($oWorkbook, $oWorkbook.Activesheet, "A1:C1", "")
If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_RangeLinkAddRemove Example 2", "Error setting hyperlink." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_RangeLinkAddRemove Example 2", "Links removed from cells 'A1:C3'.")

Example 2

#include <Excel.au3>
#include <MsgBoxConstants.au3>

; Create application object and open an example workbook
Local $oExcel = _Excel_Open()
If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_RangeLinkAddRemove Example", "Error creating the Excel application object." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
Local $oWorkbook = _Excel_BookOpen($oExcel, @ScriptDir & "\Extras\_Excel1.xls", True)
If @error Then
        MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_RangeLinkAddRemove Example", "Error opening workbook '" & @ScriptDir & "\Extras\_Excel1.xls'." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
        _Excel_Close($oExcel)
        Exit
EndIf

; Insert an index sheet with links to all other sheets.
; Handles Sheet names with spaces correctly.
Local $oSheet = _Excel_SheetAdd($oWorkbook, 1, True, 1, "Index")
If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_RangeLinkAddRemove Example 3", "Error adding sheet." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
$oSheet.Range("A1").Value = "Index"
Local $iRow = 2
For $iSheet = 2 To $oWorkbook.Sheets.Count
        $oSheet.Cells($iRow, 1).Value = $iRow - 1
        $oSheet.Cells($iRow, 2).Value = $oWorkbook.Worksheets($iRow).Name
        _Excel_RangeLinkAddRemove($oWorkbook, $oSheet, $oSheet.Cells($iRow, 2), $oWorkbook.Fullname, "'" & $oSheet.Cells($iRow, 2).Value & "'!A1")
        $iRow = $iRow + 1
Next
MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_RangeLinkAddRemove Example 3", "Index Sheet inserted as sheet 1.")