Difficulty creating macro functions to access URLs

Creating a macro - Writing a Script - Using the API

Difficulty creating macro functions to access URLs

Postby THEBookMan » Thu Feb 04, 2016 9:57 pm

 Edit: Split posts from new person in Convert clickable links to text urls to a new topic in the Macros and UNO API forum — MrProgrammer, moderator, 2020-05-08 16:48 UTC 
This code looks exactly like what I need, BUT the macro returns :

Following BASIC Scripts could not be found:
Library: 'Standard'
Module: 'Module1'
Method:'ConvertHyperlink'
location:'Application'

Any help would be appreciated.

Jim@bookman-jim.biz
Last edited by THEBookMan on Wed Mar 07, 2018 3:58 am, edited 1 time in total.
Open Office 4.1.3 Win 10
THEBookMan
 
Posts: 107
Joined: Wed Sep 30, 2015 10:03 pm
Location: Houston, TX area

Re: Convert clickable links to text urls in a spreadsheet

Postby Villeroy » Fri Feb 05, 2016 3:14 am

cell_url.ods
(14.09 KiB) Downloaded 143 times
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.4
User avatar
Villeroy
Volunteer
 
Posts: 28431
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Convert clickable links to text urls in a spreadsheet

Postby THEBookMan » Fri Feb 05, 2016 3:27 pm

Does not open. I think I D/Led this yesterday, plugged it into a MACRO and got the same message.
Perhaps I don't have something set up correctly?

TNX for your time.
Jim
Open Office 4.1.3 Win 10
THEBookMan
 
Posts: 107
Joined: Wed Sep 30, 2015 10:03 pm
Location: Houston, TX area

Re: Convert clickable links to text urls in a spreadsheet

Postby THEBookMan » Fri Feb 05, 2016 3:32 pm

Villeroy,
Sorry, it did D/L.

Copied hyperlinks into Col A, B, C, and D, but nothing happened???

Appreciate your time,
Jim
Open Office 4.1.3 Win 10
THEBookMan
 
Posts: 107
Joined: Wed Sep 30, 2015 10:03 pm
Location: Houston, TX area

Re: Convert clickable links to text urls in a spreadsheet

Postby Villeroy » Fri Feb 05, 2016 6:45 pm

Is this really difficult to understand? Did you ever use any spreadsheet?
Column B has some arbitrary hyperlink fields.

Column C contains a formula to pull out the hyperlink URL by means of userdefined function CELL_URL which is stored in the document's Standard library. CELL_URL takes a sheet number, a row number and a column number instead of a normal reference.

Column D references the hyperlink text and column E uses the built-in HYPERLINK function to compose new working hyperlinks from the extracted URL and the text.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.4
User avatar
Villeroy
Volunteer
 
Posts: 28431
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Convert clickable links to text urls in a spreadsheet

Postby THEBookMan » Fri Feb 05, 2016 8:33 pm

Something HAS to be set up incorrectly.

After I copy the hyperlinks into B and run the MACRO, I still get the message:

Following BASIC Scripts could not be found:

Scripting framework error.............
Library: 'Standard'
Module: 'Module1'
Method:'ConvertHyperlink'
location:'Application'


Anything further?

TNX,
Jim

It appears that I do not have the standard library set up.
Open Office 4.1.3 Win 10
THEBookMan
 
Posts: 107
Joined: Wed Sep 30, 2015 10:03 pm
Location: Houston, TX area

Re: Convert clickable links to text urls in a spreadsheet

Postby Villeroy » Fri Feb 05, 2016 10:49 pm

Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.4
User avatar
Villeroy
Volunteer
 
Posts: 28431
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Convert clickable links to text urls in a spreadsheet

Postby THEBookMan » Sun Feb 14, 2016 6:31 pm

Have done everything you suggested. Thank you, BUT
I still get the message:

Code: Select all   Expand viewCollapse view
  Following BASIC Scripts could not be found:
   Scripting framework error.............
   Library: 'Standard'
   Module: 'Module1'
   Method:'ConvertHyperlink'
   location:'Application'

MUST have something not set up properly.

Recorded a MACRO and altered it to loop until it encounters a blank cell, but must need some help
I added the lines with '*' at first char.
Code: Select all   Expand viewCollapse view
REM  *****  BASIC  *****

sub SHOWURL
   rem define variables
   dim document   as object
   dim dispatcher as object

   rem get access to the document
   document   = ThisComponent.CurrentController.Frame
   dispatcher = createUnoService("com.sun.star.frame.DispatchHelper")
   
*   Dim X
*   for(x)
*      If Cell"A(X)" = ""
*      then end sub
         dim args1(0) as new com.sun.star.beans.PropertyValue
         args1(0).Name = "ToPoint"
         args1(0).Value = "$B$3"
      
         dispatcher.executeDispatch(document, ".uno:GoToCell", "", 0, args1())
                                                                                          
         dispatcher.executeDispatch(document, ".uno:Paste", "", 0, Array())
      
         dim args3(1) as new com.sun.star.beans.PropertyValue
         args3(0).Name = "By"
         args3(0).Value = 1
         args3(1).Name = "Sel"
         args3(1).Value = false
      
         dispatcher.executeDispatch(document, ".uno:GoDown", "", 0, args3())
      
         dim args4(1) as new com.sun.star.beans.PropertyValue
         args4(0).Name = "By"
         args4(0).Value = 1
         args4(1).Name = "Sel"
         args4(1).Value = false

         dispatcher.executeDispatch(document, ".uno:GoLeft", "", 0, args4())
*   next(X)
end sub


It's been 25 years since I taught EXCEL levels 1,2&3 and MS VB, so I'm really rusty.

Thanks again for your help,
Jim
Last edited by RoryOF on Sun Feb 14, 2016 6:33 pm, edited 1 time in total.
Reason: Added [code] tags. [RoryOF, Moderator]
Open Office 4.1.3 Win 10
THEBookMan
 
Posts: 107
Joined: Wed Sep 30, 2015 10:03 pm
Location: Houston, TX area

Re: Convert clickable links to text urls in a spreadsheet

Postby Villeroy » Sun Feb 14, 2016 6:45 pm

You don't give us the faintest idea what you did but the error message is perfectly clear. Together with the small totorial I've linked you should be able to fix the problem or stop macro coding alltogether.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.4
User avatar
Villeroy
Volunteer
 
Posts: 28431
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Convert clickable links to text urls in a spreadsheet

Postby THEBookMan » Tue Feb 16, 2016 6:07 pm

I have a OOCALC file that has over 3000 hyperlinks (column A) that I am trying to create a MACRO to automatically convert to the text and paste into column B.

I recorded a MACRO that did exactly what I wanted it to do: Get the URL from the hyperlink in cell A1 and copy it to cell B1.
I begin by selecting cell A1, the run the MACRO.
MACRO runs great.

Then I added code to loop until it found Ax to be empty at which point the macro would end:

REM ***** BASIC *****
sub SHOWURL
rem define variables
dim document as object
dim dispatcher as object

rem get access to the document
document = ThisComponent.CurrentController.Frame
dispatcher = createUnoService("com.sun.star.frame.DispatchHelper")

rem Initiate loop
rem check to see of this cell is empty. If it is empty drop down to end sub
Do While Not IsEmpty(currentCell)

dim args1(0) as new com.sun.star.beans.PropertyValue
args1(0).Name = "ToPoint"
args1(0).Value = "currentcell"

rem Move to cell to the right (B(X)) and paste the value from cell A(X)
dispatcher.executeDispatch(document, ".uno:GoToCell", "", 0, args1())
dispatcher.executeDispatch(document, ".uno:Paste", "", 0, Array())

dim args3(1) as new com.sun.star.beans.PropertyValue
args3(0).Name = "By"
args3(0).Value = 1
args3(1).Name = "Sel"
args3(1).Value = false

rem Move down 1 row and back to Column A
dispatcher.executeDispatch(document, ".uno:GoDown", "", 0, args3())

dim args4(1) as new com.sun.star.beans.PropertyValue
args4(0).Name = "By"
args4(0).Value = 1
args4(1).Name = "Sel"
args4(1).Value = false

dispatcher.executeDispatch(document, ".uno:GoLeft", "", 0, args4())
loop
end sub

BASIC finds no errors, but running the MACRO does nothing.

Any input is greatly appreciated as I don't want to manually run the MACRO >3000 times.

TNX,
GBJim.Andrews@gmail.com
Open Office 4.1.3 Win 10
THEBookMan
 
Posts: 107
Joined: Wed Sep 30, 2015 10:03 pm
Location: Houston, TX area

Re: Convert clickable links to text urls in a spreadsheet

Postby Villeroy » Tue Feb 16, 2016 6:46 pm

You have 2 viable solutions ready to use (if you could).
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.4
User avatar
Villeroy
Volunteer
 
Posts: 28431
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Convert clickable links to text urls in a spreadsheet

Postby THEBookMan » Tue Feb 16, 2016 7:44 pm

I don't understand what you are talking about; 2 viable solutions. In the code I provided?

Do you see why that MACRO with the loop doesn't work?
The original MACRO (recorded) worked great! I can't see why it won't loop until if finds that cell Ax is empty...

thanks,
Jim
Open Office 4.1.3 Win 10
THEBookMan
 
Posts: 107
Joined: Wed Sep 30, 2015 10:03 pm
Location: Houston, TX area

Re: Convert clickable links to text urls in a spreadsheet

Postby Villeroy » Tue Feb 16, 2016 8:03 pm

THEBookMan wrote:The original MACRO (recorded) worked great!

Then use your recorded macro.
F3KTotal provided another macro and my CELL_URL function works almost like a built-in cell function. It can even extract the 2nd, 3rd, 4th hyperlink of a cell.

The error message shows clearly that the code can not be loaded from the specified location.
Library: 'Standard'
Module: 'Module1'
Method:'ConvertHyperlink'
location:'Application'
In the application wide macro container there is no Module1 with method ConvertHyperlink
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.4
User avatar
Villeroy
Volunteer
 
Posts: 28431
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Convert clickable links to text urls in a spreadsheet

Postby THEBookMan » Wed May 06, 2020 12:27 pm

'Puter crashed!
Had to reload everything (Most data saved, though)
Have OO 4.1.7
Problem is that I have been using "Show URL.ODS" as D/Led a while ago (see this thread) and it worked great!

Just D/Led it a day or 2 ago and it now does not work.
JAVA runtime is enabled, as far as I know.

File is attached.

Have tried =HYPERLINK('URL';A2) in each cell in col B to no avail.

Thanks for your help ... AGAIN,
⌡im [THE ßookMan]
Attachments
Show URL.ods
(14.43 KiB) Downloaded 18 times
Open Office 4.1.3 Win 10
THEBookMan
 
Posts: 107
Joined: Wed Sep 30, 2015 10:03 pm
Location: Houston, TX area

Re: Convert clickable links to text urls in a spreadsheet

Postby RoryOF » Wed May 06, 2020 12:30 pm

You need to download and install a 32 bit Java, as OO is a 32 bit program. This Java must then be selected (one time only) in /Tools /Options /OpenOffice /Java. It will run alongside the 64 bit Java of Windows 10.
Apache OpenOffice 4.1.7 on Xubuntu 18.04.4 (mostly 64 bit version) and very infrequently on Win2K/XP
User avatar
RoryOF
Moderator
 
Posts: 31232
Joined: Sat Jan 31, 2009 9:30 pm
Location: Ireland

Re: Convert clickable links to text urls in a spreadsheet

Postby Villeroy » Wed May 06, 2020 2:39 pm

This is a bug in OpenOffice. Basic macros do not require any Java. Just ignore the error message or run the code viaTools>Macros>Organize>Basic... [Run] or create a toolbar button, a shortcut, a menu entry or call it anyway but not via Tools>Macros>Run...
My sheet functions do not show this problem because they are called by sheet cells
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.4
User avatar
Villeroy
Volunteer
 
Posts: 28431
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Convert clickable links to text urls in a spreadsheet

Postby THEBookMan » Wed May 06, 2020 9:49 pm

FYI:

installed JAVA 1.8.0.251

Lowered security to least

Everything is great!
Open Office 4.1.3 Win 10
THEBookMan
 
Posts: 107
Joined: Wed Sep 30, 2015 10:03 pm
Location: Houston, TX area

Re: Convert clickable links to text urls in a spreadsheet

Postby Villeroy » Wed May 06, 2020 9:59 pm

THEBookMan wrote:Lowered security to least

Don't do that Set it to the highest level and define some directory(ies) where you store macro documents. This includes any subdirectories of the specified directories. Do not add your Download folder because that folder gets anything from anywhere.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.4
User avatar
Villeroy
Volunteer
 
Posts: 28431
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany


Return to Macros and UNO API

Who is online

Users browsing this forum: No registered users and 3 guests