Solved

Open office extract URL from  hyperlinks - in the adjacent cell

Posted on 2010-08-14
2
2,012 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
2 Comments
 
LVL 20

Accepted Solution

by:
ltlbearand3 earned 500 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

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
index match 2 values in row excel 5 543
Paste Image in writer 4 500
OO SCalsc Conditional formatting is not working as anticipated. 5 559
Libre Office titlebar 3 497
Starting your own business is always a daunting process, and for most people it is brand new experience. Avoid the common pitfalls by following these tips to start on the road to success.
Many businesses neglect disaster recovery and treat it as an after-thought. I can tell you first hand that data will be lost, hard drives die, servers will be hacked, and careless (or malicious) employees can ruin your data.
Microsoft Active Directory, the widely used IT infrastructure, is known for its high risk of credential theft. The best way to test your Active Directory’s vulnerabilities to pass-the-ticket, pass-the-hash, privilege escalation, and malware attacks …

861 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