![]() ' Tell Word to paste the contents of the clipboard into the new document ' Copy the data for the new document to the clipboard Range("C10").PasteSpecial Transpose:=True ' Copy data columns, transpose and paste in C10:C13 Range("A" & i).Copy Destination:=Sheets("Template").Range("C4") 'Find the last row with data in the database Set appWD = CreateObject("Word.Application.8") ' Create a new instance of Word & make it visible ' See VB Help topic "Controlling One Microsoft Office Application from Another" ' in the VB editor to execute Word commands. ' You must pick Microsoft Word 8.0 from Tools>References In fact, since I have never written a line of Word Macro code before, I went to Word, recorded the actions, then copied that code into Excel, adding the prefix before each line. For any commands in the Excel Macro which you want to apply to the Word application, you simply prefix the command with appWD. To control Word from Excel, you need to define a variable to represent the Word application. Scroll down to find "Microsoft Word 8" and check the box next to select this item. Since we want to issue Word commands here, Go to Tools > References. Columns B:E of the database will go in cells C10:C13. In this case, I will want to copy the name from column A of the database to cell C4 on the template. The Template worksheet has the structure of the Word Document that I want to create. ![]() The Data worksheet has many rows of data. I created a workbook with two sheets - one called Data and one called Template. The example below is fairly straightforward, but one would be able to build upon this concept to do fairly advanced applications.įirst, let's take a look at a sample data set. You could do this from Word using Mail Merge, but controlling Word from Excel is an interesting prospect. Jake - what a great idea! I often have sales results for the entire company and it would be great to be able to send each rep just his or her information in Word. How can I write a macro which will take Excel spreadsheet data and create a Word file for each row of data? Thanks to Jake who provided this week's Excel question:
0 Comments
Leave a Reply. |