Create a folder and insert merge docs (A2k)

Topic

New Reply

To create a folder using VB, here’s what I’m using:

 Sub CreateFolder(strID As String) 
 Dim strPath As String 


  strPath = GetMDBPath 
  strPath = strPath & strID 


  If Len(Dir(strPath, vbDirectory)) > 0 Then 
     MsgBox ("folder " & strPath & " already exists.") 
  Else 
     MkDir strPath 
  End If 


End Sub 

Reply | Quote

Replies

Good day all,

I recently changed jobs, and am now in charge of writing a bunch of “basic” letters, so I figured I’d automate their creation. Works like a beaut’, but the boss wants every document saved as a separate word doc. They’re grouped in small, well, groups, and I’d like to export each group to a folder. I use a combo box with a select distinct filter to give me the group lists in a listbox, and would like to use the value selected as the folder name, and the person’s last name as the file name for the word doc. I’m also not sure how to loop the code that initiates the mail merge, since right now it uses the current record instead of basing off a query or something. One other wrench; within each group, there could be up to 3 different types of documents required, depending on the person’s status (you can see my use of a select case statement).

I’ll simplify my request; I want to loop through a query, applying the attached code (that works on a form) to the query, and save each record as an individual word document.

Reply | Quote

The code you attached is not for a mail merge in the technical sense (you don’t use the MailMerge property of the Word document).

You can open a DAO or ADO recordset and loop through its records. You would then refer to the fields of the records instead of to controls on the form. For example:

Dim cnn As ADODB.Connection
Dim rst As New ADODB.Recordset
Dim strSQL As String

‘ Just an example
strSQL = “SELECT * FROM tblSomething WHERE datCommitDate > Date()-7”
‘ Refer to current database
Set cnn = CurrentProject.Connection
‘ Open recordset
rst.Open strSQL, cnn, adOpenKeyset, adLockOptimistic, adCmdText
‘ Loop through records
Do While Not rst.EOF
Select Case rst!Orders_type
Case “CONUS”
…
…
End Select
‘ Save the document here
…
Loop
‘ Close recordset and release object variables
rst.Close
Set rst = Nothing
Set cnn = Nothing

Reply | Quote

Hans,
I understand everything you’ve posted, and have applied it to my needs (thank you very much)….one thing I’m stuck on now, how do I specify WHERE to save the file? I have inserted:

.ActiveDocument.SaveAs strFileName
.ActiveDocument.Close
objWord.Quit
Set objWord = Nothing

between case statements to actually save the file, but I don’t know how to tell access to save the file in the folder I just created…

Reply | Quote

In your CreateFolder procedure, you use

strPath = GetMDBPath
strPath = strPath & strID

I have no idea where strID comes from, but you could use the above path when saving the document:

.ActiveDocument.SaveAs strPath & “” & strFilename

Reply | Quote

Okay, the tag after .activedocument.saveas confused me since I couldn’t specify a location, but what you’ve suggested makes sense.

I specify strID in the OnClick event of a button, since I’m referring to a combo box on a form to specify the folder name. Thanks a lot!

Reply | Quote

Well, I thought I had it finished, I clicked the button, and here’s what I get:

rst.Open strSQL, cnn, adOpenKeyset, adLockOptimistic, adCmdText

“No value given for one or more required parameters.”

Reply | Quote

Have you assigned a value to strSQL?

Reply | Quote

yep…

strSQL = “SELECT StrConv([lname] & “”, “” & [fname] & “” “” & [mi],3) AS [Full Name], qryPersonnel.SSN, ” & _
“qryPersonnel.Pay_grade, qryPersonnel.Rank, qryPersonnel.LName, qryPersonnel.FName, qryPersonnel.MI, ” & _
“qryPersonnel.Officer_general, qryPersonnel.MOS, qryPersonnel.Class_number, qryPersonnel.Reservist_phone, ” & _
“qryPersonnel.Course_Name, qryPersonnel.Graduation_date, qryPersonnel.Reported_in_time, qryPersonnel.Reported_in_date, ” & _
“qryPersonnel.WEBO_date, qryPersonnel.Orders_Report_date, qryPersonnel.Orders_type, ” & _
“qryPersonnel.Reservist_Endo_Number, qryPersonnel.Reservist_RUC, qryPersonnel.Reservist_Command, ” & _
“qryPersonnel.Reservist_city_state_zip, qryPersonnel.FMCC, qryPersonnel.Days_delay, qryPersonnel.Days_proceed, ” & _
“qryPersonnel.Days_travel, qryPersonnel.Lv_balance, qryPersonnel.Leave_address_1, qryPersonnel.Leave_City_State_Zip, ” & _
“qryPersonnel.Leave_Phone, qryPersonnel.NOK, qryPersonnel.NOK_relationship, qryPersonnel.MAC_flight_number, ” & _
“qryPersonnel.MAC_report_time, qryPersonnel.MAC_report_date, qryPersonnel.CGAddress1, qryPersonnel.CGAddress2 ” & _
“FROM qryPersonnel WHERE (((qryPersonnel.Class_number) = IIf(IsNull([Forms]![frmStudents]![Text48]), ” & _
“([qryPersonnel].[Class_number]), [Forms]![frmStudents]![Text48]))) ORDER BY StrConv([lname] & “”, “” & [fname] & “” “” & [mi],3);”

Reply | Quote

You can’t refer to the form in the SQL string – ADO doesn’t know about it. Try this:

strSQL = “SELECT … FROM qryPersonnel”
If Not IsNull(Me.Text48) Then
strSQL = strSQL & ” WHERE qryPersonnel.Class_number = ” & Me.Text48
End If
strSQL = strSQL & ” ORDER BY …”

The above assumes that Class_number is numeric; if it is text, use

… & Chr(34) & Me.Text48 & Chr(34)

Reply | Quote

Okay, so now I don’t get any errors, but the code just keeps repeating, and only on the first record. Also, no file is actually created. The folder is made, but nothing else. Code attached.

Reply | Quote

My bad! I forgot the essential line

rst.MoveNext

immediately above Loop. That’s what you get for writing air code. Sorry about that.

BTW it would be more efficient to start Word before the loop, and to quit it after the loop, instead of starting and quitting it for each document.

Reply | Quote

Hans,
That works great! Thank you! Now I’ve noticed that it’s only creating the documents for the records with the first rst!Orders_type. (For example, if the first record’s order type is CONUS, it only creates orders for records whose order type is CONUS). Any idea why this may be happening?

Reply | Quote

The code should read the type each time through the loop. Try setting a breakpoint at the line

Select Case rst!Orders_type

and single-stepping through the code to see what goes wrong.

Reply | Quote