Solved: Need macro to create add'l copies and update text

[clhare]

Hi!

I have a Word template that I need to somehow get to create copies of the output file and put a different name and address in each.

I have set up a form in VBA to get all the addressees (up to 6 different names and addressee), but I don't know how to create copies of the file, update the name and address in each copy, and save them all.

Once I have all the word docs, I have to go back and create a print file of each of these 6 files (from print screen, select print to file, then save to filename with .prn extension).

Is it possible to create all these additional files based on the original file, get the names and addresses changed, and create a print file of each?

Any help will be GREATLY appreciated!

Cheryl
:doh:

[Killian]

Hi Cheryl,
This depends on how you hold the name/address data on the userform.
If they're in a listbox, for example, you would do something like
For i = 1 to ListBox1.Count
sSet myDoc=Documents.Add templatename
With myDoc
'add Name/address to bookmark using ListIndex(i)
.SaveAs FileName
.PrintOut PrintToFile:=True, TrueOutputFileName:=FileName
.Saved=True
.Close
End With
Next iHowever, this isn't the best way to handle this operation.
What you're doing is a mailmerge (to the printer) so I would suggest you look at doing it that way, either just through the MailMerge wizard (which has a name/address collection dialog as one of it's steps), or incorporate a merge with your userform code.

[clhare]

I can't do a traditional mail merge. These have to be .dot files and they want the macro to obtain this information and then create separate .doc files as well as .prn files. I have to find a way to accomplish it.

Cheryl

[fumei]

You have not spec'ed this out enough to give a full response. But you can use Document_New of the template.
Sub Document_New()
Dim var
For var = 1 To 6
Documents.Add
' get your data and do your stuff
' to each document here
' then write a print file
Application.PrintOut Range:=wdPrintAllDocument, _
Item:=wdPrintDocumentContent, Copies:=1, Pages:="", _
PageType:=wdPrintAllPages, PrintToFile:=True, _
OutputFileName:="c:\test\Blahblah.prn", _
Append:=False
' close each doc...SAVE?????
ActiveDocument.Close
Next
End Sub

If you post clearer needs - how are you getting the data? - perhaps by posting the code of the UserForm, I am sure this can be done.

Another way is to run the UserForm first, pick up the data THEN create each new doc from there.

Spec it out more please.

[MOS MASTER]

Hi Cheryl, :D

Like the rest I'm wondering what the code in the UserForm looks like!

If this is a template doc witch has bookmarks in it for one address at a time and the UserForm would start up on creation of a new file based on that template. (AutoNew)

Then I would have the code of the Userform loop through the addresses and have them filled in one at a time in the newly created document.

I would write the address to a ranged bookmark and I would overwrite that value each time with every new Address.

Then I would saveAs the document and do a print a file routine.

Then loop again for the other addresses....

But al has to do with how you want your UserForm to work so this is also a half answered answer from my side!

Enjoy! :whistle:

[clhare]

My intent is to use a user form to get the data. I have set up the form to allow the user to fill in 5 names and addresses into the text boxes on the form.

I don't actually have any code in the user form yet as I have no idea what to do with the names and addresses once the macro has them.

[clhare]

Sorry if I am not providing enough details. I'm really stumped, so I don't have much I can provide. Once I have the data from the user form, I want to create the additional documents, one for each name and address, using the document that I originally created with my template as the base. Does that make sense?

The bookmark idea sounds interesting, but I don't understand how it works.

Cheryl

[MOS MASTER]

My intent is to use a user form to get the data. I have set up the form to allow the user to fill in 5 names and addresses into the text boxes on the form.

I don't actually have any code in the user form yet as I have no idea what to do with the names and addresses once the macro has them.
Hi, :D

Was out for a while I'm back now.

I was thinking:
Why not make a simple UserForm and just put in one person's address data and press a button to do your print and save operation?

You could then just add other data and press the button again and make more files.

At the End you just hit another button to Unload the Form..

Would this work for you? If so I'll make a little example for you.

Enjoy! :whistle:

[MOS MASTER]

The bookmark idea sounds interesting, but I don't understand how it works.

Cheryl
Have to to one little thing first and then I'll make a small example for you! :whistle:

[fumei]

here you go.

The userform is simple and can be expanded to your need.

the data is collected on the form, and dumped into formfields. the document does NOT have to be protected to acccept input into formfields.

The Make Print File button does just that. the Done button closes everything off.

[fumei]

Oh and it certainly could be adjusted to use bookmarks instead of formfields. Formfields are easier though. I have the field shading turned off so they look like regular text.

[fumei]

Sorry Joost...I couldn't resist...:tease:

[MOS MASTER]

Hi Gerry, :D

hahaha you beat me to it...haven't started yet! :rofl: (so I'm not gonna)

[MOS MASTER]

Sorry Joost...I couldn't resist...:tease:

Yes I know....I have brought it on myself! :whip

[fumei]

Cheryl, please post back if this helps you move this forward. Or, if you have problems and need any help adjusting this to your actual needs.

Please notice that you do not need to do a PrintScreen etc etc that you mentioned in your first post. I have the .prn files incrementing by number. You will need to change, of course, the folder the .prn files are writtin to. Inmay be the easiest to just hard code the folder location. But....if you wanted to, you could bring up a folder location dialog. My feeling is that unless you really need to, don't bother.

[MOS MASTER]

The other thing that I was wondering about is if the files would also have to be saved as separate Word documents. (This was my understanding....perhaps I'm wrong)

If so we have to make small adjustment! :whistle:

[fumei]

It would not be a problem at all to save each as a doc file...but the specs - as posted - specifically stated, and ONLY stated, the need to write printer files.

Better specs - better code.

[mdmackillop]

Hi Cheryl,
There's a KB entry here with various samples of fill in methods from a userform, some of which you could incorporate into Gerry's form.
http://www.vbaexpress.com/kb/getarticle.php?kb_id=184

[MOS MASTER]

Yepz..I'm reading it different over here...

I'm reading: "create a print file of each of these 6 files" and "Is it possible to create all these additional files based on the original file"

Yes subject to misinterpretation....Don't know if the files are redundant or not...

your last line says it all as always...:whistle:

[fumei]

Funny, I never thought there was mention of an original file. If the INITIAL clone (assuming this all starts from a File > New) is part of the write to print file...there is no original file. The template remains intact - empty. If there 6 "versions", with different data - which is the original? The template, which is, and remains, empty?

It could be a misunderstanding, as I notice Cheryl mentions an "output" file. I don't think I would call the created document of a template .DOT really an output file.

I can see that she mentions "the Word docs", and going back to make the print files. I - also badly - assumed that as she was stating "going back" because she did not realize you could write the print files immediately. And, perhaps wrongly...probably wrongly... thought there was no need for the doc files. There was no mention of saving each one as a doc file, and if you do not need to save them...why have them? If the principal purpose - as it seemed - is to create the print files...then...

Better specs - better code. Hey I should put that in my signature. nah. That would become really annoying.

[MOS MASTER]

Better specs - better code. Hey I should put that in my signature. nah. That would become really annoying.
No not really!

You should do that..it's your line and it's good!

Cheryl, please enlighten us?what do you really want! :dunno

[clhare]

Okay, here's what happens....

1. I run the template (.dot file) and create a new document based on the template. That will be the "original".

2. I bring up a user form which allows the user to add up to five additional recipients of the letter (for the cc list).

3. Once I have the cc information, I need to add the names to the cc list in the document, save the "original" file and create a print file (.prn file) for it.

4. Then, for the first person on the cc list, I need to insert their name and address into the document, then create a separate Word doc (.doc file) and a separate print file (.prn file) for that person.

5. For the second person on the cc list, I need to do the same as step 4.

6. For the third person on the cc list, same as step 4.

7. For the fourth person on the cc list, same as step 4.

8. For the fifth person on the cc list, same as step 4.

If there are less than 4 names and addresses entered into the user form, then I'll need to stop after the last one that was entered.

9. When all the cc files have been created, then I can close everything out.

Whew!

Cheryl

[fumei]

careful...we are getting close to good specs now.

4. Then, for the first person on the cc list, I need to insert their name and address into the document, then create a separate Word doc (.doc file) and a separate print file (.prn file) for that person.

Here is the loose part. "Insert the name and address into the document."

In a different location from the original? Which brings up a good spec to knwo...where is the "original" data going? Do you include the other cc names?

Also, you make no mention of the filenames you would like this saved as. Is it important? Would ccDoc2.doc, ccDoc3.doc work? Do you want them saved with some reference to the ccName?

Better specs....yadda yada yadda.

As it stands, the code I supplied can easily be adjusted to do this.

[MOS MASTER]

Hi Cheryl, :D

Yes you've done well but like Gery says some more blank spots to polish!

Can you please provide us with your UserForm?
That would help because I would like to see the fields you are using to get a better understanding of what you want..

O and a answer to Gerry's comments would also help providing you with the code for your UserForm

Enjoy! :whistle:

[clhare]

Sorry about that (I am so swamped, it's hard to think anymore). The template (.dot) file consists of a mailing sheet with a name and address that will appear in an envelope window, followed by the letter itself.

The cc names (there can be up to 5 of them) will need to be added at the bottom of the letter in the cc list. Then for each of the 5 cc's, the name and address will need to be added on the mailing sheet in place of the "original" name and address.

So, there could end of being 6 copies created in total of this document, and the only difference between them will be the name and address on the mailing sheet.

Cheryl

[MOS MASTER]

Hi Cheryl, :D

I'll start on your question but do you have the template for us to work with? (Zipt) :thumb

[clhare]

Okay, I have a sample file. Hopefully, I've gone about attaching it correctly, since I haven't attached here before.

Cheryl
:think:

[MOS MASTER]

Hi Cheryl, :D

Got it..
Will have a look at it! :thumb

[MOS MASTER]

Hi, :D

Before I mess things up..what do ya whanna do with those 2 address blocks (Enveloppe and letter)

Do the both have to contain the same adress of the receipient? (So basically need the same data?)

The second UserForm in there does that have to stay in there? I mean is that the UserForm on wich you make the "Origanal file" and let's say after that's created the Second form (5 receipts) has to pop-up...

Aren't we a pain in the *** for asking you all these questions! :rofl:

[clhare]

The address block in the letter with be the original addressee (like the plan participant). The address block on the mailing page will be the part that changes. Each of the 6 addresses (1 original addressee, 5 cc's) will take turns being inserted into that spot.

The frmInitialInfo will pop-up when the template is first run. Once the user does whatever they need to do within the text (there's gonna be some form fields in the text), they will need to run the second macro (for frmCcList) to complete the document. Once the cc list is setup (names are added to the letter, and all those lovely extra files are created) everything would be done and all the files could close.

You guys are terrific for taking time to help me! Thanks so much for your patience!

Cheryl

[fumei]

1. The forms are unloaded explicitly - this is good. However, generally speaking, the unload comes at the last of the procedure.

2. Why are you using those awful textboxes????/

3. This is clearly a case for formfields.

4. Why do you have the blank page between page 1, and the other one with text?

You can combine statement, as in:

strAddName = txtAddName.Value
strAddName = UCase(strAddName)

can be: strAddName = UCase(txtAddName.Value), although, since you are dealing with strings, you may as well use:
strAddName = UCase(txtAddName.Text)

I am sure Joost will tip his hat way before me and come up with something very good. Me? I am rebuilding it using formfields and conditional logic.

[clhare]

I have no choice. They don't want to use form fields on the letter itself. There's going to be some other pages (with attachment-type stuff) where I can use form fields, but not on the letter. For the letter itself, I have to put any variability in the macro. I have no choice.

Also, I have to go with a specific format, so I have to keep the text boxes. I have static text in them both because they contain information that will be coming from the macro.

There's a blank page that will print between the first page (the mailing sheet) and the second page (the letter) because these will be printed duplex. So I have to have an odd section break after the mailing sheet so the letter doesn't print on the back of it.

[MOS MASTER]

Hi Gerry, :D

You have a lot of good points.

But I'm pressed for time and I'm gonna use the Form as is. (I would have done another approach but this will work)

As for the Textboxes..well I hate them to but no problem I'm gonna use them...

I'll try to make it as less code as I can but the count of textboxes is against me...(but I can still come up with something nifty) :yes

Hi Cheryl...thanks god Gerry came up with his Formfields!

I wanted to surprise Gerry with the use of Formfields and I allready had them in! :devil: (what a pitty....)

Well you're not allowed to use them I here...wel ok back to the bookmark sollution ....

It's comming! :whistle:

[MOS MASTER]

Hi Cheryl, :D

I basically replaced al the code and as requested I used bookmarks.

The New AutoNew Code:
Option Explicit
Sub Main()
With Application
With .ActiveWindow.View
.Zoom.PageFit = wdPageFitBestFit
.ShowHiddenText = True
End With

.ScreenUpdating = False

Load frmInitialInfo
frmInitialInfo.Show

.Browser.Target = wdBrowsePage
.Selection.HomeKey Unit:=wdStory

.DisplayStatusBar = True
.StatusBar = "Macro processing has been completed."

.ScreenRefresh
.ScreenUpdating = True
End With
End Sub


The Call to the second form has to be done by hand because I'm not sure what you want whit that but the code is still the same:
Option Explicit
Public sPath As String
Public Sub FillBookmark(sText As String, sBookmark As String)
Dim oRange As Word.Range
With Application.ActiveDocument
Set oRange = .Bookmarks(sBookmark).Range
oRange.Text = sText
.Bookmarks.Add Name:=sBookmark, Range:=oRange
Set oRange = Nothing
End With
End Sub
Sub GetCcInfo()
' This procedure loads the form to get things started
Load frmCcList
frmCcList.Show
End Sub

As you see there is a special sub in there to make fill and range the bookmarks in the document.

The new code of form frmInitialInfo:
Option Explicit
Private Sub cmdCancel_Click()
' Closes the document without saving it
ActiveDocument.Close SaveChanges:=wdDoNotSaveChanges
' Removes the form and ends procedure
Unload Me
End Sub
Private Sub cmdOK_Click()
Dim sAddress As String
Dim sSave As String
Me.Hide
sPath = ThisDocument.Path & Application.PathSeparator & "Receipts" & Application.PathSeparator

If Dir(sPath) = "" Then MkDir Path:=sPath

sSave = sPath & Me.txtAddName.Text
sAddress = Me.txtAddName.Text & vbCr & _
Me.txtAddress1.Text & vbCr & _
Me.txtAddress2.Text & vbCr & _
Me.txtAddress3.Text & vbCr & _
Me.txtAddress4.Text

With Application.ActiveDocument
Call FillBookmark(UCase(sAddress), "Address")
Call FillBookmark(Me.txtDate.Text, "dDate")
Call FillBookmark(UCase(sAddress), "Address2")

.SaveAs FileName:=sSave & "______0" & ".doc"

.PrintOut Background:=False, _
Range:=wdPrintAllDocument, _
Item:=wdPrintDocumentContent, _
Copies:=1, _
Pages:="", _
PageType:=wdPrintAllPages, _
PrintToFile:=True, _
OutputFileName:=sSave & "______0" & ".prn", _
Append:=False
End With

Unload Me
End Sub



And the code of Form frmCcList:
Option Explicit
Private sCC As String
Private sAddress(1 To 5) As String
Private Sub cmdCancel_Click()
ActiveDocument.Close wdDoNotSaveChanges
Unload Me
End Sub
Private Sub cmdOK_Click()
Dim iCnt As Integer
Dim sSave As String
Dim sCtl As String
Me.Hide
Call BuildData
Call FillBookmark(sCC, "cc")

For iCnt = LBound(sAddress) To UBound(sAddress)
sCtl = Me.Controls(CStr("txtRecpName" & iCnt)).Text

If sCtl <> "" Then
sSave = sPath & sCtl

With Application.ActiveDocument
Call FillBookmark(sAddress(iCnt), "Address2")
.SaveAs FileName:=sSave & "______" & iCnt & ".doc"

.PrintOut Background:=False, _
Range:=wdPrintAllDocument, _
Item:=wdPrintDocumentContent, _
Copies:=1, _
Pages:="", _
PageType:=wdPrintAllPages, _
PrintToFile:=True, _
OutputFileName:=sSave & "______" & iCnt & ".prn", _
Append:=False
End With
End If
Next

Unload Me
End Sub
Private Sub BuildData()
Dim iCnt As Integer
Dim sTemp As String
Dim sBuild As String
For iCnt = 1 To 5
sBuild = ""
sTemp = ""
sTemp = Me.Controls(CStr("txtRecpName" & iCnt)).Text

If sTemp <> "" Then
sCC = sCC & sTemp & vbCr
sBuild = sTemp & vbCr
sTemp = ""
sTemp = Me.Controls(CStr("txtAddressLn1_" & iCnt)).Text

If sTemp <> "" Then
sBuild = sBuild & sTemp & vbCr
sTemp = ""
sTemp = Me.Controls(CStr("txtAddressLn2_" & iCnt)).Text

If sTemp <> "" Then
sBuild = sBuild & sTemp & vbCr
sTemp = ""
sTemp = Me.Controls(CStr("txtAddressLn3_" & iCnt)).Text

If sTemp <> "" Then
sBuild = sBuild & sTemp & vbCr
sTemp = ""
sTemp = Me.Controls(CStr("txtAddressLn4_" & iCnt)).Text

If sTemp <> "" Then
sBuild = sBuild & sTemp
End If

End If

End If

End If
End If
Debug.Print sBuild
sAddress(iCnt) = sBuild
Next
End Sub


I'm sure there's room for Improvement but it al runs well and fast over here.

What happens:
You double click the template
You fill in the form
Data get's filled in and document and prn-file get saved in a newly created folder!

Same happens in the other form wihout making a new folder.

The most important thing in the CCList Form is that only those files get saved and printed that have a name in the CC list!

I had no time for extensive testing so you do that for me and tell me if and where the code needs improvement. (And make sure you explain what's going fissy...)

Enjoy! :whistle:

[clhare]

Wow! This is terrific! Everything works great and with so little code. I am self-taught, so there's so much that I don't know. I'm going to spend some time figuring out how you did this.

The only thing that was wrong was the that the wrong name and address was updating for the cc's. I switched the bookmarks around and then it was fine.

Thank you so much for your help. Now to add all the text in....

Cheryl
:bow:

[MOS MASTER]

Hi Cheryl, :yes
It was my pleasure! :beerchug: