Dynamically save automagically generated word document!
May 15, 2007 6:20 AM Subscribe
VBA assistance needed. I've got the automerge and print down, now to save it as a unique file...
I've got a database used to track donors. Upon entry of the appropriate data, I press a "Print Thank-You" button, and it auto merges the fields from THAT form into a form letter and automatically prints it. What I'd like to do is have it automatically SAVE the letter too, but with a new filename so that it doesn't overwrite my form letter.
Example:
Donor name is John Smith, date is 5/15/07. I'd like to save the file name as Smith 5-07 or something similar.
Here's the code I have right now:
######start code######
Option Compare Database
Private Sub MergeButton_Click()
On Error GoTo MergeButton_Err
Dim objWord As Word.Application
Set objWord = CreateObject("Word.Application")
With objWord
'Make the application visible.
.Visible = True
'Open the document.
.Documents.Open ("C:\Users\ReStore\Documents\thankyouletter.doc")
'Move to each bookmark and insert text from the form.
.ActiveDocument.Bookmarks("First").Select
.Selection.Text = (CStr(Forms!MainData!DonorFirstName))
.ActiveDocument.Bookmarks("Last").Select
.Selection.Text = (CStr(Forms!MainData!DonorLastName))
.ActiveDocument.Bookmarks("Address").Select
.Selection.Text = (CStr(Forms!MainData!DonorAddress))
.ActiveDocument.Bookmarks("City").Select
.Selection.Text = (CStr(Forms!MainData!DonorCity))
.ActiveDocument.Bookmarks("State").Select
.Selection.Text = (CStr(Forms!MainData!DonorState))
.ActiveDocument.Bookmarks("Zip").Select
.Selection.Text = (CStr(Forms!MainData!DonorZip))
.ActiveDocument.Bookmarks("First2").Select
.Selection.Text = (CStr(Forms!MainData!DonorFirstName))
.ActiveDocument.Bookmarks("Date").Select
.Selection.Text = (CStr(Forms!MainData!Date))
.ActiveDocument.Bookmarks("items").Select
.Selection.Text = (CStr(Forms!MainData!ItemtoDonate))
End With
'Print the document in the foreground so Microsoft Word will not close
'until the document finishes printing.
objWord.ActiveDocument.PrintOut Background:=True
'Close the document without saving changes.
'objWord.ActiveDocument.Close SaveChanges:=wdDoNotSaveChanges
'Quit Microsoft Word and release the object variable.
'objWord.Quit
'Set objWord = Nothing
Exit Sub
MergeButton_Err:
'If a field on the form is empty, remove the bookmark text, and
'continue.
'If Err.Number = 94 Then
' objWord.Selection.Text = ""
' Resume Next
'If the Photo field is empty.
'ElseIf Err.Number = 2046 Then
' MsgBox "Please add a photo to this record and try again."
'Else
' MsgBox Err.Number & vbCr & Err.Description
'End If
Exit Sub
End Sub
#####end code#####
Suggestions? Thanks!
I've got a database used to track donors. Upon entry of the appropriate data, I press a "Print Thank-You" button, and it auto merges the fields from THAT form into a form letter and automatically prints it. What I'd like to do is have it automatically SAVE the letter too, but with a new filename so that it doesn't overwrite my form letter.
Example:
Donor name is John Smith, date is 5/15/07. I'd like to save the file name as Smith 5-07 or something similar.
Here's the code I have right now:
######start code######
Option Compare Database
Private Sub MergeButton_Click()
On Error GoTo MergeButton_Err
Dim objWord As Word.Application
Set objWord = CreateObject("Word.Application")
With objWord
'Make the application visible.
.Visible = True
'Open the document.
.Documents.Open ("C:\Users\ReStore\Documents\thankyouletter.doc")
'Move to each bookmark and insert text from the form.
.ActiveDocument.Bookmarks("First").Select
.Selection.Text = (CStr(Forms!MainData!DonorFirstName))
.ActiveDocument.Bookmarks("Last").Select
.Selection.Text = (CStr(Forms!MainData!DonorLastName))
.ActiveDocument.Bookmarks("Address").Select
.Selection.Text = (CStr(Forms!MainData!DonorAddress))
.ActiveDocument.Bookmarks("City").Select
.Selection.Text = (CStr(Forms!MainData!DonorCity))
.ActiveDocument.Bookmarks("State").Select
.Selection.Text = (CStr(Forms!MainData!DonorState))
.ActiveDocument.Bookmarks("Zip").Select
.Selection.Text = (CStr(Forms!MainData!DonorZip))
.ActiveDocument.Bookmarks("First2").Select
.Selection.Text = (CStr(Forms!MainData!DonorFirstName))
.ActiveDocument.Bookmarks("Date").Select
.Selection.Text = (CStr(Forms!MainData!Date))
.ActiveDocument.Bookmarks("items").Select
.Selection.Text = (CStr(Forms!MainData!ItemtoDonate))
End With
'Print the document in the foreground so Microsoft Word will not close
'until the document finishes printing.
objWord.ActiveDocument.PrintOut Background:=True
'Close the document without saving changes.
'objWord.ActiveDocument.Close SaveChanges:=wdDoNotSaveChanges
'Quit Microsoft Word and release the object variable.
'objWord.Quit
'Set objWord = Nothing
Exit Sub
MergeButton_Err:
'If a field on the form is empty, remove the bookmark text, and
'continue.
'If Err.Number = 94 Then
' objWord.Selection.Text = ""
' Resume Next
'If the Photo field is empty.
'ElseIf Err.Number = 2046 Then
' MsgBox "Please add a photo to this record and try again."
'Else
' MsgBox Err.Number & vbCr & Err.Description
'End If
Exit Sub
End Sub
#####end code#####
Suggestions? Thanks!
Response by poster: Yes, it needs to be new each time, this would be our backup of their tax deduction receipt.
Walk me through this, it's blowing my mind a little...
Dim tells it to print function FName as a string, then...
FName = "Form letter " would be the location of the file in its unedited form, right?
The next takes the first name, puts a space, last name, and saves as .doc
Then the last line tells it to save w/ the filename generated by function Fname.
Is that close to right? Thanks immensely btw---I just like to know exactly what i'm telling it to do.
Oh, and trying to get the DATE in there was my way to prevent a duplicate file name, just in case said donor comes back w/i the calendar year. Any insight on that?
posted by TomMelee at 8:06 AM on May 15, 2007
Walk me through this, it's blowing my mind a little...
Dim tells it to print function FName as a string, then...
FName = "Form letter " would be the location of the file in its unedited form, right?
The next takes the first name, puts a space, last name, and saves as .doc
Then the last line tells it to save w/ the filename generated by function Fname.
Is that close to right? Thanks immensely btw---I just like to know exactly what i'm telling it to do.
Oh, and trying to get the DATE in there was my way to prevent a duplicate file name, just in case said donor comes back w/i the calendar year. Any insight on that?
posted by TomMelee at 8:06 AM on May 15, 2007
Best answer: If you want to add the date to the string, just add Forms!MainData!Date to the filename that's being built up.
An aside: Have you thought about just having the letter be a report in Access?
We just record the date the letter was generated in a table, which lets us reprint the letter at another time with the "original" date.
A row in a database isn't any more or less of a "backup" than an individual Word file, and keeping the info in Access means you can later do reports like "figure out the average response time between donation and acknowledgement letter."
posted by bcwinters at 8:57 AM on May 15, 2007
An aside: Have you thought about just having the letter be a report in Access?
We just record the date the letter was generated in a table, which lets us reprint the letter at another time with the "original" date.
A row in a database isn't any more or less of a "backup" than an individual Word file, and keeping the info in Access means you can later do reports like "figure out the average response time between donation and acknowledgement letter."
posted by bcwinters at 8:57 AM on May 15, 2007
There's actually only three lines in the snippet I pasted; your browser window is probably wrapping the second line.
You're close to being right, but your terminology is a little off. In the first line, FName is declared as a variable of type string. A variable is a temporary storage location in the computer's memory. The string data type is used to store text. We're using the FName variable to store the actual file name as text data.
The second line builds the actual file name from component parts and assigns it to the FName variable. In VBA, the & operator joins strings together, so when I say
The last line tells Word to save the active document using the filename that was generated, exactly as if you done "Save As..." from within Word and typed in the filename yourself.
posted by zixyer at 9:05 AM on May 15, 2007
You're close to being right, but your terminology is a little off. In the first line, FName is declared as a variable of type string. A variable is a temporary storage location in the computer's memory. The string data type is used to store text. We're using the FName variable to store the actual file name as text data.
The second line builds the actual file name from component parts and assigns it to the FName variable. In VBA, the & operator joins strings together, so when I say
"Form letter " & Forms!MainData!DonorFirstName, the result is the string "Form data John", assuming that the Forms!MainData!DonorFirstName variable contains the name "John". I use the & operator multiple times to build up the string in this way.The last line tells Word to save the active document using the filename that was generated, exactly as if you done "Save As..." from within Word and typed in the filename yourself.
posted by zixyer at 9:05 AM on May 15, 2007
Sorry! I made a mistake, the result of the & operation example I posted above would have been "Form letter John", not "Form data John".
posted by zixyer at 9:10 AM on May 15, 2007
posted by zixyer at 9:10 AM on May 15, 2007
Response by poster: I realized that when I was eating lunch zixyer---the concatenated string that is. Horray for not being 100% retarded.
And bcwinters, yes, I thought about it like that, but for this particular setup, this works best. Thanks for the input! I'll try this today after our volunteers leave. Thanks!!
~Clark
btw: wtb a good VBA online tutorial...suggestions?
posted by TomMelee at 10:06 AM on May 15, 2007
And bcwinters, yes, I thought about it like that, but for this particular setup, this works best. Thanks for the input! I'll try this today after our volunteers leave. Thanks!!
~Clark
btw: wtb a good VBA online tutorial...suggestions?
posted by TomMelee at 10:06 AM on May 15, 2007
Response by poster: All right, somethings rotten in the state of denmark. Little help?
Dim FName As String
FName = Forms!MainData!DonorFirstName & " " & Forms!MainData!DonorLastName & " " & Forms!MainData!Date & ".doc"
objWord.ActiveDocument.SaveAs FileName:=FName
Is exactly what I have in there. It's not working---but it's also not erroring.
Is it because I've commented out the close-window stuff?
posted by TomMelee at 10:48 AM on May 15, 2007
Dim FName As String
FName = Forms!MainData!DonorFirstName & " " & Forms!MainData!DonorLastName & " " & Forms!MainData!Date & ".doc"
objWord.ActiveDocument.SaveAs FileName:=FName
Is exactly what I have in there. It's not working---but it's also not erroring.
Is it because I've commented out the close-window stuff?
posted by TomMelee at 10:48 AM on May 15, 2007
I don't know what the problem could be. How can you tell that it failed? It might not be saving the file where you're expecting it to. The title bar of the Word window should show the new filename after the script executes, is it still "thankyouletter.doc"?
posted by zixyer at 12:42 PM on May 15, 2007
posted by zixyer at 12:42 PM on May 15, 2007
Response by poster: Yes, it's still "thankyouletter.doc"
Reposting entirety of the module, sorry for length, there's a buncha extra gunk that I commented out. Methinks I have this pasted in the wrong place (the save dialog)
Option Compare Database
Private Sub MergeButton_Click()
On Error GoTo MergeButton_Err
Dim objWord As Word.Application
'Start Microsoft Word 97.
Set objWord = CreateObject("Word.Application")
With objWord
'Make the application visible.
.Visible = True
'Open the document.
.Documents.Open ("C:\Users\ReStore\Documents\thankyouletter.doc")
'Move to each bookmark and insert text from the form.
.ActiveDocument.Bookmarks("First").Select
.Selection.Text = (CStr(Forms!MainData!DonorFirstName))
.ActiveDocument.Bookmarks("Last").Select
.Selection.Text = (CStr(Forms!MainData!DonorLastName))
.ActiveDocument.Bookmarks("Address").Select
.Selection.Text = (CStr(Forms!MainData!DonorAddress))
.ActiveDocument.Bookmarks("City").Select
.Selection.Text = (CStr(Forms!MainData!DonorCity))
.ActiveDocument.Bookmarks("State").Select
.Selection.Text = (CStr(Forms!MainData!DonorState))
.ActiveDocument.Bookmarks("Zip").Select
.Selection.Text = (CStr(Forms!MainData!DonorZip))
.ActiveDocument.Bookmarks("First2").Select
.Selection.Text = (CStr(Forms!MainData!DonorFirstName))
.ActiveDocument.Bookmarks("Date").Select
.Selection.Text = (CStr(Forms!MainData!Date))
.ActiveDocument.Bookmarks("items").Select
.Selection.Text = (CStr(Forms!MainData!ItemtoDonate))
End With
'Print the document in the foreground so Microsoft Word will not close
'until the document finishes printing.
objWord.ActiveDocument.PrintOut Background:=True
Exit Sub
Dim FName As String
FName = Forms!MainData!DonorFirstName & " " & Forms!MainData!DonorLastName & " " & Forms!MainData!Date & ".doc"
objWord.ActiveDocument.SaveAs FileName:=FName
MergeButton_Err:
'If a field on the form is empty, remove the bookmark text, and
'continue.
'If Err.Number = 94 Then
' objWord.Selection.Text = ""
' Resume Next
'If the Photo field is empty.
'ElseIf Err.Number = 2046 Then
' MsgBox "Please add a photo to this record and try again."
'Else
' MsgBox Err.Number & vbCr & Err.Description
'End If
Exit Sub
End Sub
posted by TomMelee at 12:48 PM on May 15, 2007
Reposting entirety of the module, sorry for length, there's a buncha extra gunk that I commented out. Methinks I have this pasted in the wrong place (the save dialog)
Option Compare Database
Private Sub MergeButton_Click()
On Error GoTo MergeButton_Err
Dim objWord As Word.Application
'Start Microsoft Word 97.
Set objWord = CreateObject("Word.Application")
With objWord
'Make the application visible.
.Visible = True
'Open the document.
.Documents.Open ("C:\Users\ReStore\Documents\thankyouletter.doc")
'Move to each bookmark and insert text from the form.
.ActiveDocument.Bookmarks("First").Select
.Selection.Text = (CStr(Forms!MainData!DonorFirstName))
.ActiveDocument.Bookmarks("Last").Select
.Selection.Text = (CStr(Forms!MainData!DonorLastName))
.ActiveDocument.Bookmarks("Address").Select
.Selection.Text = (CStr(Forms!MainData!DonorAddress))
.ActiveDocument.Bookmarks("City").Select
.Selection.Text = (CStr(Forms!MainData!DonorCity))
.ActiveDocument.Bookmarks("State").Select
.Selection.Text = (CStr(Forms!MainData!DonorState))
.ActiveDocument.Bookmarks("Zip").Select
.Selection.Text = (CStr(Forms!MainData!DonorZip))
.ActiveDocument.Bookmarks("First2").Select
.Selection.Text = (CStr(Forms!MainData!DonorFirstName))
.ActiveDocument.Bookmarks("Date").Select
.Selection.Text = (CStr(Forms!MainData!Date))
.ActiveDocument.Bookmarks("items").Select
.Selection.Text = (CStr(Forms!MainData!ItemtoDonate))
End With
'Print the document in the foreground so Microsoft Word will not close
'until the document finishes printing.
objWord.ActiveDocument.PrintOut Background:=True
Exit Sub
Dim FName As String
FName = Forms!MainData!DonorFirstName & " " & Forms!MainData!DonorLastName & " " & Forms!MainData!Date & ".doc"
objWord.ActiveDocument.SaveAs FileName:=FName
MergeButton_Err:
'If a field on the form is empty, remove the bookmark text, and
'continue.
'If Err.Number = 94 Then
' objWord.Selection.Text = ""
' Resume Next
'If the Photo field is empty.
'ElseIf Err.Number = 2046 Then
' MsgBox "Please add a photo to this record and try again."
'Else
' MsgBox Err.Number & vbCr & Err.Description
'End If
Exit Sub
End Sub
posted by TomMelee at 12:48 PM on May 15, 2007
Yeah, it shouldn't go after the Exit Sub. Exit Sub ... exits the subroutine, so it never gets to the code.
posted by zixyer at 12:56 PM on May 15, 2007
posted by zixyer at 12:56 PM on May 15, 2007
Response by poster: Still not working, taking it to email. Thanks for the help!
posted by TomMelee at 1:01 PM on May 15, 2007
posted by TomMelee at 1:01 PM on May 15, 2007
This thread is closed to new comments.
objWord.ActiveDocument.SaveAs FileName:="whatever.doc"before you close the document.
If you want to generate a new, unique filename for the generated form letter, you can pull data out of the form to generate a discriptive name like this:
Dim FName As String
FName = "Form letter " & Forms!MainData!DonorFirstName & " " & Forms!MainData!DonorLastName & ".doc"
objWord.ActiveDocument.SaveAs FileName:=FName
If you absolutely need to make sure the filename doesn't clash with an already-generated form letter, you'll have to read the files in the directory and perhaps append an incrementing number to the end of the filename in the case of duplicates. (Word's object model doesn't support generating a unique filename, at least as far as I know.)
posted by zixyer at 7:25 AM on May 15, 2007