Solved

Open office extract URL from  hyperlinks - in the adjacent cell

Posted on 2010-08-14
2
1,985 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

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

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

Suggested Solutions

One of the biggest threats in the cyber realm pertains to advanced persistent threats (APTs). This paper is a compare and contrast of Russian and Chinese APT's.
Google Drive is extremely cheap offsite storage, and it's even possible to get extra storage for free for two years.  You can use the free account 15GB, and if you have an Android device..when you install Google Drive for the first time it will give…
Windows 10 is mostly good. However the one thing that annoys me is how many clicks you have to do to dial a VPN connection. You have to go to settings from the start menu, (2 clicks), Network and Internet (1 click), Click VPN (another click) then fi…
In this video I am going to show you how to back up and restore Office 365 mailboxes using CodeTwo Backup for Office 365. Learn more about the tool used in this video here: http://www.codetwo.com/backup-for-office-365/ (http://www.codetwo.com/ba…

816 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now