JBJB Posted February 10, 2008 Share Posted February 10, 2008 I see in the ExcelCOM_UDF that you can add a hyperlink, but is there any way to read a stored hyperlink? Thanks Link to comment Share on other sites More sharing options...
Moderators big_daddy Posted February 13, 2008 Moderators Share Posted February 13, 2008 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) Link to comment Share on other sites More sharing options...
JBJB Posted February 13, 2008 Author Share Posted February 13, 2008 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 Link to comment Share on other sites More sharing options...
Moderators big_daddy Posted February 13, 2008 Moderators Share Posted February 13, 2008 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) Link to comment Share on other sites More sharing options...
JBJB Posted February 13, 2008 Author Share Posted February 13, 2008 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. Link to comment Share on other sites More sharing options...
Moderators big_daddy Posted February 13, 2008 Moderators Share Posted February 13, 2008 (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 February 13, 2008 by big_daddy Link to comment Share on other sites More sharing options...
JBJB Posted February 13, 2008 Author Share Posted February 13, 2008 Ahhhh . . . that's the way I was trying to write it last night. Unsuccessfully. Thanks again! Link to comment Share on other sites More sharing options...
Moderators big_daddy Posted February 14, 2008 Moderators Share Posted February 14, 2008 You're welcome, glad I was able to help. Link to comment Share on other sites More sharing options...
ArviUTA Posted May 28, 2008 Share Posted May 28, 2008 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: " & .TextToDisplayEndWith$sInfo &= @CR & @CRConsoleWrite($sInfo)when i run this code - i get this errorLine5 $oSheet= oWorkbook.Activesheet$oSheet= oWorkbook^ERRORError : Variable must be of type object Link to comment Share on other sites More sharing options...
PsaltyDS Posted May 28, 2008 Share Posted May 28, 2008 #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: " & .TextToDisplayEndWith$sInfo &= @CR & @CRConsoleWrite($sInfo)when i run this code - i get this errorLine5 $oSheet= oWorkbook.Activesheet$oSheet= oWorkbook^ERRORError : Variable must be of type objectDid 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 Link to comment Share on other sites More sharing options...
ArviUTA Posted May 28, 2008 Share Posted May 28, 2008 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 Link to comment Share on other sites More sharing options...
PsaltyDS Posted May 28, 2008 Share Posted May 28, 2008 Thank you for your replay, i checked the path name it is correct. and yes it was a typo error, so still the error persistsYou 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 Link to comment Share on other sites More sharing options...
ArviUTA Posted May 28, 2008 Share Posted May 28, 2008 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. Link to comment Share on other sites More sharing options...
Recommended Posts
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 accountSign in
Already have an account? Sign in here.
Sign In Now