Export Access Data with VBA
In this example, we’re going to have a look at exporting some fields to Microsoft Word by using VBA Code. Brace yourself, this gets a little strange!
We’re going to go ahead and create our very own procedure in order to loop through records in a table and move them into Microsoft Word.
Why would you do this?
Great question – probably if you either have too much time on your hands, or feel like creating more work for yourself in order to avoid washing your Manager’s car!
Seriously, there are quite a few reasons. One of the reasons I’ve used this method is to export text from a database but change the formatting. For instance, place it into a Word document with different line breaks or tab characters in order to format it for a specific purpose. This is really only scratching the surface of why you would do such a thing, but it’s a handy trick to have in your arsenal.
At this point, I’m going to assume you’re comfortable enough working in VBA code to make a few changes comfortably. The first step in this process is to create a new module (save it as bas_WordOpen or some such name), and paste the following code in the module:
Public WordApp As Word.Application
Public doc As Word.Document
Public sel As Word.Selection
Public Sub WordEx()
Set WordApp = New Word.Application
WordApp.Documents.Add
Set doc = WordApp.ActiveDocument
Set sel = WordApp.Selection
WordApp.Visible = True
End Sub
Before you run this code, you’ll have to click on Tools, then References, and enable the Microsoft Word Object Library. For XP, you’ll be enabling Microsoft Word 10.0 Object Library. We can’t have Access and Word chatting in different languages!
As it stands right now, this procedure will work on it’s own without any problems. Of course, all it will do is open up a Word document!
The next step is to create the procedure that will run through records in a table or query, and spit them out into Word in whatever format we desire. Lucky for you, I’ve created the code and you just have to copy and paste it into a new module (or combine it with the existing one – that’s all up to you):
Public Sub WordXPort()
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim fld As Field
Set db = CurrentDb()
Set rs = db.OpenRecordset("qsel_Test")
Set fld = rs.Fields(0)
WordEx
Do Until rs.EOF
Loop
End Sub
Basically, this code sets up whatever recordset you want (in this case, I’m picking on “qsel_Test”), starts up word (that’s the “WordEx” procedure getting called – the first procedure we created), then loops through all of the records, putting the phrase, “ENTRY – “ in front of each line and giving us the value in the first field (that’s the “Fields(0)” reference you see).
The Word Document output would look something like this:
ENTRY – First Field Value
ENTRY – Second Field Value
ENTRY – Third Field Value
You can replace “ENTRY” with whatever you want – or add tabs, more line feed characters (The “vbCrLf” that you see), or any change you desire. This is extremely flexible, as most code solutions are, and can be changed, adjusted, enhanced and completely overhauled to get you your desired results!
©2010 Blue Moose Technology, LLC
Article Publishing and Re-Print Guidelines
- The article text, resource box, URLs copyright information and these guidelines must be left intact and unchanged.
- When re-published online, all links must be live hyperlinks.
- These articles may not be distributed in any manner that does not comply with federal communication guidelines. Be Good!
- These articles must not be password protected or limited to membership or paid-only viewing. Keep 'em free, folks!
You can easily link to this article!
If you'd like to link to this article from your own website, copy and paste the following HTML code onto your page:
On your page, the code will appear like this: