In Episode 943, I took data from Word and pasted to Excel for sorting. Now, I need to "type" that data back into Word, using the proper Style for each paragraph. Episode 944 shows you an Excel macro to automate this process.
This video is the podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!
This video is the podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!
Transcript of the video:
Hey. Alright. Welcome back to the MrExcel netcast. I’m Bill Jelen.
Well, this is part 2.
Yesterday, I started out with this problem where I had data in Word.
I needed to sort that data in Word, so I brought it to Excel, ran a few macros, and then sorted the data in Excel.
Now, I need to get this data from column E back to Word, and I need to use the same styles, these Word styles, that we had in the original documents.
That way, my editor doesn’t realize that I've taken the data somewhere and moved it back.
I want it to look just like they set that up.
So, I’m going to switch over to VBA.
I use Excel VBA all the time but we're doing something different here.
We’re actually using some Word VBA.
Before you can do that, you have to come up to TOOLS and then REFERENCES, and you're going to search down in the Ms for Microsoft Word.
I've already checked it so it's up here at the top, Microsoft Word, and then some version number -- for Word 2003, it’s 11; for Word 2007, it’s 12 -- Object Library.
You’ll check that and then you're going to be able to use Word VBA commands right here from Excel.
So, this is pretty cool.
I want to run this slowly so you can see what's happening.
I’m going to use the F8 key to run one line at a time.
Basically, what I do is I generate a new Word application -- Dim wrdApp As New Word.Application -- and then set that = to VISIBLE.
That fires upward just to no document at all.
I then use Documents.Open.
Now, in Word VBA, it’d be Documents.Open, but because I’m here in Excel VBA, I need to prefix it with the name and the application.
So, I have an object variable for the application -- wrdApp.Documents.Open -- have it open my file, and then set up a reference to the data on this worksheet.
I want to take all the data from row 3 down to row 702, and I’m going to do TypeParagraph in the Word document, and then type the text.
So, what this is doing, this is saying, take the data from row 3 column 5.
Let's take a look at that in Excel.
Row 3 column 5 is this absolute value.
In the Word document, we're here in a brand new paragraph.
Now, it happens that this is the data that I left from the old Word document but let's run this line of code.
It types it in just a regular, normal style, and then the next line I use Selection.Style and specify the style from column 4.
So, we'll press F8 to run that and you see that it changed the style.
Let's run through the loop one more time, and it typed the next paragraph and changed the style back to function description.
Run it a few more times pressing F8 and you see that we get all of those items.
Now, this was the original text that we started with.
I'll delete that, run it a few more times, and I see that it seems to be working very well.
We'll let it go.
Now, it has to basically type 700 paragraphs over to Word and so, you know, we're taking it from Excel, typing the paragraphs in Word.
That'll take a minute or two and I’m not going to bore you.
We’ll cut here.
There we are.
So, that was about a minute and a half.
We’ll switch over to Word and you see that I now have a 23-page document.
700 paragraphs have been typed over here, all with the correct styles.
What a great way it is to create a massive Word document using some data that we already had in Excel.
Well, in this case, it started out in Word.
We brought it to Excel.
I’m going to save this macro.
This is going to be one of my general purpose macros that I’m probably going to pull out again.
Next time I need to take a lot of data and format it nicely from Excel to Word, we'll pull this macro back out.
So, cool little trick there using a little bit of Word VBA right here in our Excel document.
You know, I know lots of people are good at Excel VBA and I’m sure there's people good at Word VBA.
It's tough to see someone who can cross over and do them both, but, as you can see in this case, fairly simple.
Just have to figure out about 3 or 4 commands here in Word VBA and the rest of it was all simple Excel VBA.
Well, hey.
I want to thank you for stopping by.
We'll see you next time for another netcast from MrExcel.
Well, this is part 2.
Yesterday, I started out with this problem where I had data in Word.
I needed to sort that data in Word, so I brought it to Excel, ran a few macros, and then sorted the data in Excel.
Now, I need to get this data from column E back to Word, and I need to use the same styles, these Word styles, that we had in the original documents.
That way, my editor doesn’t realize that I've taken the data somewhere and moved it back.
I want it to look just like they set that up.
So, I’m going to switch over to VBA.
I use Excel VBA all the time but we're doing something different here.
We’re actually using some Word VBA.
Before you can do that, you have to come up to TOOLS and then REFERENCES, and you're going to search down in the Ms for Microsoft Word.
I've already checked it so it's up here at the top, Microsoft Word, and then some version number -- for Word 2003, it’s 11; for Word 2007, it’s 12 -- Object Library.
You’ll check that and then you're going to be able to use Word VBA commands right here from Excel.
So, this is pretty cool.
I want to run this slowly so you can see what's happening.
I’m going to use the F8 key to run one line at a time.
Basically, what I do is I generate a new Word application -- Dim wrdApp As New Word.Application -- and then set that = to VISIBLE.
That fires upward just to no document at all.
I then use Documents.Open.
Now, in Word VBA, it’d be Documents.Open, but because I’m here in Excel VBA, I need to prefix it with the name and the application.
So, I have an object variable for the application -- wrdApp.Documents.Open -- have it open my file, and then set up a reference to the data on this worksheet.
I want to take all the data from row 3 down to row 702, and I’m going to do TypeParagraph in the Word document, and then type the text.
So, what this is doing, this is saying, take the data from row 3 column 5.
Let's take a look at that in Excel.
Row 3 column 5 is this absolute value.
In the Word document, we're here in a brand new paragraph.
Now, it happens that this is the data that I left from the old Word document but let's run this line of code.
It types it in just a regular, normal style, and then the next line I use Selection.Style and specify the style from column 4.
So, we'll press F8 to run that and you see that it changed the style.
Let's run through the loop one more time, and it typed the next paragraph and changed the style back to function description.
Run it a few more times pressing F8 and you see that we get all of those items.
Now, this was the original text that we started with.
I'll delete that, run it a few more times, and I see that it seems to be working very well.
We'll let it go.
Now, it has to basically type 700 paragraphs over to Word and so, you know, we're taking it from Excel, typing the paragraphs in Word.
That'll take a minute or two and I’m not going to bore you.
We’ll cut here.
There we are.
So, that was about a minute and a half.
We’ll switch over to Word and you see that I now have a 23-page document.
700 paragraphs have been typed over here, all with the correct styles.
What a great way it is to create a massive Word document using some data that we already had in Excel.
Well, in this case, it started out in Word.
We brought it to Excel.
I’m going to save this macro.
This is going to be one of my general purpose macros that I’m probably going to pull out again.
Next time I need to take a lot of data and format it nicely from Excel to Word, we'll pull this macro back out.
So, cool little trick there using a little bit of Word VBA right here in our Excel document.
You know, I know lots of people are good at Excel VBA and I’m sure there's people good at Word VBA.
It's tough to see someone who can cross over and do them both, but, as you can see in this case, fairly simple.
Just have to figure out about 3 or 4 commands here in Word VBA and the rest of it was all simple Excel VBA.
Well, hey.
I want to thank you for stopping by.
We'll see you next time for another netcast from MrExcel.
