How To Export Data To Microsoft Word From Access 2013

hello everyone and welcome to access 2013 how to my name is Steve Bishop and in this video we're going to talk about how to export data to a Microsoft Word document so we know that access has a wonderful reporting facility in it that even allows us to export our reports to Microsoft Word but sometimes you have a Microsoft Word document that's already pre-created or drafted up and you just want to take some of the data from out of access and insert it into Microsoft Word now what I'm going to show you is a very simple example this isn't going to be really complicated I'm not going to be putting like full tables into Microsoft Word or doing any sort of special you know development within Microsoft Word I'm just going to show you a basic inserting of data from an access table into a Microsoft Word document now this is especially helpful for things like like newsletters or inserts that you might want to address to various customers and send those mailers out to your customers and you just want to include things like the customer name or their address and that sort of thing so without further delay let's go ahead and hop into our Access database so for this example I have three records in a table called table names or TBL names and you can see I just have an Auto numbered ID field with a first name middle name and a last name and I have three records here of fictitional characters I also have a word document here and looking at the word document you can see a place that we clearly would want to put the first name middle name and a last name of each record that we have in our table as well as an actual sentence here that's been typed out where I probably want to insert some information like the first name of the person so our first example would be like Alfred Newman so I'd want Alfred here efore middle name and Newman for last name and then I'd want to say say for example hello Alfred I am writing you because I have nothing better to do so that's the example that I've set up for you in this demonstration so the first thing we need to do is prepare our word document we need to put some sort of indication or marker of where within this word document do we want to put each one of those pieces of information and the way that you do that is by using a special thing called bookmarks you can get to the bookmarks by going to the insert tab and then right here you can see under links it says bookmark and if you click on bookmark you can go ahead and add a bookmark I'm not ready just yet to do that though so I want to cancel out of this and I need to place the cursor in the location that I want my bookmark to be so here just after first name colon with a little space there right where the cursor is flashing is where I want to insert my bookmark so I'm going to leave my cursor there and I'm going to click on bookmark and now up here in this little text area I can type out the name of what I want to name this bookmark I'm just going to call it first name and then I'll click on add now the dialog window goes away so that you can go back to the document and we can add another one so I'm going to do one here for the middle name I'll just click on bookmark again and you can see there's my first bookmark that I created called first name and you do need to be careful because if I just click on add that would actually just move this first name bookmark to the new cursor location so I need to be sure to type in some sort of new name for this bookmark that's just going to be middle name we'll click on add again then I'll do the same thing for a last name and finally down here after hello but before the comma I'm going to add one more bookmark and I'm going to call this greeting and click on add now the bookmark dialog is very easy to use and I like some of this you can if you're kind of lost on where for example the first name bookmark is you can select it and then click on the go to button and that will actually go to it'll move your cursor to that location of where that bookmark is so now that it's at first name if I click on bookmark again and I say I want to find out where the greeting bookmark is and click on go to you can see that it jumps the cursor down here to where that greeting bookmark is another really nice thing is that you can actually sort these according to their location within the document so you can see that the first bookmark is called first name the second bookmark is middle name and then last name and then greeting so just a couple of nice little things you can do in order to find out exactly where those bookmarks are located within your word document okay so now that we've got our bookmarks let's go ahead and close that and save our document and now I'm going to go back to the Access database let's go ahead and go into our Visual Basic window and I'm going to go ahead and create a new module and in this module I'm going to create a public subroutine we'll call this export names to word and I'm not going to pass any any parameters here now in order for me to work with Word documents inside of VBA code I need to bring in the references to Microsoft Word so I need to go to up to tools references and then I'm going to scroll down here to Microsoft we're looking for Microsoft Word now I have the 15.0 object library because I'm using office 2013 for this demonstration but you might have a different number there for your version number just make sure that it's the Microsoft Word object library and then I'm going to go ahead and click on OK I need to go ahead and dim a couple of objects so that we can work with our Word document the first object I'm going to call W app and it's a type of word application okay so we're actually opening up a version or an instance of Microsoft Word then I'm going to go ahead and dim and a W doc as a type of Word document so this is going to be actual document that we open up so we can go ahead and set our W app object equal to a new instance of word application and to go and set up our Word documents the actual document were going to work with you have to set it equal to some sort of result back so we're going to do w app and within the W app application there is a collection called documents and on the documents collection there is not the add function or the add method but we want to do the open method and you can see on the open method if I put the parentheses here we get the intellisense that tells us that we need to pass in a file name which is just a string but you can see at the very end of the intellisense it returns back as document so the result from the open method is that it returns back a type of document and that just so happens to be a word document which we are creating here as a as W doc so W doc is an object of a type Word document so when this open method opens up that file it's going to return a type of document and we can set it equal to word to the W doc so I need to go out and get the file name here and I'm just going to copy this from my Windows Explorer whoops wrong window and we'll just paste this in here and I know that the name of my document is my letter dot do see X and that will open up that particular document and I could of course do all these other fancy little parameters here if I have passwords or you know any four XML transforms or stuff like that I don't need to do any of that I'm just going to go ahead and open the document without passing any of those extra parameters okay so now that I have my word document open I also need to go out and get the data and put it in some sort of object okay so the way I'm going to do that is something you guys should all be familiar with by now I'm just going to go ahead and dim a record set as and I'm going to specify Dao dot record set and then we're going to go ahead and set our record set equal to current DB open record set and I'm just going to pass in the table names now this could be a query this could be a string query so you could do a whole select statement if you wanted to or some sort of saved query in Access but I'm just going to go ahead and access the table directly and now I'm just going to run through my record set so we'll do if not our s end of file so we're just checking to see do we have any records in the record set if we do then our s move to the first one all right then we'll do until our SEO F so we'll loop through our record sets until we reach the end and for each iteration we need to move the cursor to the next record and before we do that move to the next record this is where we can put our code in it's going to take the data from our record set and insert it into our Word document now the way you do that is by accessing on the word document a collection of bookmarks so that's that bookmarks collection of all of those bookmarks that we created here in our word document and if we take a look at it we can see that we again have them all named here and it just so happens that on the bookmarks collection since it is a collection it's an indexed collection and that means I can pass along the name of the bookmark I want to get to so for this case in the first example I'm going to do first name so the first bookmark is first name and on the bookmark that I've selected their first name there's a special property called range and within the range property there's another property called text so this is where I can actually set the text of the range of that bookmark and then I can assign that whatever the text is that I want which would be coming from our record set first name you know a couple of things that I should probably do here or at least one thing I should do here is I should do a null to zero on this just in case the data that comes from the first name is null I don't try to pass in a null value to that text property because that will cause an error so doing a null to zero and then saying that if it is null pass in a string an empty string then that will basically eliminate the potential for any particular problem from that I'm just going to go ahead and copy this and I know that I'm going to need to do this four times so there's one two three and four and now we'll just go through here and we'll fix the name so this is going to be middle name because the middle name bookmark is the one that I put the middle name from the record set too so I'll do middle name bookmark gets the middle name from our record set and same thing for last name and then finally this last bookmark was called greeting right so we have a greeting bookmark that's going to be where the hello and then the comma and I'm just going to use the first name of the person that we're sending this to now after we change the data in our bookmarks let's go ahead and save the document so we'll do W doc save and I'm going to do save as too and the save as to method just takes a I can specify the new file name of where I want to save this as so that way I keep the original document intact here and I don't have to you know worry about you know saving it and then moving it using a file system object or anything like that I could just use the just keep saving it as a new document each time that I have a new customer that I want to address this to so we'll go ahead and take this folder path here that I used up above oops forgot my space and we'll just concatenate this together I'm going to do the record set ID so I'm just going to make sure that this is a unique I'm just going to use the ID value for the name of each document that gets created and we'll concatenate onto that my letter dot do CX and that will save each iteration each time that I put this data in it will save it as a new or document so let's go ahead and save that and I need to actually save this as export to word and I need to actually close out of my word document now before I can run this because if you have the word document open then this will have a hard time opening the document it'll say that it's already opened do you want to open it as read as and obviously if we're going to change the data inside of the document we can't open it as read as so we need to make sure we're out of the Word document before we try to run this so let's go ahead and do that let's run export names to Word and see what we get so if I just look inside my folder here we can see one my letter D ocx look at that Alfred E Newman hello Alfred I'm writing you because I have nothing better to do so that worked on the first one let's take a look at the second one here and this is au Azure Alfred E a high Newman hello Zor Alfred that's not what we wanted we didn't want to concatenate all that and that's because what's happening here is we're just simply re inserting the next value from each record we're inserting it so it's not overwriting it and it's not taking out or deleting or replacing the original text so we need to do something like that and I can just confirm that here that it does it again in the third one by putting all three of the first and last names together so that's not the behavior we're looking for let's just go ahead and delete those and so what we need to do is after we've saved the document we need to go ahead and delete what's in there so before we move on to the next record let's go ahead and do w doc actually I'm going to copy all four of these and we'll paste this again so all four of these bookmarks we need to go in and delete the text that's in there now the way you do that is on the range property instead of doing dot text you can do dot delete and the delete method takes a type of unit and a count now the unit could be things like the number of words or the number of characters and in this case I wanted to delete the same number of characters that we inserted so I'm just going to go ahead and do WD character and that is an enumerable so that's just an enum value of WD character that indicates that I want to delete by the specified number of characters and the number of characters I want to delete after the bookmark is going to be oops the length that we get from null to zero of the first name so just like that now let's go to this next one and we'll do the same thing so we'll do for middle name we want to delete WD character the length Knowle to zero of middle name and the same thing for last name delete WD character length of the null to zero last name and finally let's get this greeting tulips greeting this will do dot delete WD character length null to zero of the first name okay let's see what we get with a debug I just want to make sure I didn't flat fat-finger anything and that looks good okay so that will delete the characters before the next iteration through the record set but after we've saved the document so let's see what that looks like let's go ahead and run this export again and we'll take a look here we have one my letter okay Alfred Newman that looks good second one Azure a hi alright hello is or that looks good let's take a look at the third one and oh we have blanks hmm blanks how did we get blanks well let's go ahead and delete these three letters again and what happened was is that we saved once we once we changed the data even after we saved it the document was still actually open so when we went to open the the third letter it just opened up the instance that was already open and we ended up just saving the document with nothing in it okay so even though we saved the document as something else so now we're on document number three for example once we're at document number three we then deleted the data within document 3 and then we exited out so we actually need to fix this by making sure that we don't save the data from the last document and what I'm going to do here is I'm actually going to tell the document to to close but when it does you can see that the closed method says do you want to save the changes we're going to say false ok so if you left that by default its true and if you left that as true it would save your last iteration through with blanks because again we just blanked out the values there on the third time through and even though we saved it it still went to that saved document that document number three and changed it all to blanks and then once we closed out of word it saved it that way so that's the default behavior now if we do close but we say no no don't save the data on the last time through then that will work and then we'll also go ahead and close the application by doing W app quit and just for good measure I'm going to go ahead and set W doc equal to nothing and set W app equal to nothing and why not we'll do set RS equal to nothing okay just good garbage Cleanup we'll save that debug it compile all right let's take one last rip at this and make sure that everything works the way we need it to so export names to word and enter and let's take a look at our documents so I'm just going to check out this last one here and that should tell us everything so yep there we go first name is Kaiser middle name is blank last name is so say hello Kaiser I'm writing it because I have nothing better to do so there we go we have successfully inserted data into all three of these letters we saved them individually as their own letters and obviously we could instead of saving them we could certainly print them out if you prefer whatever you want to do with these documents obviously that's up to you at that particular point but there you go that is how you export data from an Access database or query again you could just change this record set to some sort of query that comes from a sequel server or some other data source just like we have here coming from the table names I hope you guys enjoyed this video I hope that it you guys learn something from it and if you did please like this video and don't forget to favorite it and subscribe to my channel so you can get notified of more videos

Channel: Programming Made EZ

Views: 71,942

Rating: 4.883985 out of 5

Keywords: Microsoft, Access, 2013, Programming, VBA, Visual Basic, Applications, tutorial, lesson, guide, database, SQL, advanced, software, microsoft access, table, query, form, code, coding, development, visual basic for applications, computer programming, programming language, data, export, word, mailings, addresses, customer, information, insert

Id: JilB511V3AU

Length: 21min 1sec (1261 seconds)

Published: Wed Jun 08 2016