How to Write on Word from Excel using VBA

Video Statistics and Information

Video
Captions Word Cloud
Reddit Comments
Captions
Hey There YouTube danrong with Excel VB fun thank you so much for tuning back in today we have a question from one of our viewers he said hey is there a way to uh basically type onto Excel actually he asked if you could select an actual field from a table that is in word um in this video we are going to delve into taking Excel into Microsoft Word but we're not going to get as specific as locating a certain field inward um from possibly a table in there um but we're going to get into uh the first part we're going to open up Excel and we're going to jump in there and and uh Place some content inside word uh through Excel so of course to get started uh first of all let's type something in uh cell A1 um how's it going and the answer of course is awesome okay so A1 is is jazzed up now so perfect all right now uh what I've learned and yes I I don't know everything so I actually had to do a little research on this one what I learned from scouring the web is that by default uh the word properties are not in Excel you actually have to go under Tools and references and I'm going to show you exactly how to do that to access that library but normally if you try to dim uh something as a Word Microsoft Word do something rather you can't do it it's not even going to show up but if you click on to where my manners hit alt of1 to get here we're in the Visual Basic editor okay um then you go to tools and then references and from here you're going to zoom down quite a bit because they are alphabetized Microsoft uh word 14.0 or whatever Microsoft Word object library is what you want click okay and now check this out so we're going to say we're going to dim the uh Microsoft Word um you could call application or program or whatever you want to call this you could call this blah but it's doesn't mean much to you Dim word app AS word. application and just hit enter the next thing we're going to declare and oh where are my maners again let's erase this what we need to do actually um is create a new module I'm getting ahead of myself aren't I uh go over here and click new module so now we got a new one now we need to create a a procedure which is as easy as typ in the word sub and hitting spacebar so what are we going to name this one we're going to call it um uh shove data to msword that's a crazy long title but that's all right I can do what I want so we're going to again we're going to declare the word app as a word do application we're going to declare W document as word do document pretty self-explanatory right we're just declaring our variables we got W app and W doc all right so it's my understanding that this next step actually opens word when it's run but it word is not visible yet until you make it visible so the next step is uh we're going to set W app uh equals I think it's a create object and in inside there you're going to put a quotes and you're going to say um word. application and spell it right unlike me so create object word. application and that I believe this line one it's run actually opens up Microsoft Word in the background but it's not visible for some reason so then you say w. visible equals true and that shows it up for you um so again this opens up word and this displays it so what we're doing is opening up a uh the application of word uh but we haven't opened up a new document yet okay the next step is kind of like in kind we're going to use the set but we're going to use set um the actual document up so we're going to set W doc or whatever you name this one equals let me try to remember this here um I think we're going to use the actual application and you can put a period after it and it gives you more options so it's going to be W app uh dot not activate do documents. add um and that step is where you actually add a new document and it opens that up and so in in order to affect what's going in there what we want to do is we actually want to shove whatever's in cell A1 you may want to do something different but we want to shove whatever's in A1 or you can automate that if you seen my other seen my other videos you know that you can pretty much customize where you want it to go you you could have it go to the last line using the XL up um or you might want to manipulate whatever's in there and add or subtract based on I don't know you there's all kinds of different ways that people may want that to go alt f11 so we're going to go back here W do dot you notice ever since we declared these two things uh we now have features after we put a period so w doc uh that's our current document that we just opened up dot what is it is it create I know it starts with a C okay it's a I think it's content W doc. content wow you can you can tell I really got this down P huh okay it's insert something rather insert before insert uh insert after okay what are we going to insert we're going to insert let's try A1 let's try range A1 okay let's try this hit f8 with me let's debug through so first of all we're we're opening a word in the background and you notice that step took uh a couple seconds so you know I was doing something pretty big so it just opened up word in the background um invisible equals true so once we hit that and now it's visible you see in the background okay set W Doc is W the application. documents. add so add a new document to the program so hitting f8 and now we've got a new Fresh Word document you can see behind me all right now uh wish me luck here so range A1 we're wanting to take the content of the current document do insert after and we want the thing that we're inserting is whatever's there so let's see what happens hit f8 oh snap says how's it going awesome okay that's actually pretty sweet all right so um let's do it all from scratch let's go ahead and close out word here going to save this uh we'll be sharing this workbook in our shared Dropbox folder so if you don't have access to that check out all my video links pretty much all of them have that um you'll want to be able to have instant access to this stuff so you can play with it so you don't have to retype it all so check it out should be a download link this one is called type onto word from Excel so that's the one you'll want to mess with what I want to do now is just do it all at once without uh actually what I want to do is add to that I'm going to use Amper sand and I'm going to say Playdoh so it's going to say how's it going awesome and then it's going to say Playdoh what so I'm going to hit F5 watch it it's going to happen right now here we go three two one F5 so it's running through everything immediately uh how's it going awesome Play-Doh has just been made on a new word document all right so we're going to play around with more of these fun features probably mess with PDFs and Microsoft Word documents a little bit more it's pretty exciting uh it's new to me um I haven't done a lot of that mostly uh dealt with Excel but we're going to delve into some some new and and um bold too where nobody's excelled or worded before probably not true uh probably a lot of people have messed with this we're going to check it out here on YouTube here on Excel is VBA is fun and uh that'll be awesome thanks for joining me and God [Music] bless [Music] [Music] [Music] he [Music] [Music] a
Info
Channel: ExcelVbaIsFun
Views: 246,478
Rating: undefined out of 5
Keywords: edit word with excel, macro, VBA, Visual Basic, reports, reporting, debug, Computer, excel vba, programming, vba excel, ms excel, ms excel vba, excel visual basic, microsoft visual basic, thisworkbook, object, vba basics, vba tutorial, visual basic tutorial, range, excel, excel tutorial, excel basics, easy excel, easy vba, easy visual basic, free, tutorial, lesson, hd, training, how to, data, how to microsoft office, office 2010, ms office, excel to word
Id: -2oPxHmAdtA
Channel Id: undefined
Length: 12min 17sec (737 seconds)
Published: Fri Aug 16 2013
Related Videos
Note
Please note that this website is currently a work in progress! Lots of interesting data and statistics to come.