Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 02-08-2021, 09:35 PM
Bumba Bumba is offline How to add date from excel to word document header? Windows 7 32bit How to add date from excel to word document header? Office 2007
Novice
How to add date from excel to word document header?
 
Join Date: Jan 2019
Posts: 26
Bumba is on a distinguished road
Default How to add date from excel to word document header?


Say I have a worksheet where in cell D2 there is a date in format dd-mmm-yyyy. How do I copy that date and paste it to a header of a word document keeping source date format using vba?
Reply With Quote
  #2  
Old 02-09-2021, 01:14 AM
macropod's Avatar
macropod macropod is offline How to add date from excel to word document header? Windows 10 How to add date from excel to word document header? Office 2016
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 21,956
macropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond repute
Default

What have you tried? FWIW, you don't necessarily even need VBA for this.
__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
Reply With Quote
  #3  
Old 02-09-2021, 01:32 AM
Bumba Bumba is offline How to add date from excel to word document header? Windows 7 32bit How to add date from excel to word document header? Office 2007
Novice
How to add date from excel to word document header?
 
Join Date: Jan 2019
Posts: 26
Bumba is on a distinguished road
Default

Something like this,

Code:
Dim wrdApp          As Object, wrdDoc As Object
On Error Resume Next
Set wrdApp = GetObject(Class:="Word.Application")

If wrdApp Is Nothing Then
    Err.Clear
    Set wrdApp = CreateObject(Class:="Word.Application")
    
    If wrdApp Is Nothing Then
        MsgBox "Microsoft Word could Not be found - Aborting"
        Exit Sub
    End If
End If
On Error GoTo 0
Application.ScreenUpdating = FALSE
Application.EnableEvents = FALSE
With wrdApp
    .Application.ScreenUpdating = FALSE
    Set wrdDoc = .Documents.Open("D: \08-02-21\test.docx")
    .Visible = TRUE
    .Activate
    
    Dim HdrText     As String
    Dim BoldRange   As Range
    Dim HdrRange    As Range
    
    'Set Variable equal to Header Range
    Set HdrRange = wrdDoc.Sections.Item(1).Headers(wdHeaderFooterPrimary).Range
    
    ‘Data To add To Header
    HdrText = ThisWorkbook.Sheets("details").Cells(4,2).Value
    
    'Add Text To Word Header
    HdrRange.Text = HdrText
    
    'Bold Only First Sentence in Header
    Set BoldRange = HdrRange.Words(1)        'Get First Word
    BoldRange.Expand (wdSentence)        'Expand To Entire Sentence
    BoldRange.Font.Bold = TRUE        'Bold Entire Sentence
End With
But getting error at wdHeaderFooterPrimary.

Also, I'm new to this kind of thing and I have not done word doc manipulation from excel.
Reply With Quote
  #4  
Old 02-09-2021, 03:11 AM
macropod's Avatar
macropod macropod is offline How to add date from excel to word document header? Windows 10 How to add date from excel to word document header? Office 2016
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 21,956
macropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond repute
Default

Well, if it's always the same Excel cell and same Word document - as your code implies - you don't need VBA at all. All you need to do is to set up an OLE link That's as simple as copying the Excel cell, switching to Word, selecting the destination and choosing Edit|Paste Special, with the 'paste link' option and choosing a suitable paste format. Done. From now on, the Word document will update to reflect any changes to the Excel cell.

PS: The error is because you're trying to use a named constant with late binding. You need to either:
• use early binding;
• define that constant; or
• substitute the name with its value.
__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
Reply With Quote
  #5  
Old 02-09-2021, 03:18 AM
Bumba Bumba is offline How to add date from excel to word document header? Windows 7 32bit How to add date from excel to word document header? Office 2007
Novice
How to add date from excel to word document header?
 
Join Date: Jan 2019
Posts: 26
Bumba is on a distinguished road
Default

Well, there are other things that I am going to copy from that sheet to the word document but that part is sorted and now I just want to add the date to the header along with some text each time I run the code. Also instead of copying the date from a cell it also can be made like it picks the date from the computer date when I run the macro i.e. If I run the code today it will paste today's date in the word document header and it never changes.
Hope this clears your queries
Reply With Quote
  #6  
Old 02-09-2021, 03:23 AM
macropod's Avatar
macropod macropod is offline How to add date from excel to word document header? Windows 10 How to add date from excel to word document header? Office 2016
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 21,956
macropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond repute
Default

Again, if those 'other things' are consistent ranges - or even named ranges that might change size - the same approach works without the need for any code.
__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Extract Document ID and description from header in multiple Word documents and paste in new word doc venkat_m Word VBA 2 05-23-2020 03:57 AM
How to add date from excel to word document header? Calculate week in Word document based on date entered into same document ArviLaanemets Word VBA 4 11-18-2019 12:25 AM
Document attached to Word Header MidgesMom Word 0 11-13-2018 10:58 AM
Convert csv document to excel, format date coba Excel Programming 3 01-07-2016 04:18 AM
How to add date from excel to word document header? word 2007 - Inserting value in the document header chamdan Word VBA 7 11-15-2013 05:06 PM

Other Forums: Access Forums

All times are GMT -7. The time now is 08:32 AM.


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