[Solved] HYPERLINK formula: copy/paste result w/o formula

[Feznizzle]

Hi,

I have two columns, 'A' is a list of model numbers, 'B' is a list of URLs where pictures of the model can be found.

To make the model numbers into hyperlinks, I created this forumula:
=HYPERLINK($B1; $A1)

I need to be able to copy and paste the result of that formula with the hyperlink, but *WITHOUT* the formula in it!

If I "Paste Special..." with "Formulas" unchecked, then all I get is my model number back with no link.

My columns contain way too many links to do this manually, so I need to figure an automated way to do it. Anybody know a trick?

Thanks,
Mike

[acknak]

I don't know of any way to get OO to export the HYPERLINK results as an actual hyperlink.

The only workaround I can think of is to turn on formula view (for example, Tools > Options > OO Calc > View > Formulas: ON) then copy/paste the HYPERLINK cells (NOT THE WHOLE COLUMN) into Writer (Edit > Paste Special > As unformatted text).

At that point, you can use Edit > Find & Replace to convert the Calc function syntax into html.

There has to be an easier way ...

[Charlie Young]

Hopefully this macro will help. It should be fairly obvious how to tailor it to your situation, but please ask if anything isn't clear, or if there is something I missed.

Here the Model Numbers are on Sheet1 (oDoc.Sheets( 0)), the model numbers are in A1:A100, the picture URLs are in B1:B100, and the desired hyperlinks are written into C1:C100.

Code: Select all

			
Sub MakeLinks
	Dim oDoc As Object
	Dim oSheet As Object
	Dim ModelColumn As Object
	Dim PictureColumn As Object
	Dim HyperLinkColumn As Object
	Dim oCell As Object
	Dim oCursor As Object
	Dim oText
	Dim tField
	Dim i As Long
	
	oDoc = ThisComponent
	oSheet = oDoc.Sheets(0)
	ModelColumn = oSheet.getCellRangeByName("A1:A100")
	PictureColumn = oSheet.getCellRangeByName("B1:B100")
	HyperLinkColumn = oSheet.getCellRangeByName("C1:C100")
	For i = 0 to HyperLinkColumn.getRows().getCount() - 1
		oCell = HyperLinkColumn.getCellByPosition(0,i)
		tField = oDoc.createInstance( "com.sun.star.text.textfield.URL" )
		tField.Representation = ModelColumn.getCellByPosition(0,i).getString()
		tField.URL = PictureColumn.getCellByPosition(0,i).getString()
		oCell.setString(tField.Representation)
		oText = oCell.getText()
		oCursor = oText.createTextCursor
		oCursor.gotoStart(False)
		oCursor.gotoEnd(True)
		oText.insertTextContent(oCursor,tField,True)
	Next i	
End Sub

[Feznizzle]

Wow! Two great answers to play with, thanks!

I plan to take a run at using Charlie's macro today, will follow up to let you know how it goes. Unfortunately, I've never used a macro before, so I will have to sort that out before I can try it. Can't be that hard, right? *yikes*

I'm really shocked that this isn't just a simple fix. During copy/paste, you should be able to checkoff "FORMULA RESULTS" as an option.

[Feznizzle]

Hi Charlie,

I hope you are still out there. I've been fiddling around, cannot for the life of me figure out how to use your macro. I have never used a macro before, even with Excel. I've been searching for a basic instruction set, can't seem to find anything!

Can you post a link or provide some step-by-step?

Sorry to be such a noob, I really appreciate your patience!

[Charlie Young]

See How to install a code snippet.

If need be, I can attach a spreadsheet containing the macro, then you could copy your data into it. But here I'll just indicate the steps from the tutorial.

Tools > Macros > Organize Macros > OpenOffice Basic

Select your file in the left box of the dialog, and hit New to create Module1, then copy the code above into Module1, but not inside the Main Sub (copy to above or below it). As I indicated, you will need to modify the cell references to your needs. You can run it a couple of ways, but for starters it's probably best to just go back to the spreadsheet and do Tools > Macros > Run.

[Feznizzle]

AWESOME!!!

Worked like a dream! Thank you so much, Charlie!

[Feznizzle]

Sorry, follow up question... maybe I should start a new thread for it?

Now that I have my links, I want to use them as a reference. They are in column C, now I want to search thru column D, replacing the Model Numbers with the Linked Model Numbers we just created.

I'm sure this has been covered somewhere. I have googled the heck out of it... just can't figure out how to phrase the question to search for an answer!

POSTED THIS QUESTION IN SEPARATE THREAD:
http://forum.openoffice.org/en/forum/vi ... =9&t=64007

[wtk123]

This message has been very helpful. Does anyone know why I am getting an compile error on the below line:

oText.insertTextContent(oCursor,tField,True)

[RoryOF]

Without sight of the entire macro we cannot say.

[Lupp]

@RoryOF: wtk123 referred to the code posted by "Charlie Young" here (2013-08-30): viewtopic.php?f=9&t=63914#p283483.

@wtk123:
I cannot find a problem with the code. Are there conflicting Global variables? Specific options?
Errors on compile generally display a message. How did that read?

[wtk123]

Sorry for the late reply. I appreciate your help.

I can't imagine that there are conflicting global variables, because this is the only macro in the workbook. Not am not extremely knowledgeable of VBA though.

The only message I get is a dialog box the reads "Compile Error: Syntax error".

Thank you for any assistance.

The full code. It should be identical to the above.

Code: Select all

Sub MakeLinks()
   Dim oDoc As Object
   Dim oSheet As Object
   Dim ModelColumn As Object
   Dim PictureColumn As Object
   Dim HyperLinkColumn As Object
   Dim oCell As Object
   Dim oCursor As Object
   Dim oText
   Dim tField
   Dim i As Long
   
   oDoc = ThisComponent
   oSheet = oDoc.Sheets(0)
   ModelColumn = oSheet.getCellRangeByName("b10:b3333")
   PictureColumn = oSheet.getCellRangeByName("c10:c3333")
   HyperLinkColumn = oSheet.getCellRangeByName("d10:d3333")
   For i = 0 To HyperLinkColumn.getRows().getCount() - 1
      oCell = HyperLinkColumn.getCellByPosition(0, i)
      tField = oDoc.createInstance("com.sun.star.text.textfield.URL")
      tField.Representation = ModelColumn.getCellByPosition(0, i).getString()
      tField.URL = PictureColumn.getCellByPosition(0, i).getString()
      oCell.setString (tField.Representation)
      oText = oCell.getText()
      oCursor = oText.createTextCursor
      oCursor.gotoStart (False)
      oCursor.gotoEnd (True)
      oText.insertTextContent(oCursor,tField,True)
   Next i
End Sub

[Lupp]

The 2 Sub are not identical. You shifted the ranges one column to the right and changed the range of rows from 1:100 to 10:3333.
This aside both the routines work flawlessly for me in AOO 4.1.3 under Win 10.

I still cannot imagine a reason for the compiler error you got. User profile corruption? System specifics? ... Strange!