Get URL from Hyperlink

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
11 messages Options
Reply | Threaded
Open this post in threaded view
|

Get URL from Hyperlink

David Stuhr
Hello,

I have an OpenOffice spreadsheet that has over a thousand hyperlinks to
an external www website. The information in each hyperlinked cell is the
URL and descriptive text. From a search of the web it appears that Excel
has a "get URL" function but a search of OpenOffice help did not return
a similar function.

Is there a method/way of getting the URL in a cell that contains a
hyperlink and putting it in another? I want to do this to identify
duplicate URL references.

Thank you for your assistance.

Reply | Threaded
Open this post in threaded view
|

Re: Get URL from Hyperlink

Brian Barker-2
At 08:18 17/08/2020 -0400, David Stuhr wrote:
>I have an OpenOffice spreadsheet that has over a thousand hyperlinks
>to an external www website. The information in each hyperlinked cell
>is the URL and descriptive text. From a search of the web it appears
>that Excel has a "get URL" function but a search of OpenOffice help
>did not return a similar function.
>
>Is there a method/way of getting the URL in a cell that contains a
>hyperlink and putting it in another? I want to do this to identify
>duplicate URL references.

See
https://superuser.com/questions/315752/separating-text-and-links-from-a-hyperlink-in-openoffice 
- which includes a function CELL_URL() which appears to provide what you need.

I trust this helps.

Brian Barker


---------------------------------------------------------------------
To unsubscribe, e-mail: [hidden email]
For additional commands, e-mail: [hidden email]

Reply | Threaded
Open this post in threaded view
|

Re: Get URL from Hyperlink

David Stuhr
Hello Brian,

I had time today to copy the Basic macro code in the link provided today
into a test spreadsheet. I pasted the code between "Sub Main" and "End
Sub". I then saved the macro. When I attempted to compile the macro I
got the following error.

BASIC syntax error.
Function not allowed within a procedure.

What am I doing wrong?

David

On 8/17/2020 9:12 AM, Brian Barker wrote:

> At 08:18 17/08/2020 -0400, David Stuhr wrote:
>> I have an OpenOffice spreadsheet that has over a thousand hyperlinks
>> to an external www website. The information in each hyperlinked cell
>> is the URL and descriptive text. From a search of the web it appears
>> that Excel has a "get URL" function but a search of OpenOffice help
>> did not return a similar function.
>>
>> Is there a method/way of getting the URL in a cell that contains a
>> hyperlink and putting it in another? I want to do this to identify
>> duplicate URL references.
>
> See
> https://superuser.com/questions/315752/separating-text-and-links-from-a-hyperlink-in-openoffice 
> - which includes a function CELL_URL() which appears to provide what
> you need.
>
> I trust this helps.
>
> Brian Barker
>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: [hidden email]
> For additional commands, e-mail: [hidden email]
>
Reply | Threaded
Open this post in threaded view
|

Re: Get URL from Hyperlink

Andrew Pitonyak

My best guess is that you pasted the macro into the subroutine that is created when a module is created. 

I created a Calc Document and then I placed the macro as a module in the standard library of the Calc document. I tested the code and it seems to work just fine. 

I will mail you the Calc document directly. 
Because the macro is in the Standard Library of the document, the macros are always available for that document as functions. On the other hand, I think that you cannot import the Standard library into another library container (such as a document), so you would need to manually copy the macros into a new document. 

Enough testing for me for now, I have some other things I need to do, but, watch for the document by itself. I do not believe that I can post the document (attachement) to the list. 

cheers!


On Tuesday, August 18, 2020 17:40 EDT, David Stuhr <[hidden email]> wrote:
 Hello Brian,

I had time today to copy the Basic macro code in the link provided today
into a test spreadsheet. I pasted the code between "Sub Main" and "End
Sub". I then saved the macro. When I attempted to compile the macro I
got the following error.

BASIC syntax error.
Function not allowed within a procedure.

What am I doing wrong?

David

On 8/17/2020 9:12 AM, Brian Barker wrote:

> At 08:18 17/08/2020 -0400, David Stuhr wrote:
>> I have an OpenOffice spreadsheet that has over a thousand hyperlinks
>> to an external www website. The information in each hyperlinked cell
>> is the URL and descriptive text. From a search of the web it appears
>> that Excel has a "get URL" function but a search of OpenOffice help
>> did not return a similar function.
>>
>> Is there a method/way of getting the URL in a cell that contains a
>> hyperlink and putting it in another? I want to do this to identify
>> duplicate URL references.
>
> See
> https://superuser.com/questions/315752/separating-text-and-links-from-a-hyperlink-in-openoffice
> - which includes a function CELL_URL() which appears to provide what
> you need.
>
> I trust this helps.
>
> Brian Barker
>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: [hidden email]
> For additional commands, e-mail: [hidden email]
>


 
Reply | Threaded
Open this post in threaded view
|

Re: Get URL from Hyperlink

Andrew Pitonyak
In reply to this post by David Stuhr

Nope, sorry, I did the work, but I could not send an email to your email address. Did not bother trying to figure out why, might be because my email is not letting me send the macro attachment, in which case I need to fix my outgoing email server or if yours rejected it. I will look at this later. 

On Tuesday, August 18, 2020 17:40 EDT, David Stuhr <[hidden email]> wrote:
 Hello Brian,

I had time today to copy the Basic macro code in the link provided today
into a test spreadsheet. I pasted the code between "Sub Main" and "End
Sub". I then saved the macro. When I attempted to compile the macro I
got the following error.

BASIC syntax error.
Function not allowed within a procedure.

What am I doing wrong?

David

On 8/17/2020 9:12 AM, Brian Barker wrote:

> At 08:18 17/08/2020 -0400, David Stuhr wrote:
>> I have an OpenOffice spreadsheet that has over a thousand hyperlinks
>> to an external www website. The information in each hyperlinked cell
>> is the URL and descriptive text. From a search of the web it appears
>> that Excel has a "get URL" function but a search of OpenOffice help
>> did not return a similar function.
>>
>> Is there a method/way of getting the URL in a cell that contains a
>> hyperlink and putting it in another? I want to do this to identify
>> duplicate URL references.
>
> See
> https://superuser.com/questions/315752/separating-text-and-links-from-a-hyperlink-in-openoffice
> - which includes a function CELL_URL() which appears to provide what
> you need.
>
> I trust this helps.
>
> Brian Barker
>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: [hidden email]
> For additional commands, e-mail: [hidden email]
>


 
Reply | Threaded
Open this post in threaded view
|

Re: Get URL from Hyperlink

Steven Ahlers
Andrew you need to remove the .INVALID from the end of his email address.

Sent from my iPhone

> On Aug 18, 2020, at 5:25 PM, Andrew Pitonyak <[hidden email]> wrote:
>
> 
> Nope, sorry, I did the work, but I could not send an email to your email address. Did not bother trying to figure out why, might be because my email is not letting me send the macro attachment, in which case I need to fix my outgoing email server or if yours rejected it. I will look at this later.
>
> On Tuesday, August 18, 2020 17:40 EDT, David Stuhr <[hidden email]> wrote:
>  Hello Brian,
>
> I had time today to copy the Basic macro code in the link provided today
> into a test spreadsheet. I pasted the code between "Sub Main" and "End
> Sub". I then saved the macro. When I attempted to compile the macro I
> got the following error.
>
> BASIC syntax error.
> Function not allowed within a procedure.
>
> What am I doing wrong?
>
> David
>
>> On 8/17/2020 9:12 AM, Brian Barker wrote:
>> At 08:18 17/08/2020 -0400, David Stuhr wrote:
>>> I have an OpenOffice spreadsheet that has over a thousand hyperlinks
>>> to an external www website. The information in each hyperlinked cell
>>> is the URL and descriptive text. From a search of the web it appears
>>> that Excel has a "get URL" function but a search of OpenOffice help
>>> did not return a similar function.
>>>
>>> Is there a method/way of getting the URL in a cell that contains a
>>> hyperlink and putting it in another? I want to do this to identify
>>> duplicate URL references.
>>
>> See
>> https://superuser.com/questions/315752/separating-text-and-links-from-a-hyperlink-in-openoffice
>> - which includes a function CELL_URL() which appears to provide what
>> you need.
>>
>> I trust this helps.
>>
>> Brian Barker
>>
>>
>> ---------------------------------------------------------------------
>> To unsubscribe, e-mail: [hidden email]
>> For additional commands, e-mail: [hidden email]
>>
>
>
>  
>
>


---------------------------------------------------------------------
To unsubscribe, e-mail: [hidden email]
For additional commands, e-mail: [hidden email]

Reply | Threaded
Open this post in threaded view
|

Re: Get URL from Hyperlink

Andrew Pitonyak

Yeah, it would not send. Not sure if my server rejected the email, if his server rejected my server, etc. I need to look into it. 

On Tuesday, August 18, 2020 18:37 EDT, Steven Ahlers <[hidden email]> wrote:
 Andrew you need to remove the .INVALID from the end of his email address.

Sent from my iPhone

> On Aug 18, 2020, at 5:25 PM, Andrew Pitonyak <[hidden email]> wrote:
>
> 
> Nope, sorry, I did the work, but I could not send an email to your email address. Did not bother trying to figure out why, might be because my email is not letting me send the macro attachment, in which case I need to fix my outgoing email server or if yours rejected it. I will look at this later.
>
> On Tuesday, August 18, 2020 17:40 EDT, David Stuhr <[hidden email]> wrote:
> Hello Brian,
>
> I had time today to copy the Basic macro code in the link provided today
> into a test spreadsheet. I pasted the code between "Sub Main" and "End
> Sub". I then saved the macro. When I attempted to compile the macro I
> got the following error.
>
> BASIC syntax error.
> Function not allowed within a procedure.
>
> What am I doing wrong?
>
> David
>
>> On 8/17/2020 9:12 AM, Brian Barker wrote:
>> At 08:18 17/08/2020 -0400, David Stuhr wrote:
>>> I have an OpenOffice spreadsheet that has over a thousand hyperlinks
>>> to an external www website. The information in each hyperlinked cell
>>> is the URL and descriptive text. From a search of the web it appears
>>> that Excel has a "get URL" function but a search of OpenOffice help
>>> did not return a similar function.
>>>
>>> Is there a method/way of getting the URL in a cell that contains a
>>> hyperlink and putting it in another? I want to do this to identify
>>> duplicate URL references.
>>
>> See
>> https://superuser.com/questions/315752/separating-text-and-links-from-a-hyperlink-in-openoffice
>> - which includes a function CELL_URL() which appears to provide what
>> you need.
>>
>> I trust this helps.
>>
>> Brian Barker
>>
>>
>> ---------------------------------------------------------------------
>> To unsubscribe, e-mail: [hidden email]
>> For additional commands, e-mail: [hidden email]
>>
>
>
>
>
>


---------------------------------------------------------------------
To unsubscribe, e-mail: [hidden email]
For additional commands, e-mail: [hidden email]
 


 
Reply | Threaded
Open this post in threaded view
|

Re: Get URL from Hyperlink

Andrew Pitonyak
In reply to this post by Steven Ahlers

They updated my email servers and they have decided that any document with a macro is invalid / dangerous. Will see if I can fix it or if I need to have the admin to id. It was fixed a few months back, but the server was updated. They are just trying to keep me safe.... Hate that to some extent. 

On Tuesday, August 18, 2020 18:37 EDT, Steven Ahlers <[hidden email]> wrote:
 Andrew you need to remove the .INVALID from the end of his email address.

Sent from my iPhone

> On Aug 18, 2020, at 5:25 PM, Andrew Pitonyak <[hidden email]> wrote:
>
> 
> Nope, sorry, I did the work, but I could not send an email to your email address. Did not bother trying to figure out why, might be because my email is not letting me send the macro attachment, in which case I need to fix my outgoing email server or if yours rejected it. I will look at this later.
>
> On Tuesday, August 18, 2020 17:40 EDT, David Stuhr <[hidden email]> wrote:
> Hello Brian,
>
> I had time today to copy the Basic macro code in the link provided today
> into a test spreadsheet. I pasted the code between "Sub Main" and "End
> Sub". I then saved the macro. When I attempted to compile the macro I
> got the following error.
>
> BASIC syntax error.
> Function not allowed within a procedure.
>
> What am I doing wrong?
>
> David
>
>> On 8/17/2020 9:12 AM, Brian Barker wrote:
>> At 08:18 17/08/2020 -0400, David Stuhr wrote:
>>> I have an OpenOffice spreadsheet that has over a thousand hyperlinks
>>> to an external www website. The information in each hyperlinked cell
>>> is the URL and descriptive text. From a search of the web it appears
>>> that Excel has a "get URL" function but a search of OpenOffice help
>>> did not return a similar function.
>>>
>>> Is there a method/way of getting the URL in a cell that contains a
>>> hyperlink and putting it in another? I want to do this to identify
>>> duplicate URL references.
>>
>> See
>> https://superuser.com/questions/315752/separating-text-and-links-from-a-hyperlink-in-openoffice
>> - which includes a function CELL_URL() which appears to provide what
>> you need.
>>
>> I trust this helps.
>>
>> Brian Barker
>>
>>
>> ---------------------------------------------------------------------
>> To unsubscribe, e-mail: [hidden email]
>> For additional commands, e-mail: [hidden email]
>>
>
>
>
>
>


---------------------------------------------------------------------
To unsubscribe, e-mail: [hidden email]
For additional commands, e-mail: [hidden email]
 


 
Reply | Threaded
Open this post in threaded view
|

Re: Get URL from Hyperlink - Solved

David Stuhr
In reply to this post by David Stuhr
Hello,

I figured out a very low tech solution ... save as an HTML file, a
little editing and a file import. Done. Sometimes simpler is better and
I know I will fully understand what I am dong. :-)

I would be more than happy to write up a cheat sheet of the steps in the
procedure that I am yet to devise. (But I know I can do it.)

I want to thank the members of the group for the unpaid work that you
do. I for one appreciate what each of you do. I look forward each day to
the question/problems users present and the thoughtful attention you
give to the issues raised. Keep up the good work.

I think in this day and age that a function that would get the URL from
a Hyperlink in a spreadsheet cell would be entirely appropriate. Does
the team/individual that maintains OpenOffice have a "suggestion" box?

David

On 8/18/2020 5:40 PM, David Stuhr wrote:

>
> Hello Brian,
>
> I had time today to copy the Basic macro code in the link provided
> today into a test spreadsheet. I pasted the code between "Sub Main"
> and "End Sub". I then saved the macro. When I attempted to compile the
> macro I got the following error.
>
> BASIC syntax error.
> Function not allowed within a procedure.
>
> What am I doing wrong?
>
> David
>
> On 8/17/2020 9:12 AM, Brian Barker wrote:
>> At 08:18 17/08/2020 -0400, David Stuhr wrote:
>>> I have an OpenOffice spreadsheet that has over a thousand hyperlinks
>>> to an external www website. The information in each hyperlinked cell
>>> is the URL and descriptive text. From a search of the web it appears
>>> that Excel has a "get URL" function but a search of OpenOffice help
>>> did not return a similar function.
>>>
>>> Is there a method/way of getting the URL in a cell that contains a
>>> hyperlink and putting it in another? I want to do this to identify
>>> duplicate URL references.
>>
>> See
>> https://superuser.com/questions/315752/separating-text-and-links-from-a-hyperlink-in-openoffice 
>> - which includes a function CELL_URL() which appears to provide what
>> you need.
>>
>> I trust this helps.
>>
>> Brian Barker
>>
>>
>> ---------------------------------------------------------------------
>> To unsubscribe, e-mail: [hidden email]
>> For additional commands, e-mail: [hidden email]
>>
Reply | Threaded
Open this post in threaded view
|

Re: Get URL from Hyperlink - Solved

Brian Barker-2
At 19:38 18/08/2020 -0400, David Stuhr wrote:
>I think in this day and age that a function that would get the URL
>from a Hyperlink in a spreadsheet cell would be entirely
>appropriate. Does the team/individual that maintains OpenOffice have
>a "suggestion" box?

Yup. You can suggest enhancements at the same place as you report
bugs: https://bz.apache.org/ooo/ . See also
https://wiki.openoffice.org/wiki/QA/HowToFileIssue .

Brian Barker


---------------------------------------------------------------------
To unsubscribe, e-mail: [hidden email]
For additional commands, e-mail: [hidden email]

Reply | Threaded
Open this post in threaded view
|

Re: Get URL from Hyperlink

Brian Barker-2
In reply to this post by David Stuhr
At 17:40 18/08/2020 -0400, David Stuhr wrote:

>Hello Brian,
>
>I had time today to copy the Basic macro code in the link provided
>today into a test spreadsheet. I pasted the code between "Sub Main"
>and "End Sub". I then saved the macro. When I attempted to compile
>the macro I got the following error.
>
>BASIC syntax error.
>Function not allowed within a procedure.
>
>What am I doing wrong?

I'm guessing you should delete the two lines you quote above. But I'm
no expert - and others, including Mr Pitonyak, are.

Brian Barker  


---------------------------------------------------------------------
To unsubscribe, e-mail: [hidden email]
For additional commands, e-mail: [hidden email]