OpenOffice.org Forum at OOoForum.orgThe OpenOffice.org Forum
 
 [Home]   [FAQ]   [Search]   [Memberlist]   [Usergroups]   [Register
 [Profile]   [Log in to check your private messages]   [Log in

Question about hyperlinks in OOcalc

 
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Macros and API
View previous topic :: View next topic  
Author Message
rebelxt
OOo Enthusiast
OOo Enthusiast


Joined: 10 Dec 2006
Posts: 114
Location: StLouis

PostPosted: Tue Feb 27, 2007 3:33 pm    Post subject: Question about hyperlinks in OOcalc Reply with quote

I need a routine to find and validate/change the hyperlinks in a Calc document. I have OpenOffice.org 2.1 running in WinXP. I found the following code, posted by pitonyak, at http://www.oooforum.org/forum/viewtopic.phtml?t=40031&highlight=hyperlink
Code:

Sub SetURLLink
 Dim oSels
 Dim oSel
 Dim i As Integer
 Dim s$
 oSels = ThisComponent.getCurrentController().getSelection()
 For i = 0 To oSels.getCount() - 1
   oSel = oSels.getByIndex(i)
   If oSel.supportsService("com.sun.star.text.TextRange") Then
     s = oSel.getString()
     If Len(s) > 0 Then
       REM Set them as you desire
       'oSel.HyperLinkName =
       oSel.HyperLinkTarget = s
       oSel.HyperLinkURL = s
     End If
   End If
 Next
End Sub

When I try to run it, the oSels.getCount() gets an error: Property or method not found.

What is wrong here?
Back to top
View user's profile Send private message
Villeroy
Super User
Super User


Joined: 04 Oct 2004
Posts: 10106
Location: Germany

PostPosted: Tue Feb 27, 2007 4:24 pm    Post subject: Reply with quote

Just a hint: In Calc you can handle hyperlinks much easier:
A1: http://www.oooforum.org/
B1: Forum
=HYPERLINK(A1;B1)&T(STYLE("hyperlink"))
where "hyperlink" is a cell style with blue, underlined font.
You may even use:
C1 =IF(left(A1;5)="http:";"httpLinkstyle";IF(left(A1;7)="mailto:";"mailLinkStyle";"errStyle"))
with
=HYPERLINK(A1;B1)&T(STYLE(C1))

OK, you have a spreadsheet with textfields in column A.
http://www.oooforum.org/forum/viewtopic.phtml?t=32909
provides a set of cell-functions. One of them is CELL_URL(sheet;row;column;[number])
If column A of this sheet has one hyperlink per cell:
B1 =CELL_URL(SHEET();ROW();1) -> http://www.oooforum.org
C1 =HYPERLINK(B1;A1) -> unformatted cell, showing the text of A1 with a hyperlink, pointing to http://www.oooforum.org
If some of your cells have 2 hyperlinks you can use the optional argument
B1 =CELL_URL(SHEET();ROW();1) -> 1st hyperlink in A1
C1 =CELL_URL(SHEET();ROW();1;2) -> 2nd hyperlink in A1 or ""

EDIT: Forgot to answer you initial question:
You have to get the text-object of the cell.
oSels should be a single range
get a single cell by *column and* row:
oCell = oSels.getByIndex(0,i)
get the text-fields of the cell:
oFields = oCell.v.getTextfields()
for i = 0 to oFields.getCount -1
...
next
_________________
Rest in peace, oooforum.org
Get help on https://forum.openoffice.org
Back to top
View user's profile Send private message
rebelxt
OOo Enthusiast
OOo Enthusiast


Joined: 10 Dec 2006
Posts: 114
Location: StLouis

PostPosted: Tue Feb 27, 2007 5:34 pm    Post subject: Reply with quote

OK, my first thought was, you didn't answer my question. Then I read the whole thing again, and realized I don't need a macro for what I want to do. So that's great. Now, I put this formula in a cell:
Code:

=HYPERLINK("file:///D:/Spreadsheets/" & Summary.A2 & "/I40.pdf"; "I40") & T(STYLE("hyperlink"))

I now have a hyperlink, and it works, but it's black, and there is no highlighting or underlining. A hyperlink entered through the Insert>Hyperlink menu does show blue characters with a light gray background. Do you know why the difference?

If the style function doesn't work for me, I can, of course, just format the cell.

Oh, and if I look at the cell with the Insert>Hyperlink menu item, it doesn't recognize a hyperlink. Is that to be expected?
Back to top
View user's profile Send private message
noranthon
Super User
Super User


Joined: 07 Jul 2005
Posts: 3318

PostPosted: Wed Feb 28, 2007 12:48 am    Post subject: Reply with quote

Since Villeroy has not reappeared, I will reply. You will have to create your own hyperlink cell style. A style named "hyperlink" does not exist by default (unless I'm behind the times on that score).

Cell styles - F11, right-click "Default" and select "New". See the Calc guide (~ chapter 10) on this page: http://documentation.openoffice.org/manuals/oooauthors2/index.html

Insert >Hyperlink inserts a hyperlink. You have not exactly done that. You have used the HYPERLINK function to bring about the same effect. Using the function gives you more flexibility with regard to the formatting of the cell.

You do not have to use the STYLE function. Once you have a cell style, you can just apply it to the cell, using F11, a keyboard shortcut or a macro assigned to a menu command or toolbar icon.
_________________
search forum by month
Back to top
View user's profile Send private message
rebelxt
OOo Enthusiast
OOo Enthusiast


Joined: 10 Dec 2006
Posts: 114
Location: StLouis

PostPosted: Wed Feb 28, 2007 6:01 am    Post subject: Reply with quote

Thanks, noranthon, for that explanation; I'm good to go there. There is a style in OOcalc 2.1 named Excel_BuiltIn_Hyperlink.

Back to Villeroy, could you be more explicit on the macro? I can't get oSel.supportsService("com.sun.star.text.TextRange") to test TRUE.
Back to top
View user's profile Send private message
Villeroy
Super User
Super User


Joined: 04 Oct 2004
Posts: 10106
Location: Germany

PostPosted: Wed Feb 28, 2007 10:55 am    Post subject: Reply with quote

The cell supports service c.s.s.sheet.SheetCell, having interface c.s.s.text.XTextFieldsSupplier, having method getTextFields(). A cell does not support other text fields than hyperlinks. So you get a collection of hyperlinks. Each hyperlink has a property "Representation" (displayed text) and "URL". My CELL_URL() extracts any URL from any text field in any cell:
Code:

REM get URL of N th text-hyperlink from a cell, default N=1)
Function CELL_URL(vSheet,lRowIndex&,iColIndex%,optional n%)
Dim v
   If isMissing(n) then n = 1
   If n < 1 then exit function
REM get a cell by name or index of sheet, row-index and column-index
   v = getSheetCell(vSheet,lRowIndex&,iColIndex%)
   if vartype(v) = 9 then
REM getSheetCell returned a cell-object:
      if v.Textfields.Count >= n  then
         CELL_URL = v.getTextfields.getByIndex(n-1).URL
      else
         Cell_URL = ""
      endif
   else
REM getSheetCell() returns Null if something goes wrong
REM which leads to cell-error #VALUE
      CELL_URL = v
   endif
End Function

The function depends on getSheetCells which depends on getSheet. So you need to copy those as well (see link in prev. post)
If you want to get the Representation of a TextField, simply copy this function, replace CELL_URL with another name and replace
v.getTextfields.getByIndex(n-1).URL
with
v.getTextfields.getByIndex(n-1).Representation
_________________
Rest in peace, oooforum.org
Get help on https://forum.openoffice.org
Back to top
View user's profile Send private message
rebelxt
OOo Enthusiast
OOo Enthusiast


Joined: 10 Dec 2006
Posts: 114
Location: StLouis

PostPosted: Wed Feb 28, 2007 12:37 pm    Post subject: Reply with quote

Idea Very Happy

Now I understand. Thanks, Villeroy.
Back to top
View user's profile Send private message
Villeroy
Super User
Super User


Joined: 04 Oct 2004
Posts: 10106
Location: Germany

PostPosted: Wed Feb 28, 2007 1:01 pm    Post subject: Reply with quote

Very Happy Great!
Just fixed a minor issue. The function should return #VALUE if argument n <1:
Code:

If n < 1 then
  CELL_URL = Null
  exit function
endif

_________________
Rest in peace, oooforum.org
Get help on https://forum.openoffice.org
Back to top
View user's profile Send private message
Display posts from previous:   
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Macros and API All times are GMT - 8 Hours
Page 1 of 1

 
Jump to:  
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2005 phpBB Group