Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 11-15-2022, 02:38 AM
Caerleon Caerleon is offline Batch generating word template with CC from excel data Windows 7 64bit Batch generating word template with CC from excel data Office 2007
Novice
Batch generating word template with CC from excel data
 
Join Date: Sep 2015
Location: London UK
Posts: 5
Caerleon is on a distinguished road
Default Batch generating word template with CC from excel data

Hello,
Seem to have fallen foul of the initial posting and in an attempt to post had to delelte most of what i wrote...





Anyway, getting back on point.


I have an excel form with headings and over 3,000 entries in rows. Data from some of those fields are in text and numbers and I want to put that data into a saved .docm template and save as a .docx as there is other data to add, but the creation of the intial document will complete about 70% of my workload. I knew that this can be done with word, but kept hitting issues with my code. I have since doing research found that excel also has the function to push data to word and seems to be noted and better supported.


I want to put the data from the excel cells into a CC .docm and save it with TWO text ref from the excel fields e.g. REF/LOCATION (LONESN12345 Chingford Green.Docx). I have found some excellent code which was on this site from gmayor and I am trying to understand what goes where and how.



Going back to basics and relearning VBA will take me more time that I am trying to save (age related brain storage issues), so any assistance would be greatly appriciated.

Last edited by Caerleon; 11-15-2022 at 08:34 PM.
Reply With Quote
  #2  
Old 11-15-2022, 08:46 PM
Caerleon Caerleon is offline Batch generating word template with CC from excel data Windows 10 Batch generating word template with CC from excel data Office 2016
Novice
Batch generating word template with CC from excel data
 
Join Date: Sep 2015
Location: London UK
Posts: 5
Caerleon is on a distinguished road
Default

Sub PopulateCCs()
Const sPath As String = "I:\COL\COL Passport - Template.docm" 'the full path of the document
Dim oCC As Object
Dim wdApp As Object
Dim wdDoc As Object
Dim LastRow As Long, lngIndex As Long
Dim xlSheet As Worksheet
Dim sValue As String

On Error Resume Next
Set wdApp = GetObject(, "Word.Application")
If Err Then
Set wdApp = CreateObject("Word.Application")
End If
On Error GoTo 0
ActiveWorkbook.Save
Set wdDoc = wdApp.Documents.Open(sPath)

wdApp.Visible = False

Set xlSheet = ActiveSheet
With xlSheet
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
For lngIndex = 2 To LastRow
For Each oCC In wdDoc.ContentControls

wdDoc.ContentControls("Location").Range.Text = Sheets("H").Cells(r, 2)
wdDoc.ContentControls("COLREF").Range.Text = Sheets("A").Cells(r, 2)
wdDoc.ContentControls("OCU").Range.Text = Sheets("X").Cells(r, 2)
wdDoc.ContentControls("Address").Range.Text = Sheets("H").Cells(r, 2)
wdDoc.ContentControls("ukgridref").Range.Text = Sheets("New Reference").Cells(r, 2)
wdDoc.ContentControls("Northings").Range.Text = Sheets("K").Cells(r, 2)
wdDoc.ContentControls("Eastings").Range.Text = Sheets("J").Cells(r, 2)
wdDoc.ContentControls("Latitude").Range.Text = Sheets("New Reference").Cells(r, 2)
wdDoc.ContentControls("Longitude").Range.Text = Sheets("New Reference").Cells(r, 2)
wdDoc.ContentControls("ESN Users").Range.Text = Sheets("New Reference").Cells(r, 2)

If UCase(oCC.Title) = UCase(.Cells(lngIndex, 1)) Then
oCC.Range.Text = .Cells(lngIndex, 2)

Exit For
End If
Next oCC
Next lngIndex

End With

lbl_Exit:
Set wdApp = Nothing
Set wdDoc = Nothing
Set oCC = Nothing
Exit Sub
End Sub
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Copy excel userform data to a word template Farnkf1970 Excel Programming 1 10-31-2017 01:35 AM
Add data from two Excel Files to Word Doc Template nivolaped Word 4 04-10-2016 04:23 PM
Batch generating word template with CC from excel data Batch create word files with link to changing excel hannes.ledegen Mail Merge 8 04-07-2016 01:22 AM
Batch generating word template with CC from excel data Automatically generating paragraphs with form data mkellar Word 1 03-22-2013 10:10 PM
Batch generating word template with CC from excel data Generating multiple documents from 1 data source themangoagent Word 2 08-14-2009 12:12 PM

Other Forums: Access Forums

All times are GMT -7. The time now is 02:38 PM.


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