ExcelCOM_UDF and Hyperlinks

[JBJB]

I see in the ExcelCOM_UDF that you can add a hyperlink, but is there any way to read a stored hyperlink?

Thanks

[big_daddy]

Try this...

#include <ExcelCOM_UDF.au3>

$sFilePath = @ScriptDir & "\test.xls"
$oWorkbook = _ExcelBookOpen($sFilePath)
$oSheet = $oWorkbook.ActiveSheet

$oHyperlinks = $oSheet.Hyperlinks
If $oHyperlinks.Count = 0 Then
    ConsoleWrite("There are no hyperlinks in this worksheet." & @CR)
    Exit
EndIf

$sInfo = ""
For $oHyperlink In $oHyperlinks
    With $oHyperlink
        $sInfo &= "Address: " & .Address
        $sInfo &= "ScreenTip : " & .ScreenTip
        $sInfo &= "TextToDisplay: " & .TextToDisplay
    EndWith
    $sInfo &= @CR & @CR
Next

ConsoleWrite($sInfo)

[JBJB]

Thank you big_daddy, that did work, and I thought I was home free.

However I need to adapt it to work on a specific cell address. I went thru the msdn docs for excel, but I'm just going in circles.

So . . . if you know how to retrieve the Hyperlink for just one cell . . .

Thanks

[big_daddy]

So . . . if you know how to retrieve the Hyperlink for just one cell . . .

Try this...

#include <ExcelCOM_UDF.au3>

$sFilePath = @ScriptDir & "\test.xls"
$oWorkbook = _ExcelBookOpen($sFilePath)
$oSheet = $oWorkbook.ActiveSheet

; Return a range of cells
;~ $oRange = $oSheet.Range("A1:D5")

; Return a single cell (A6)
$oRange = $oSheet.Cells(6, 1)

$oHyperlinks = $oRange.Hyperlinks
If $oHyperlinks.Count = 0 Then
    ConsoleWrite("There are no hyperlinks in this worksheet." & @CR)
    Exit
EndIf

$sInfo = ""
For $oHyperlink In $oHyperlinks
    With $oHyperlink
        $sInfo &= "Address: " & .Address
        $sInfo &= "ScreenTip : " & .ScreenTip
        $sInfo &= "TextToDisplay: " & .TextToDisplay
    EndWith
    $sInfo &= @CR & @CR
Next

ConsoleWrite($sInfo)

[JBJB]

High Five! Thank you, that works perfect.

I'm trying to understand what you did, and it all makes sense except for the line:

"For $oHyperlink In $oHyperlinks"

What is $oHyperlink? It isn't predefined. This use is slightly different than the example in the AutoIt help file. Is the use of the For . . . In . . . With the only way to extract the hyperlink?

Thanks again! I do appreciate you taking the time to help me.

[big_daddy]

Here's another way to do it...

#include <ExcelCOM_UDF.au3>

$sFilePath = @ScriptDir & "\test.xls"
$oWorkbook = _ExcelBookOpen($sFilePath)
$oSheet = $oWorkbook.ActiveSheet

$oHyperlink = $oSheet.Range("A6").Hyperlinks.Item(1)

$sInfo = ""
With $oHyperlink
    $sInfo &= "Address: " & .Address
    $sInfo &= "ScreenTip : " & .ScreenTip
    $sInfo &= "TextToDisplay: " & .TextToDisplay
EndWith
$sInfo &= @CR & @CR

ConsoleWrite($sInfo)

Edited February 13, 2008 by big_daddy

[JBJB]

Ahhhh . . . that's the way I was trying to write it last night. Unsuccessfully.

Thanks again!

[big_daddy]

You're welcome, glad I was able to help.

[ArviUTA]

You're welcome, glad I was able to help.

#include <ExcelCOM_UDF.au3> ; Include the collection

$sFilePath = @ScriptDir & "C:\remo.xls"

$oWorkbook = _ExcelBookOpen($sFilePath)

$oSheet = $oWorkbook.ActiveSheet

$oHyperlink = $oSheet.Range("A2").Hyperlinks.Item(1)

$sInfo = ""

With $oHyperlink

$sInfo &= "Address: " & .Address

$sInfo &= "ScreenTip : " & .ScreenTip

$sInfo &= "TextToDisplay: " & .TextToDisplay

EndWith

$sInfo &= @CR & @CR

ConsoleWrite($sInfo)

when i run this code - i get this error

Line5

$oSheet= oWorkbook.Activesheet

$oSheet= oWorkbook^ERROR

Error : Variable must be of type object

[PsaltyDS]

#include <ExcelCOM_UDF.au3> ; Include the collection

$sFilePath = @ScriptDir & "C:\remo.xls"

$oWorkbook = _ExcelBookOpen($sFilePath)

$oSheet = $oWorkbook.ActiveSheet

$oHyperlink = $oSheet.Range("A2").Hyperlinks.Item(1)

$sInfo = ""

With $oHyperlink

$sInfo &= "Address: " & .Address

$sInfo &= "ScreenTip : " & .ScreenTip

$sInfo &= "TextToDisplay: " & .TextToDisplay

EndWith

$sInfo &= @CR & @CR

ConsoleWrite($sInfo)

when i run this code - i get this error

Line5

$oSheet= oWorkbook.Activesheet

$oSheet= oWorkbook^ERROR

Error : Variable must be of type object

Did you really leave the dollar sign ($) off the variable name? Is should be $oWorkbook.Activesheet.

Assuming that was a typo, if $oWorkbook is not an object, then _ExcelBookOpen() failed. Was your path to the .xls file correct?

[ArviUTA]

Did you really leave the dollar sign ($) off the variable name? Is should be $oWorkbook.Activesheet.

Assuming that was a typo, if $oWorkbook is not an object, then _ExcelBookOpen() failed. Was your path to the .xls file correct?

Thank you for your replay,

i checked the path name it is correct. and yes it was a typo error, so still the error persists

[PsaltyDS]

Thank you for your replay,

i checked the path name it is correct. and yes it was a typo error, so still the error persists

You are suuuuure this is correct...?

$sFilePath = @ScriptDir & "C:\remo.xls"

If your @ScriptDir is C:\MyScripts then this would give you $sFilePath = "C:\MyScriptsC:\remo.xls"

[ArviUTA]

You are suuuuure this is correct...?

If your @ScriptDir is C:\MyScripts then this would give you $sFilePath = "C:\MyScriptsC:\remo.xls"

thanks a lot i had figured it out. i had given @ScriptDir without knowing the use of it.