Microsoft Office Forums Copying text from a cell in the footer and pasting it into a hyperlink address

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 11-07-2019, 05:54 AM
taryn taryn is offline Copying text from a cell in the footer and pasting it into a hyperlink address Windows 10 Copying text from a cell in the footer and pasting it into a hyperlink address Office 2016
Novice
Copying text from a cell in the footer and pasting it into a hyperlink address
 
Join Date: Jan 2015
Location: Gibraltar
Posts: 21
taryn is on a distinguished road
Default 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+)&currency=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
Reply With Quote
  #2  
Old 11-07-2019, 03:29 PM
Guessed's Avatar
Guessed Guessed is online now Copying text from a cell in the footer and pasting it into a hyperlink address Windows 10 Copying text from a cell in the footer and pasting it into a hyperlink address Office 2016
Expert
 
Join Date: Mar 2010
Location: Canberra/Melbourne Australia
Posts: 1,388
Guessed is a jewel in the roughGuessed is a jewel in the roughGuessed is a jewel in the roughGuessed is a jewel in the rough
Default

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
Reply With Quote
  #3  
Old 11-08-2019, 04:53 AM
taryn taryn is offline Copying text from a cell in the footer and pasting it into a hyperlink address Windows 10 Copying text from a cell in the footer and pasting it into a hyperlink address Office 2016
Novice
Copying text from a cell in the footer and pasting it into a hyperlink address
 
Join Date: Jan 2015
Location: Gibraltar
Posts: 21
taryn is on a distinguished road
Default 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
Attached Files
File Type: docx REP446774.DOCX (20.5 KB, 7 views)
Reply With Quote
  #4  
Old 11-08-2019, 06:00 AM
gmayor's Avatar
gmayor gmayor is offline Copying text from a cell in the footer and pasting it into a hyperlink address Windows 10 Copying text from a cell in the footer and pasting it into a hyperlink address Office 2016
Expert
 
Join Date: Aug 2014
Posts: 3,029
gmayor is just really nicegmayor is just really nicegmayor is just really nicegmayor is just really nicegmayor is just really nice
Default

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
Reply With Quote
  #5  
Old 11-12-2019, 03:48 AM
taryn taryn is offline Copying text from a cell in the footer and pasting it into a hyperlink address Windows 10 Copying text from a cell in the footer and pasting it into a hyperlink address Office 2016
Novice
Copying text from a cell in the footer and pasting it into a hyperlink address
 
Join Date: Jan 2015
Location: Gibraltar
Posts: 21
taryn is on a distinguished road
Default

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
Reply With Quote
  #6  
Old 11-12-2019, 05:33 AM
gmayor's Avatar
gmayor gmayor is offline Copying text from a cell in the footer and pasting it into a hyperlink address Windows 10 Copying text from a cell in the footer and pasting it into a hyperlink address Office 2016
Expert
 
Join Date: Aug 2014
Posts: 3,029
gmayor is just really nicegmayor is just really nicegmayor is just really nicegmayor is just really nicegmayor is just really nice
Default

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
Reply With Quote
  #7  
Old 11-14-2019, 04:57 AM
taryn taryn is offline Copying text from a cell in the footer and pasting it into a hyperlink address Windows 10 Copying text from a cell in the footer and pasting it into a hyperlink address Office 2016
Novice
Copying text from a cell in the footer and pasting it into a hyperlink address
 
Join Date: Jan 2015
Location: Gibraltar
Posts: 21
taryn is on a distinguished road
Default 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
Attached Files
File Type: docx Error upon opening word document.docx (47.6 KB, 3 views)
Reply With Quote
  #8  
Old 11-14-2019, 05:24 AM
gmayor's Avatar
gmayor gmayor is offline Copying text from a cell in the footer and pasting it into a hyperlink address Windows 10 Copying text from a cell in the footer and pasting it into a hyperlink address Office 2016
Expert
 
Join Date: Aug 2014
Posts: 3,029
gmayor is just really nicegmayor is just really nicegmayor is just really nicegmayor is just really nicegmayor is just really nice
Default

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.
Attached Files
File Type: docm REP446774.docm (33.9 KB, 3 views)
__________________
Graham Mayor - MS MVP (Word) (2002-2019)
Visit my web site for more programming tips and ready made processes www.gmayor.com
Reply With Quote
  #9  
Old 11-14-2019, 03:42 PM
Guessed's Avatar
Guessed Guessed is online now Copying text from a cell in the footer and pasting it into a hyperlink address Windows 10 Copying text from a cell in the footer and pasting it into a hyperlink address Office 2016
Expert
 
Join Date: Mar 2010
Location: Canberra/Melbourne Australia
Posts: 1,388
Guessed is a jewel in the roughGuessed is a jewel in the roughGuessed is a jewel in the roughGuessed is a jewel in the rough
Default

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
Reply With Quote
  #10  
Old 11-14-2019, 11:58 PM
gmayor's Avatar
gmayor gmayor is offline Copying text from a cell in the footer and pasting it into a hyperlink address Windows 10 Copying text from a cell in the footer and pasting it into a hyperlink address Office 2016
Expert
 
Join Date: Aug 2014
Posts: 3,029
gmayor is just really nicegmayor is just really nicegmayor is just really nicegmayor is just really nicegmayor is just really nice
Default

Quote:
Originally Posted by Guessed View Post
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?
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
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Copying text from a cell in the footer and pasting it into a hyperlink address 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
Copying text from a cell in the footer and pasting it into a hyperlink address Maintain formatting while copying text from word, and pasting into a webpage. kdogg121 Word 1 07-07-2009 02:50 AM


All times are GMT -7. The time now is 05:47 PM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2019, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2019 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft