#1
|
|||
|
|||
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. |
#2
|
|||
|
|||
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 |
|
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 create word files with link to changing excel | hannes.ledegen | Mail Merge | 8 | 04-07-2016 01:22 AM |
Automatically generating paragraphs with form data | mkellar | Word | 1 | 03-22-2013 10:10 PM |
Generating multiple documents from 1 data source | themangoagent | Word | 2 | 08-14-2009 12:12 PM |