How do I import Access values into Word?

F

farmerjohn1324

Member
I am trying to print 200 letters, each to a different name and address... I have an Access (and Excel, if that counts) database of 200 different names and addresses that I want to use as the input....

The Word document is a template like...

To [Access value 1],

[Access value 2]

Sincerely,

(my name)

How can I print 200 different letters using one Word document using the Access or Excel databases that I have?
 
Agent Smith

Agent Smith

Well-Known Member
I couldn't find any info. on doing this other than E-mail addresses, labels and envelops. It sounds like you might need a macro. Either in Word or use Macromaker. There's also Autohotkey which is very powerful.
 
Cromewell

Cromewell

Administrator
Staff member
You could do something like this (I'm not 100% sure this exact code works, it should be reasonably close though):
Code: 
PublicFunction ImportData()
   Dim db As DAO.Database
   Dim rs As DAO.Recordset
   Dim strDB AsString
   Dim strSQL AsString

   ' you need to update this query accordingly for your access table
   strSQL ="Select name, address, whateverelse from my_table"
   strDB ="C:\Path\To\Your\Database.accdb"

   Set db = OpenDatabase(strDB)
   Set rs = db.OpenRecordset(strSQL)

   If rs.RecordCount > 0 Then
      Dim counter = 0
      Do Until rs.EOF
         Dim strName = rs!name
         Dim strAddress = rs!address

         Dim word As Word.Application

         word = CreateObject("Word.Application")
         word.DisplayAlerts =False
         Dim doc As Word.Document
         Set doc = wApp.Documents.Open Filename:="C:\Path\To\Your\Template.dot",ReadOnly:=True
         With wDoc
            .Application.Selection.Find.Text ="<<name>>"
            .Application.Selection.Find.Execute
            .Application.Selection = strName
            .Application.Selection.EndOf

            .Application.Selection.Find.Text ="<<address>>"
            .Application.Selection.Find.Execute
            .Application.Selection = strAddress

            .ActiveDocument.PrintOut
         EndWith

         rs.MoveNext
      Loop
   EndIf
   rs.Close
   db.Close

   Set rst =Nothing
   Set db =Nothing
EndFunction
 
strollin

strollin

Well-Known Member
MS Word has support for Mail Merge built-in with the ability to read from an Access DB. Which version of Office are you using? Try looking n Help for Mail Merge.
 
SpriteMidr

SpriteMidr

Active Member
Cromewell said:
You could do something like this (I'm not 100% sure this exact code works, it should be reasonably close though):
Code: 
PublicFunction ImportData()
   Dim db As DAO.Database
   Dim rs As DAO.Recordset
   Dim strDB AsString
   Dim strSQL AsString

   ' you need to update this query accordingly for your access table
   strSQL ="Select name, address, whateverelse from my_table"
   strDB ="C:\Path\To\Your\Database.accdb"

   Set db = OpenDatabase(strDB)
   Set rs = db.OpenRecordset(strSQL)

   If rs.RecordCount > 0 Then
      Dim counter = 0
      Do Until rs.EOF
         Dim strName = rs!name
         Dim strAddress = rs!address

         Dim word As Word.Application

         word = CreateObject("Word.Application")
         word.DisplayAlerts =False
         Dim doc As Word.Document
         Set doc = wApp.Documents.Open Filename:="C:\Path\To\Your\Template.dot",ReadOnly:=True
         With wDoc
            .Application.Selection.Find.Text ="<<name>>"
            .Application.Selection.Find.Execute
            .Application.Selection = strName
            .Application.Selection.EndOf

            .Application.Selection.Find.Text ="<<address>>"
            .Application.Selection.Find.Execute
            .Application.Selection = strAddress

            .ActiveDocument.PrintOut
         EndWith

         rs.MoveNext
      Loop
   EndIf
   rs.Close
   db.Close

   Set rst =Nothing
   Set db =Nothing
EndFunction
Click to expand...

If you are going that far you might as well use C#/VB.NET and use OleDb to read from the database and then interop with the Microsoft Word COM library.

Actually. Powershell uses the dotnet framework, so you may well be able to write an elegant solution in powershell and then not require any additional software.
 
Last edited:
SpriteMidr

SpriteMidr

Active Member
Cromewell said:
But I can write VBA directly in the application :p
Click to expand...

To open up and play with Word from powershell, you only need this... (not tried it yet, gotta install office first).
Code: 
$word = New-Object -ComObject "Word.application"  
$word.visible = $true  
$doc = $word.Documents.Add()  
$doc.Activate()  
  
$word.Selection.Font.Name = "Cambria"  
$word.Selection.Font.Size = "20"  
$word.Selection.TypeText("PowerShell")  
$word.Selection.TypeParagraph()  
  
$word.Selection.Font.Name = "Calibri"  
$word.Selection.Font.Size = "12"  
$word.Selection.TypeText("The best scripting language in the world!")  
$word.Selection.TypeParagraph()  
  
$file = "c:\scripts\office\test1.doc"  
$doc.SaveAs([REF]$file)  
  
$Word.Quit()

Then it is just a case of using sequel to gather the data from the server and then plonk variables into the above in whatever way.
 
F

farmerjohn1324

Member
Cromewell said:
You could do something like this (I'm not 100% sure this exact code works, it should be reasonably close though):
Code: 
PublicFunction ImportData()
   Dim db As DAO.Database
   Dim rs As DAO.Recordset
   Dim strDB AsString
   Dim strSQL AsString

   ' you need to update this query accordingly for your access table
   strSQL ="Select name, address, whateverelse from my_table"
   strDB ="C:\Path\To\Your\Database.accdb"

   Set db = OpenDatabase(strDB)
   Set rs = db.OpenRecordset(strSQL)

   If rs.RecordCount > 0 Then
      Dim counter = 0
      Do Until rs.EOF
         Dim strName = rs!name
         Dim strAddress = rs!address

         Dim word As Word.Application

         word = CreateObject("Word.Application")
         word.DisplayAlerts =False
         Dim doc As Word.Document
         Set doc = wApp.Documents.Open Filename:="C:\Path\To\Your\Template.dot",ReadOnly:=True
         With wDoc
            .Application.Selection.Find.Text ="<<name>>"
            .Application.Selection.Find.Execute
            .Application.Selection = strName
            .Application.Selection.EndOf

            .Application.Selection.Find.Text ="<<address>>"
            .Application.Selection.Find.Execute
            .Application.Selection = strAddress

            .ActiveDocument.PrintOut
         EndWith

         rs.MoveNext
      Loop
   EndIf
   rs.Close
   db.Close

   Set rst =Nothing
   Set db =Nothing
EndFunction
Click to expand...

What program do I type this code in to?
 
F

farmerjohn1324

Member
Agent Smith said:
I couldn't find any info. on doing this other than E-mail addresses, labels and envelops. It sounds like you might need a macro. Either in Word or use Macromaker. There's also Autohotkey which is very powerful.
Click to expand...

Macro Express is my favorite program in the world, but I'm not sure it would help me in this case.

I need something that makes 200 unique documents from one template document and prints them all at once.
 
Cromewell

Cromewell

Administrator
Staff member
farmerjohn1324 said:
What program do I type this code in to?
Click to expand...
You should be able to run it from a text file (kind of).

You paste it in, save it as something.vba and run it. As is it won't do anything though. If you remove "PublicFunction ImportData()" line and the "end function" line and update the paths/template strings it should. I didn't test it out, but the gist of it is correct. I wouldn't try to run the looped version without making sure it works first though. If it does partially work you might end up with a lot of junk printed
 
strollin

strollin

Well-Known Member
Given Farmerjohn1324's skill level, why are people telling him to write/run a script when Word and Access have built-in functions/interoperability to accomplish what he wants? That's like suggesting he use a crowbar to unlock his car door instead of simply pressing the button on the remote. Word even has a Wizard that will take him thru it step-by-step.
 
F

farmerjohn1324

Member
strollin said:
Given Farmerjohn1324's skill level, why are people telling him to write/run a script when Word and Access have built-in functions/interoperability to accomplish what he wants? That's like suggesting he use a crowbar to unlock his car door instead of simply pressing the button on the remote. Word even has a Wizard that will take him thru it step-by-step.
Click to expand...

I agree. I could probably figure it out given enough time... but at this point, I don't even know what a .vba file is and I don't why the program Notepad is involved at all.

So when I open up word, click F1... what should I type into the "Help" search?

And same for Access?

Although, "writing and running a script" does sound like fun... and is probably something I should learn at some point...

_________________________________________________________________________

After a quick Google search.... .vba is a Visual Basic file... this sounds like something I could do...
 
Cromewell

Cromewell

Administrator
Staff member
strollin said:
Given Farmerjohn1324's skill level, why are people telling him to write/run a script when Word and Access have built-in functions/interoperability to accomplish what he wants?
Click to expand...
Simple, really. When I did a quick search the first many answers all said vba was required. Visual Basic isn't nearly as bad ot learn for this as something like C# or <insert .net language here>. And to be perfectly honest, I have never heard of mail merge, it sounded like a 3rd party tool :p
 
strollin

strollin

Well-Known Member
farmerjohn1324 said:
I agree. I could probably figure it out given enough time... but at this point, I don't even know what a .vba file is and I don't why the program Notepad is involved at all.

So when I open up word, click F1... what should I type into the "Help" search?

And same for Access?

Although, "writing and running a script" does sound like fun... and is probably something I should learn at some point...

_________________________________________________________________________

After a quick Google search.... .vba is a Visual Basic file... this sounds like something I could do...
Click to expand...
First things first, what version of Office are you using?
 
F

farmerjohn1324

Member
Cromewell said:
You should be able to run it from a text file (kind of).

You paste it in, save it as something.vba and run it. As is it won't do anything though. If you remove "PublicFunction ImportData()" line and the "end function" line and update the paths/template strings it should. I didn't test it out, but the gist of it is correct. I wouldn't try to run the looped version without making sure it works first though. If it does partially work you might end up with a lot of junk printed
Click to expand...

I try to run it in Command Prompt..... it tells me that there is no script engine for file extension .vba.

I saved the file in Notepad.
 
strollin

strollin

Well-Known Member
farmerjohn1324 said:
But this isn't an email... it's something I'm trying to print. Will this still help me?

I think I need to "run some code," like they were talking before.
Click to expand...

Yes it will help. Mail-merge has been a part of Word for as long as I can remember, it probably was one of the features in Word's 1st release. Here's a different tutorial that doesn't mention email. http://www.laptopmag.com/articles/mail-merge-microsoft-office

The concept is the same either way. Not necessary to write any code.

Here's a video tutorial that might help: https://support.office.com/en-US/article/Mail-merge-507b5468-f771-485d-9ef0-27857168a266

Have you ever heard of Google? In the days that have passed since you first asked how to do this, you could have easily asked the question in Google, gotten the answer, created the letters and mailed them off.
 
Last edited:
You must log in or register to reply here.
Top