Sign in to follow this  
Followers 0
JBJB

ExcelCOM_UDF and Hyperlinks

13 posts in this topic

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

Thanks

Share this post


Link to post
Share on other sites



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)

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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)

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

#6 ·  Posted (edited)

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 by big_daddy

Share this post


Link to post
Share on other sites

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

Thanks again!

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

#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?

:)


Valuater's AutoIt 1-2-3, Class... Is now in Session!For those who want somebody to write the script for them: RentACoder"Any technology distinguishable from magic is insufficiently advanced." -- Geek's corollary to Clarke's law

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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"

:)


Valuater's AutoIt 1-2-3, Class... Is now in Session!For those who want somebody to write the script for them: RentACoder"Any technology distinguishable from magic is insufficiently advanced." -- Geek's corollary to Clarke's law

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!


Register a new account

Sign in

Already have an account? Sign in here.


Sign In Now
Sign in to follow this  
Followers 0