How to add date from excel to word document header?

Bumba · #1 02-08-2021, 09:35 PM

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?

macropod · #2 02-09-2021, 01:14 AM

What have you tried? FWIW, you don't necessarily even need VBA for this.

Bumba · #3 02-09-2021, 01:32 AM

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.

macropod · #4 02-09-2021, 03:11 AM

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.

Bumba · #5 02-09-2021, 03:18 AM

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

macropod · #6 02-09-2021, 03:23 AM

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.

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
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
word 2007 - Inserting value in the document header	chamdan	Word VBA	7	11-15-2013 05:06 PM