?
Solved

Open office extract URL from  hyperlinks - in the adjacent cell

Posted on 2010-08-14
2
Medium Priority
?
2,119 Views
Last Modified: 2013-12-27
I have a column peppered with hyperlinks.

I need to extract the hyperlinks from each cell in my .ods spreadsheet.  

I need them to appear in the cell they exist in , or to the cell to the right.


You can see by checking the Image column, I need the URL's from the hyperlinks.
 Bay-Speed-Aero-Kit-Product-List-.xls
0
Comment
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
2 Comments
 
LVL 20

Accepted Solution

by:
ltlbearand3 earned 2000 total points
ID: 33437860
We can take the Macro from your earlier questions (http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/Star_OpenOffice/StarOffice_Calc/Q_26374214.html) and modify it to just replace the current cell with the text from the link.  Just update the Macro with the one below.  This will not longer prompt for a sheet to enter, but instead will just update any hyperlink on the current sheet.  I noticed you posted an .xls file but asked in OpenOffice.  This macro is for OpenOffice.  If you need an Excel macro, you will need to let us know that.

REM  *****  BASIC  *****

Option Explicit

Sub ShowAllHyperlinks()
	Dim oDocument as object
	Dim oSheet as Object
	Dim oCellCursor as object	
	Dim intLastRow as integer
	Dim intLastCol as integer
	Dim oNewSheet as object
	Dim intCurRow as integer
	Dim intCurCol as integer
	Dim intNewRow as integer
	Dim oNewCell as object
	Dim oCell as Object
	Dim strLink as String

	' Get access to the document
	oDocument   = ThisComponent 

	' Find Last Cell of Data in Sheet
	oSheet = oDocument.getCurrentSelection.getSpreadSheet
	oCellCursor = oSheet.createCursor()
   	oCellCursor.gotoEndOfUsedArea(False)
	intLastRow  = oCellCursor.getRangeAddress().endRow
	intLastCol = oCellCursor.getRangeAddress().endColumn
	intNewRow = 0
	
	' Loop Through all Cells looking for URLs
	For intCurRow = 0 to intLastRow
		For intCurCol = 0 to intLastCol
			oCell =  oSheet.getCellByPosition(intCurCol, intCurRow)
			' VarType 9 = Object
			If vartype(oCell) = 9 then
				' If Count is greater than 1, we have a cell with a URL
				If oCell.TextFields.Count > 0 Then
					strLink = oCell.GetTextFields.getByIndex(0).URL
					oCell.string = strLink
		   		End If
		   	End If
 		Next
 	Next
 	
	msgbox "URL Extraction is Complete"

End Sub

Open in new window

0
 

Author Comment

by:www_puertoricoautoforo_com
ID: 33439575
-- (in a Mr. Burns voice...)  "Egggcelent!"
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In the absence of a fully-fledged GPO Management product like AGPM, the script in this article will provide you with a simple way to watch the domain (or a select OU) for GPOs changes and automatically take backups when policies are added, removed o…
What's worse than having your data encrypted by ransomware? Getting attacked by a so-called "wiper," which simply destroys the data and offers you no hope of ever seeing it again.
If you’ve ever visited a web page and noticed a cool font that you really liked the look of, but couldn’t figure out which font it was so that you could use it for your own work, then this video is for you! In this Micro Tutorial, you'll learn yo…
This tutorial will teach you the special effect of super speed similar to the fictional character Wally West aka "The Flash" After Shake : http://www.videocopilot.net/presets/after_shake/ All lightning effects with instructions : http://www.mediaf…
Suggested Courses

770 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question