#1
|
|||
|
|||
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?
|
#2
|
||||
|
||||
What have you tried? FWIW, you don't necessarily even need VBA for this.
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
#3
|
|||
|
|||
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 Also, I'm new to this kind of thing and I have not done word doc manipulation from excel. |
#4
|
||||
|
||||
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] |
#5
|
|||
|
|||
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 |
#6
|
||||
|
||||
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] |
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 |
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 |