Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel Macro to Auto-Populate a Word Template
I am using Excel to pull data together from multiple sources, and am trying
to set it up as, essentially, a dashboard for a user. I have it set so that the user hits a button and all the data that they need is automatically pulled in to the workbook. However, I have a MS Word form that I would like the data to be pushed to, and I cannot figure out how to write the macro to automatically populate that particular form. I would normally just write the macro so that it created the document from scratch every time, but there is a LOT of static data in the form, and it changes often. I am using Excel 2003 Can anyone provide an example of the code for how to do this? I am very new to VBA programming (and not a programmer by trade), so an example should allow me to figure out how to make it work in this particular instance. Thanks in advance for all your help! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel Macro to Auto-Populate a Word Template
Dan: If the data is the same data points each time, just different data,
then create a template in Word (a Word doc saved as ".dot") that has all of your data point locations as bookmarks. In your Excel macro, set an object to Word and open a new doc based on that template (see http://word.mvps.org/FAQs/InterDev/C...ordFromXL.htm). Then iterate through the document's Bookmarks collection and set your data into each bookmark, then SaveAs with your doc name. HTH Ed "Dan Thorman" wrote in message ... I am using Excel to pull data together from multiple sources, and am trying to set it up as, essentially, a dashboard for a user. I have it set so that the user hits a button and all the data that they need is automatically pulled in to the workbook. However, I have a MS Word form that I would like the data to be pushed to, and I cannot figure out how to write the macro to automatically populate that particular form. I would normally just write the macro so that it created the document from scratch every time, but there is a LOT of static data in the form, and it changes often. I am using Excel 2003 Can anyone provide an example of the code for how to do this? I am very new to VBA programming (and not a programmer by trade), so an example should allow me to figure out how to make it work in this particular instance. Thanks in advance for all your help! |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel Macro to Auto-Populate a Word Template
That's a bit of a poser. Normally I would use the strategy of pulling the
data straight into Word and let the Word Document run the process, since it is the furthest downstream. However, if Excel is running things I can at least steer you in the right direction, I hope. This is designed so just about everything is done by the properties of the document and only minimal VBA is needed. 1. Set up the Word document as a mail-merge drawing from your spreadsheet (this works best if your data is pulled to a consistent place in the Excel Sheet). 2. Create a macro in the Word document (not in Normal.dot) something like this: Sub AutoOpen() ActiveDocument.Fields.Update End Sub If you have set up the mailmerge fields properly, this should update everything to match your data source (i.e. the Excel document). Bob's your uncle. You may have to create a tab in your Excel document called "datatab" or something like that to hold the data in a form Word likes. Now, as to opening the Word document from Excel, I must leave that to the Cross-Application wizards; I am a single-application geek. I hope this helps somewhat... Tim Smith. "Dan Thorman" wrote: I am using Excel to pull data together from multiple sources, and am trying to set it up as, essentially, a dashboard for a user. I have it set so that the user hits a button and all the data that they need is automatically pulled in to the workbook. However, I have a MS Word form that I would like the data to be pushed to, and I cannot figure out how to write the macro to automatically populate that particular form. I would normally just write the macro so that it created the document from scratch every time, but there is a LOT of static data in the form, and it changes often. I am using Excel 2003 Can anyone provide an example of the code for how to do this? I am very new to VBA programming (and not a programmer by trade), so an example should allow me to figure out how to make it work in this particular instance. Thanks in advance for all your help! |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel Macro to Auto-Populate a Word Template
This seems like the way to go. So, seeing as how new I am to all of this, I
have been looking for some code examples of how to iterate through the bookmarks in the template i have created and populate them with cells from the Excel spreadsheet. Unfortunately, I cannot seem to find any such examples. Does anybody happen to know of any? P.S. Thanks for your help Ed! "Ed" wrote: Dan: If the data is the same data points each time, just different data, then create a template in Word (a Word doc saved as ".dot") that has all of your data point locations as bookmarks. In your Excel macro, set an object to Word and open a new doc based on that template (see http://word.mvps.org/FAQs/InterDev/C...ordFromXL.htm). Then iterate through the document's Bookmarks collection and set your data into each bookmark, then SaveAs with your doc name. HTH Ed "Dan Thorman" wrote in message ... I am using Excel to pull data together from multiple sources, and am trying to set it up as, essentially, a dashboard for a user. I have it set so that the user hits a button and all the data that they need is automatically pulled in to the workbook. However, I have a MS Word form that I would like the data to be pushed to, and I cannot figure out how to write the macro to automatically populate that particular form. I would normally just write the macro so that it created the document from scratch every time, but there is a LOT of static data in the form, and it changes often. I am using Excel 2003 Can anyone provide an example of the code for how to do this? I am very new to VBA programming (and not a programmer by trade), so an example should allow me to figure out how to make it work in this particular instance. Thanks in advance for all your help! |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel Macro to Auto-Populate a Word Template
Here is one, you'll have to wade through and use what is relevant to your
needs... this shows both manual population of two bookmarks, and also populating a series of bookmarks by looping through an array. HTH Keith Sub MakeGuideA() Dim wrdApp As Word.Application Dim wrdDoc As Word.Document Set wrdApp = CreateObject("Word.Application") Dim BMRange As Word.Range Dim toc As Word.TableOfContents wrdApp.Visible = True wrdApp.DisplayAlerts = wdAlertsNone Set wrdDoc = wrdApp.Documents.Open(CurrentPath & "Interview_GuideA" & ".doc", , True) 'word operations With wrdDoc For deleteComp = 1 To 18 Bkmksize = .Bookmarks.Count thisVal = Application.WorksheetFunction.Max(WordArray) thismatch = Application.Match(thisVal, WordArray, 0) If IsError(thismatch) Then Exit For Else BookMarkName = .Bookmarks(thisVal).Name If thisVal 0 Then Set BMRange = wrdDoc.Bookmarks(BookMarkName).Range BMRange.Text = "" .Bookmarks.Add BookMarkName, BMRange End If WordArray(thismatch) = "" End If Next BookMarkName = .Bookmarks(2).Name Set BMRange = wrdDoc.Bookmarks(BookMarkName).Range BMRange.Text = ApplicantName .Bookmarks.Add BookMarkName, BMRange BookMarkName = .Bookmarks(17).Name Set BMRange = wrdDoc.Bookmarks(BookMarkName).Range BMRange.Text = PositionString .Bookmarks.Add BookMarkName, BMRange For Each toc In wrdDoc.TablesOfContents toc.Update Next wrdDoc.SaveAs (CurrentPath & ApplicantName & ".doc") .Close ' close the document End With wrdApp.Quit ' close the Word application Set wrdDoc = Nothing Set wrdApp = Nothing End Sub "Dan Thorman" wrote in message ... This seems like the way to go. So, seeing as how new I am to all of this, I have been looking for some code examples of how to iterate through the bookmarks in the template i have created and populate them with cells from the Excel spreadsheet. Unfortunately, I cannot seem to find any such examples. Does anybody happen to know of any? P.S. Thanks for your help Ed! "Ed" wrote: Dan: If the data is the same data points each time, just different data, then create a template in Word (a Word doc saved as ".dot") that has all of your data point locations as bookmarks. In your Excel macro, set an object to Word and open a new doc based on that template (see http://word.mvps.org/FAQs/InterDev/C...ordFromXL.htm). Then iterate through the document's Bookmarks collection and set your data into each bookmark, then SaveAs with your doc name. HTH Ed "Dan Thorman" wrote in message ... I am using Excel to pull data together from multiple sources, and am trying to set it up as, essentially, a dashboard for a user. I have it set so that the user hits a button and all the data that they need is automatically pulled in to the workbook. However, I have a MS Word form that I would like the data to be pushed to, and I cannot figure out how to write the macro to automatically populate that particular form. I would normally just write the macro so that it created the document from scratch every time, but there is a LOT of static data in the form, and it changes often. I am using Excel 2003 Can anyone provide an example of the code for how to do this? I am very new to VBA programming (and not a programmer by trade), so an example should allow me to figure out how to make it work in this particular instance. Thanks in advance for all your help! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
excell auto-populate word? | Excel Worksheet Functions | |||
How can I have Excel cells populate a Word label template? | Excel Discussion (Misc queries) | |||
Enable Word Template Macro from Excel | Excel Programming | |||
export excel data to a word template using a macro | Excel Programming | |||
How to auto increment a template in Excel & Word | Excel Programming |