Results 1 to 14 of 14
  1. #1
    5 Star Lounger
    Join Date
    Nov 2004
    Location
    Wilmington, North Carolina, USA
    Posts
    1,196
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Create a folder and insert merge docs (A2k)

    To create a folder using VB, here's what I'm using:
    <pre> 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
    </pre>

    ____________________________
    Jeremy
    "If you spend more on coffee than on IT security, then you will be hacked. What&#39;s more, you deserve to be hacked." -Richard Clarke

  2. #2
    5 Star Lounger
    Join Date
    Nov 2004
    Location
    Wilmington, North Carolina, USA
    Posts
    1,196
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Create a folder and insert merge docs (A2k)

    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.
    ____________________________
    Jeremy
    "If you spend more on coffee than on IT security, then you will be hacked. What&#39;s more, you deserve to be hacked." -Richard Clarke

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Create a folder and insert merge docs (A2k)

    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

  4. #4
    5 Star Lounger
    Join Date
    Nov 2004
    Location
    Wilmington, North Carolina, USA
    Posts
    1,196
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Create a folder and insert merge docs (A2k)

    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...
    ____________________________
    Jeremy
    "If you spend more on coffee than on IT security, then you will be hacked. What&#39;s more, you deserve to be hacked." -Richard Clarke

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Create a folder and insert merge docs (A2k)

    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

  6. #6
    5 Star Lounger
    Join Date
    Nov 2004
    Location
    Wilmington, North Carolina, USA
    Posts
    1,196
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Create a folder and insert merge docs (A2k)

    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!
    ____________________________
    Jeremy
    "If you spend more on coffee than on IT security, then you will be hacked. What&#39;s more, you deserve to be hacked." -Richard Clarke

  7. #7
    5 Star Lounger
    Join Date
    Nov 2004
    Location
    Wilmington, North Carolina, USA
    Posts
    1,196
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Create a folder and insert merge docs (A2k)

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

    <span style="background-color: #FFFF00; color: #000000; font-weight: bold">rst.Open strSQL, cnn, adOpenKeyset, adLockOptimistic, adCmdText</span hi>

    "No value given for one or more required parameters."
    ____________________________
    Jeremy
    "If you spend more on coffee than on IT security, then you will be hacked. What&#39;s more, you deserve to be hacked." -Richard Clarke

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Create a folder and insert merge docs (A2k)

    Have you assigned a value to strSQL?

  9. #9
    5 Star Lounger
    Join Date
    Nov 2004
    Location
    Wilmington, North Carolina, USA
    Posts
    1,196
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Create a folder and insert merge docs (A2k)

    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);"
    ____________________________
    Jeremy
    "If you spend more on coffee than on IT security, then you will be hacked. What&#39;s more, you deserve to be hacked." -Richard Clarke

  10. #10
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Create a folder and insert merge docs (A2k)

    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)

  11. #11
    5 Star Lounger
    Join Date
    Nov 2004
    Location
    Wilmington, North Carolina, USA
    Posts
    1,196
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Create a folder and insert merge docs (A2k)

    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.
    ____________________________
    Jeremy
    "If you spend more on coffee than on IT security, then you will be hacked. What&#39;s more, you deserve to be hacked." -Richard Clarke

  12. #12
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Create a folder and insert merge docs (A2k)

    <img src=/S/blush.gif border=0 alt=blush width=15 height=15> 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.

  13. #13
    5 Star Lounger
    Join Date
    Nov 2004
    Location
    Wilmington, North Carolina, USA
    Posts
    1,196
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Create a folder and insert merge docs (A2k)

    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?
    ____________________________
    Jeremy
    "If you spend more on coffee than on IT security, then you will be hacked. What&#39;s more, you deserve to be hacked." -Richard Clarke

  14. #14
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Create a folder and insert merge docs (A2k)

    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •