I have several employees and each employee has a table in a access 2000 database with a primary key defined. What I am wanting is to be able to build a form in excel, that has two boxes, one for the Employee name, and the other for the ID. Then a press button, and once this button is pressed it will open the word doc that is in let's say EmployeeName field on the form, and run a mail merge with the ID that is in the IDRequested field on the form.
I have all of these steps set up manually, ie the access database is built, the word doc with mail merge set up etc. I just would like to try to automate it and make it as simple as enter the employee name, enter the ID requested an Bam, there is what you are looking for.
I have this code that will open word for me, I just don't know how to pass the two parameters....
[vba]
Dim wdApp As Word.Application, wdDoc As Word.Document
On Error Resume Next
Set wdApp = GetObject(, "Word.Application")
If Err.Number <> 0 Then 'Word isn't already running
Set wdApp = CreateObject("Word.Application")
End If
On Error GoTo 0
Set wdDoc = wdApp.Documents.Open("C:\temp\anyolddoc.doc")
wdApp.Visible = True
End Sub
[/vba]
Last edited by Aussiebear; 10-27-2011 at 03:16 PM.
Reason: adjusted the code tags for the correct usage
Doing it manually is the first step. In fact, you are almost done. Of course this could all be done in MSWord but doing it in Excel is what we are here for. After you complete your project, please post the code here if you don't mind. This helps others by having real world examples.
Are the ID's not in the same record as the Names? I can see a case where you might have two John Does but different IDs. Your SQL should limit records to ID most likely.
Record a macro in MSWord where you do the merge. Then adapt that code for Excel. To get the ID and/or Names, you can do that in listbox controls on the Sheet or a Userform with listbox controls by use of ADO methods.
In this thread, http://www.vbaexpress.com/forum/showthread.php?p=167033, I showed how to use ADO to fill a listbox control in a userform from an MDB file. Similar to that, I did it in ActiveX listbox controls. I attached a spreadsheet to show both listbox control methods. To test it, you will need the readily available NWind.mdb file as explained in the thread and replace the paths in the code routines.
I am not sure how to get a macro in word to record what I am doing. It is set up currently that when you open the worddoc, let's take Mark for example when I double click on Mark.doc it opens, and then it opens the access database and automatically asks me for the id I want to view in word. How could I record a macro that catches those steps?
I tried opening word and recording a macro to catch what goes on "behind the scenes" but the macro record looses it becuase once the ID is entered it opens a new instance of word, and that "New" instance is not the active.document
Because opening the word document only opens the template, I then still have to enter the ID that I wish to "merge". I was able to capture the code through a macro that actually "merges" the template with the ID Document and that is:
[vba]
With ActiveDocument.MailMerge
.Destination = wdSendToNewDocument
.MailAsAttachment = False
.MailAddressFieldName = ""
.MailSubject = ""
.SuppressBlankLines = True
With .DataSource
.FirstRecord = wdDefaultFirstRecord
.LastRecord = wdDefaultLastRecord
End With
.Execute Pause:=True
End With
[/vba]
Last edited by Aussiebear; 10-27-2011 at 03:18 PM.
Reason: adjusted the code tags for the correct usage
You need to get the string for the SQLStatement. Record a simple merge to get what you need. e.g. Open a new file, add one field, do the merge.
I used a txt file for this merge example as a standard routine in MSWord's VBA. The FRM file is the DOC file of course. Notice how I suppressed the initial merge in the Documents.Open. I initially did this project in WordPerfect for a consultant fee. They then asked for an MSWord solution and I gave it to them but they never paid for that. Oh well, so much for consulting.
Once you have the SQL string, that can easily be added to this routine.
[VBA]Sub MergeRun(frmFile As String, datFile As String, _
Optional bClose As Boolean = True, Optional bPrint As Boolean = True, _
Optional iNoCopies As Integer = 1)
If Dir(frmFile) = "" Or Dir(datFile) = "" Then Exit Sub
And the SQL string is just simply the SQL from the query that the mail merge calls, correct?
Here is a snippet of the SQL for my access query..And ID is the prompt that displays as soon as you open Richard.doc so that once you enter the ID it pulls in all of the data from the query.
[vba]
SELECT tbl_Richard.ID, tbl_Richard.FName, tbl_Richard.lName, tbl_Richard.phone...
FROM tbl_Richard
WHERE (((tbl_Richard.ID)=[ID]));
[/vba]
Last edited by Aussiebear; 10-27-2011 at 03:18 PM.
Reason: adjusted the code tags for the correct usage
Right, it should be fairly similar. What you will do is to rebuild the string that will replace the [ID] with the value from the selection list in Excel for that ID that matches the Name that you need.
I did that one project several years ago so I would have to make up a new example to see the exact syntax.
I am wanting the ID to be entered into a text box on form in Excel. So I would have to say something like ID = Textbox1.Value or something similar to that, correct?
Right. Try a hard coded string first but it may go something like:
[vba]s = "SELECT tbl_Richard.ID, tbl_Richard.FName, tbl_Richard.lName, tbl_Richard.phone FROM tbl_Richard WHERE (((tbl_Richard.ID)=" & "'" & Textbox1.value & "')))"[/vba] more or less. Note the single quotes around the value.
If I get time, I will see if I can make up a quick example using nwind.mdb in an MSWord file using an SQL string in my MergeRun routine. Note how I set the path to the datFile in MergeRun which in this case would be the MDB file.
Here is my simple recorded MSWord VBA mailmerge code. In particular, note this syntax:
[VBA]ActiveDocument.MailMerge.DataSource.QueryString = _
"SELECT * FROM `Employees` WHERE `EmployeeID` = 3" & ""[/VBA]
I put it all together for you in the XLSM file with an example DOCX. I used ADO to fill a combobox with the LastName, FirstName, EmployeeID from the Employees table in the Northwind database. I parsed the ID name from the string value in the combobox to build the SQL string. You will need to get nwind.mdb and change the paths accordingly. There are a few more routines in it than you really need. Look at the Sheet1 code which contains the code for the two command buttons.
I updated the MergeRunMDB routine to:
[VBA]'Add Tools > References... > Microsoft Word Word 14.0 Object Libraray
Sub MergeRunMDB(frmFile As String, datFile As String, _
sSQL As String, _
Optional bClose As Boolean = False, Optional bPrint As Boolean = False, _
Optional iNoCopies As Integer = 1)
Dim wd As Word.Application, doc As Word.Document
If Dir(frmFile) = "" Or Dir(datFile) = "" Then Exit Sub
On Error Resume Next
Set wd = GetObject(, "Word.Application")
If wd Is Nothing Then
Set wd = CreateObject("Word.Application")
End If
wd.Visible = False
On Error GoTo 0
wd.Application.DisplayAlerts = wdAlertsNone
'sSQL = "SELECT * FROM `Employees`"
'Merge to a new document
With .MailMerge
.Destination = wdSendToNewDocument
.SuppressBlankLines = True
With .DataSource
.FirstRecord = wdDefaultFirstRecord
.LastRecord = wdDefaultLastRecord
End With
.Execute Pause:=False
End With
If bPrint = True Then
wd.ActiveDocument.PrintOut Range:=wdPrintAllDocument, Item:= _
wdPrintDocumentContent, Copies:=iNoCopies, Pages:="", PageType:=wdPrintAllPages, _
ManualDuplexPrint:=False, Collate:=True, Background:=True, PrintToFile:= _
False, PrintZoomColumn:=0, PrintZoomRow:=0, PrintZoomPaperWidth:=0, _
PrintZoomPaperHeight:=0
End If
.Application.DisplayAlerts = wdAlertsAll
If Not bClose Then wd.Visible = True
.Close False
If bClose = True Then
wd.ActiveDocument.Close False
wd.Quit
Set doc = Nothing
Set wd = Nothing
End If
End With
End Sub
This is what I want my form to look like, and I know I need to add in the directory where myd atabase is stored, as well as my SQL string...My question tho, is it is possible to pass data that is entered on this form into the query, and essentially into the mail merge?
It should work ok after the conversion and replacement of MergeRunMDB and edits to the paths for the MDB and DOCX or DOC files. You will just need to reference the lower versions of the early bound objects. In VBE for the Excel file, go to menus Tools > References... and uncheck the Missing ones and replace with the next lower version.
One possible snag that I see is that the SQL statement that is run, is dependent upon whose template is chosen (see attachment above). The SQL varies slightly for each different template as they each have there own query. Could the code say something like TemplateOpen = TextBox4.Value.
Then have the code for the database to open be C:\Test\TemplateOpen\TemplateOpen.mdb
Then I could input the actual query Name from within the TemplateOpen database
The parameter inputs for MergeRunMDB() can be anything you want. That is why I do modular code. If you are going to use a query string already in the database, you will have to submit that SQL string to MergeRunMDB adapted to MSWord's syntax. It should be similar but it will be different.
You can use Access's existing SQL using Excel's QueryTables method to import that data to Excel. One could then use the Excel data as the data source but that involves another type of MergeRun routine.
Here is how to import the data to Excel using an existing Access stored SQL with some thread links for more info. Obviously, you can use the same nwind.mdb file. Just replace the path and name of the MDB file accordingly.
[vba]'http://www.vbaexpress.com/forum/showthread.php?t=24118
'http://www.vbaexpress.com/forum/showthread.php?t=24575
'http://www.vbaexpress.com/forum/showthread.php?t=23783
'http://www.vbaexpress.com/forum/showthread.php?t=26145
Sub Test()
Dim mdbPath As String, dbName As String, cmdText As String
Dim rngDestination As String
'mdbPath = "E:\ADO\NWind2003.mdb" 'change the path here to suit your needs
'mdbPath = "c:\myfiles\edrive\excel\ado\NWind2003.mdb"
mdbPath = "//matpc10/ExcelVBAExamples/ado/NWind2003.mdb"
dbName = "NWind2003_1" 'change the database name here to suit your needs
cmdText = "Aug94" 'change the stored SQL here to suit your needs
rngDestination = "A1" 'change the destination range here to suit your needs
'Insert other data to the right of A1 with a blank column separating the two
rngDestination = Cells(1, Columns.Count).End(xlToLeft).Offset(0, 2).Address
cmdText = "Sales by Category"
InsertTableWithStoredSQL mdbPath, dbName, cmdText, rngDestination
End Sub
Sub InsertTableWithStoredSQL(mdbPath As String, dbName As String, _
cmdText As String, rngDestination As String, _
Optional bFieldNames = True)
Regarding your Excel file, I recommend that you make the two top textboxes as Read Only or as Label controls.
If you want to just let the user enter what they think the userid or name might be, that is fine. Your SQL string can be constructed according to their userid first and if blank, then use the name. Obviously, your users must enter the correct information. This is why I used ADO to help the user pick the correct data to construct the SQL string. IF you let the user enter the values then you may want to add some code to my routine to exit if the recordcount is 0.
The stored SQL method may not be what you were talking about. I thought that you now want to use a stored SQL. If that is not the case, it is not all that complicated now that you have my latest MergeRunMDB routine.
Maybe my best bet isn't to try to go from Access to Excel to Word. I think I am going to look into going directly from Access to Word by using C# coding instead of my beginner knowledge of VBA.
Or I may end up with a dead end there, and find out that you have to do the same thing with C#
Reply With Quote
atabase Locking Mode=0;Jet OLEDB:Global Par" _