Thanks again to Jake for this great question. ' Save the new document with a sequential file nameĪ FileName:="File" & iĪfter running this macro, you will have one new Word file for each row of data on your data sheet. ' 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
Macro to insert text in word document code#
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? I try my best to help everyone out, but sometimes I don't have time to fit everyone's questions in (there never seem to be quite enough hours in the day!).Thanks to Jake who provided this week's Excel question: I can guarantee 9 times out of 10, one of my strategies will get you the answer(s) you are needing faster than it will take me to get back to you with a possible solution. I highly recommend that you check this guide out before asking me or anyone else in the comments section to solve your specific problem. That's why I want to share with you: My Guide to Getting the Solution to your Problems FAST! In this article, I explain the best strategies I have come up with over the years to getting quick answers to complex problems in Excel, PowerPoint, VBA, you name it!
We all have different situations and it's impossible to account for every particular need one might have. How Do I Modify This To Fit My Specific Needs?Ĭhances are this post did not give you the exact answer you were looking for.
0 kommentar(er)
