[Solved] HYPERLINK formula: copy/paste result w/o formula
[Solved] HYPERLINK formula: copy/paste result w/o formula
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
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
Last edited by Hagar Delest on Tue Sep 03, 2013 11:34 pm, edited 1 time in total.
Reason: tagged [Solved].
Reason: tagged [Solved].
OpenOffice 4 on OSx
Re: HYPERLINK formula: How to copy/paste the result w/o form
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 ...
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 ...
AOO4/LO5 • Linux • Fedora 23
- Charlie Young
- Volunteer
- Posts: 1559
- Joined: Fri May 14, 2010 1:07 am
Re: HYPERLINK formula: How to copy/paste the result w/o form
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.
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
Apache OpenOffice 4.1.1
Windows XP
Windows XP
Re: HYPERLINK formula: How to copy/paste the result w/o form
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.
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.
OpenOffice 4 on OSx
Re: HYPERLINK formula: How to copy/paste the result w/o form
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!
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!
OpenOffice 4 on OSx
- Charlie Young
- Volunteer
- Posts: 1559
- Joined: Fri May 14, 2010 1:07 am
Re: HYPERLINK formula: How to copy/paste the result w/o form
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.
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.
Apache OpenOffice 4.1.1
Windows XP
Windows XP
Re: HYPERLINK formula: How to copy/paste the result w/o form
AWESOME!!!
Worked like a dream! Thank you so much, Charlie!
Worked like a dream! Thank you so much, Charlie!
OpenOffice 4 on OSx
Re: HYPERLINK formula: How to copy/paste the result w/o form
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
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
OpenOffice 4 on OSx
Re: [Solved] HYPERLINK formula: copy/paste result w/o formul
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)
oText.insertTextContent(oCursor,tField,True)
OpenOffice 2.4 on Ubuntu 9.04
Re: [Solved] HYPERLINK formula: copy/paste result w/o formul
Without sight of the entire macro we cannot say.
Apache OpenOffice 4.1.15 on Xubuntu 22.04.4 LTS
Re: [Solved] HYPERLINK formula: copy/paste result w/o formul
@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:
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?
On Windows 10: LibreOffice 24.2 (new numbering) and older versions, PortableOpenOffice 4.1.7 and older, StarOffice 5.2
---
Lupp from München
---
Lupp from München
Re: [Solved] HYPERLINK formula: copy/paste result w/o formul
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.
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
OpenOffice 2.4 on Ubuntu 9.04
Re: [Solved] HYPERLINK formula: copy/paste result w/o formul
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!
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!
On Windows 10: LibreOffice 24.2 (new numbering) and older versions, PortableOpenOffice 4.1.7 and older, StarOffice 5.2
---
Lupp from München
---
Lupp from München