|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Copying text from a cell in the footer and pasting it into a hyperlink address
Hello
I am trying to create a macro that will run automatically when a word document is opened and will do the following: 1) Copy the six digits contained in a table located in the footer of the 1st page of the document; 2) Paste the above six digits into the address of a hyperlink also located in the table in the footer of the 1st page of the document (different cell) replacing some of the text already in the address. I am completely new to word macros and the Record Macro button only does what I ask partly: ================================================== Sub Macro3() ' ' Macro3 Macro ' ' If ActiveWindow.View.SplitSpecial <> wdPaneNone Then ActiveWindow.Panes(2).Close End If If ActiveWindow.ActivePane.View.Type = wdNormalView Or ActiveWindow. _ ActivePane.View.Type = wdOutlineView Then ActiveWindow.ActivePane.View.Type = wdPrintView End If ActiveWindow.ActivePane.View.SeekView = wdSeekCurrentPageFooter Selection.Range.Copy ChangeFileOpenDirectory _ "C:\Users\taryn.avellano\Documents\ViewPoint.gibsq l01.VPMain" Selection.Range.Hyperlinks(1).Range.Fields(1).Resu lt.Select Selection.Range.Hyperlinks(1).Delete ActiveDocument.Hyperlinks.Add Anchor:=Selection.Range, Address:= _ "https://secure.worldpay.com/wcc/purchase?instId=1033965&cartId=20150013&amount=(+D T.EURO+)¤cy=GBP&desc=WorldPay+Test&testMode= 100" _ , SubAddress:="" Selection.Collapse Direction:=wdCollapseEnd End Sub ================================================== The above macro does copy the six digits (i.e. 20150013) and pastes it into the hyperlink address. However, it has the following limitations, which makes it unusable for me, as I need it to require no action on behalf of the user opening the document: 1) It only works when I run it after having highlighted the hyperlink object. I need it to run automatically when the word document is opened; 2) It always pastes the same six digits (i.e. 20150013) into the hyperlink address, instead of copying the six digits contained in the table located in the footer of the 1st page of the document, which changes with each document. Any help would be greatly appreciated. Kind regards. Taryn |
#2
|
||||
|
||||
Taryn
Is there a point to having a hyperlink in a footer when it isn't clickable unless you edit the footer? In my opinion a hyperlink in a footer is really only useful if you are exporting the doc to PDF. We would do this with ranges rather than selections. Can you post a sample document which doesn't need any body content but will need to contain the footer you want to work on. You will also need to identify which 6 digits you want to replace. Your recorded code shows three separate numbers in the hyperlink path but none of them have six digits.
__________________
Andrew Lockton Chrysalis Design, Melbourne Australia |
#3
|
|||
|
|||
Copying text from a cell in the footer and pasting it into a hyperlink address
Dear Andrew.
Thanks for your message. We will be exporting the document to PDF but, if it's easier, we can take the hyperlink out of the footer. It's something we may be doing in the future anyway. I attach a sample document, with just the footer. The digits that need to be copied is the invoice number, in this sample it's 123456 The text in the hyperlink that needs to be replaced is (+HD.ProformaNr+) Kind regards. Taryn |
#4
|
||||
|
||||
The following should do the trick
Code:
Sub Macro1() Dim oLink As Hyperlink Dim oTable As Table Dim i As Integer Dim strInvoice As String Dim strAmount As String Set oTable = ActiveDocument.Sections(1).Footers(wdHeaderFooterFirstPage).Range.Tables(1) strInvoice = oTable.Range.Cells(7).Range strInvoice = Left(strInvoice, Len(strInvoice) - 1) strAmount = oTable.Range.Cells(10).Range strAmount = Left(strAmount, Len(strAmount) - 1) Set oLink = ActiveDocument.Sections(1).Footers(wdHeaderFooterFirstPage).Range.Hyperlinks(1) 'Debug.Print oLink.Address oLink.Address = Replace(oLink.Address, "(+HD.ProformaNr+)", strInvoice) oLink.Address = Replace(oLink.Address, "(+DT.EURO+)", strAmount) 'Debug.Print oLink.Address Set oLink = Nothing Set oTable = Nothing End Sub
__________________
Graham Mayor - MS MVP (Word) (2002-2019) Visit my web site for more programming tips and ready made processes www.gmayor.com |
#5
|
|||
|
|||
That works well, thank you Graham. Is there a way to make this macro run automatically when the word document is opened?
Kind regards. Taryn |
#6
|
||||
|
||||
Rename the macro AutoOpen and save it in the document template (not the normal template) or save it in the document and save the document as macro enabled.
__________________
Graham Mayor - MS MVP (Word) (2002-2019) Visit my web site for more programming tips and ready made processes www.gmayor.com |
#7
|
|||
|
|||
Copying text from a cell in the footer and pasting it into a hyperlink address
Dear Graham
Thanks for your help. The document, saved as a macro-enabled document, runs the macro automatically upon opening. I do, however, get an error when the document opens (see attachment) and I am not sure why. If I ignore the error and click on "End", the document opens and the macro has been run correctly. If I click on "Debug" it takes me to the VBA screen and highlights a line in yellow (see attachment). As the macro appears to be working, is there a way to stop the error appearing? Or perhaps incorporate the clicking of "End" into the macro? By the way, I have slightly amended you macro code to reflect a change in table cell position and text of the target items. Kind regards. Taryn |
#8
|
||||
|
||||
It works with the example document from earlier in the thread. As you have not stated what changes you have made, it is difficult to suggest where the error lies, however as the table has merged cells you need to be accurate with the cell indices.
See attached.
__________________
Graham Mayor - MS MVP (Word) (2002-2019) Visit my web site for more programming tips and ready made processes www.gmayor.com |
#9
|
||||
|
||||
I'm not understanding why this needs to run on AutoOpen.
After it has run once, what is the macro supposed to do on a second run? Doesn't the first run remove the text that any subsequent runs are looking to replace?
__________________
Andrew Lockton Chrysalis Design, Melbourne Australia |
#10
|
||||
|
||||
Good point, and it wasn't in my original macro, but I assume we are not getting the whole story and the footer appears to contain variable information that will need to be processed afresh each time, which suggests also that putting the code in the document rather than its template is not a good plan. This of course is the dilemma of only seeing part of the story.
__________________
Graham Mayor - MS MVP (Word) (2002-2019) Visit my web site for more programming tips and ready made processes www.gmayor.com |
Thread Tools | |
Display Modes | |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Copying and Pasting from Other Programs Results in Italicized Text | CrossReach | Word | 3 | 02-20-2017 02:08 PM |
copying and pasting text format | paik1002 | Excel | 2 | 10-07-2016 02:34 AM |
Copying and pasting from Amazon sales to MS Word the text is garbled. | voyagerone | Word | 6 | 09-08-2015 04:18 PM |
Pasting text from Excel cell into word without creating a table, and keeping the in-cell formatting | hanvyj | Excel Programming | 0 | 08-28-2015 01:15 AM |
Maintain formatting while copying text from word, and pasting into a webpage. | kdogg121 | Word | 1 | 07-07-2009 02:50 AM |