Michele wrote:
On 11/7/07, NoOp <[EMAIL PROTECTED]> wrote:
On 11/06/2007 06:03 PM, Allen wrote:
Hi gang,
I inherited an .ods spreadsheet that has hyperlinks to an e-mail
address. About 10,000 of them.
The text says "Email Me" but when you look at the hyperlink it
says "mailto:[EMAIL PROTECTED]".
I want to create a column next to it that has just the email
address without having to collect them one by one so that I can
send out group emails to a few people at a time when a subject
comes up that would interest those people.
So how do I de-link them in bulk, not 10,000 one at a time?
Thanks,
[snip lots of stuff]
OOo Help is your friend...
Help|OpenOffice.org Help F1| enter hyper in the search bar then go to
'turning off automatic recognition' & double-click.
---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
Hello Allen,
As I see it you should *not* do what NoOP is suggesting because this will
simply transform the hyperlink into normal text which is not what you want.
I am afraid you will need to write a macro to do what you want. Basically
you have to inspect the hyperlink properties and extract the target.
I am not very good with Calc macros so I am not sure I would be able to do
it, but if you have time you can check out the free macro document of Andrew
Pitonyak as well as have a look at the oooforum where there are plenty of
examples. You may even find someone who offers to write the macro for you.
Cheers,
Michele
The macro would look almost exactly like this if you did not care about
speed (and your welcome)
Sub ExtractEmailLinksFromCells
ExtractEmailFromLink(2, 1, 2, 1, Thiscomponent.Sheets(0))
End Sub
Sub ExtractEmailFromLink(leftCol%, topRow%, rightCol%, bottomRow%, oSheet)
Dim iRow%, iCol%
Dim oCell
Dim s$
For iCol = leftCol To rightCol
For iRow = topRow to bottomRow
oCell = oSheet.getCellByPosition(iCol, iRow)
s = FindFirstEmailInCell(oSheet.getCellByPosition(iCol, iRow))
If Len(s) > 0 Then
Print "Setting value to " & s
oSheet.getCellByPosition(iCol-1, iRow).setString(s)
End If
Next
Next
End Sub
Function FindFirstEmailInCell(oCell) As String
Dim oText
Dim oParEnum 'Enumerator used to enumerate the paragraphs
Dim oPar 'The enumerated paragraph
Dim oSectionEnum 'Enumerator used to enumerate the text sections
Dim oSection 'The enumerated text section
Dim s$
Dim i%
FindFirstEmailInCell() = ""
oText = oCell.getText()
REM Enumerate the paragraphs.
REM Tables are enumerated along with paragraphs
oParEnum = oText.createEnumeration()
Do While oParEnum.hasMoreElements()
oPar = oParEnum.nextElement()
REM This avoids the tables. Add an else statement if you want to
REM process the tables.
If oPar.supportsService("com.sun.star.text.Paragraph") Then
oSectionEnum = oPar.createEnumeration()
Do While oSectionEnum.hasMoreElements()
oSection = oSectionEnum.nextElement()
If oSection.TextPortionType = "TextField" Then
s = oSection.TextField.URL
i = InStr(s, "mailto:";)
If i > 0 Then
FindFirstEmailInCell() = Right(s, Len(s) - 6 - i)
Exit Function
End If
End If
Loop
End If
Loop
End Function
--
Andrew Pitonyak
My Macro Document: http://www.pitonyak.org/AndrewMacro.odt
My Book: http://www.hentzenwerke.com/catalog/oome.htm
Info: http://www.pitonyak.org/oo.php
See Also: http://documentation.openoffice.org/HOW_TO/index.html
---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
