Custom App

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

axeman - 29 Jul 2005 16:47 GMT

Ok, so I'm building this form that will cycle through a range of documents.
The range may vary depending on user selection. That is, the module that
processes the information may cycle through as little as 5 documents (sets
values from the text fields into the fields on the documents) to as many as
maybe 20 documents. What is the fastest way to get this done? Some documents
do not have any fields in them so they don't require any information to be
added to them but they do need to be copied over to another folder for the
user to browse. I have attached the code that I have used for this below. My
problem I believe that this code can run faster I just don't know how. Is
there any way to tell if a document has bookmarks without opening it? That
would definitely cut down on the processing time I think. Any suggestions are
welcome!!!!

Code: (startList & endList are defined in a separate module: current range
is 5-16)
For i = startList To endList

' Opens, saves, and closes each document

Set oDoc = Documents.Open(templateDir + Template$(i) + ".dot",
Visible:=False)
oDoc.ActiveWindow.SetFocus
oDoc.ActiveWindow.View.Type = wdPrintView
If oDoc.Bookmarks.Count >= 1 Then
Application.Run MacroName:="SetFormFieldInfo"
oDocSaveName = Template$(i) + ".doc"
oDoc.SaveAs FileName:=saveDir + oDocSaveName,
AddToRecentFiles:=False
Else:
WordBasic.CopyFileA templateDir + rifTemplate$(i) + ".dot",
saveDir + Template$(i) + ".doc"
End If

oDoc.Close

Next i

Reply to this Message

Jonathan West - 29 Jul 2005 18:32 GMT

Comments inline

> Ok, so I'm building this form that will cycle through a range of
> documents.
[quoted text clipped - 22 lines]
> Set oDoc = Documents.Open(templateDir + Template$(i) + ".dot",
> Visible:=False)

> oDoc.ActiveWindow.SetFocus
> oDoc.ActiveWindow.View.Type = wdPrintView

You don't need to two lines above oDoc will still refer to the right
document even if the window is not showing.

> If oDoc.Bookmarks.Count >= 1 Then
> Application.Run MacroName:="SetFormFieldInfo"

If the SetFormFieldInfo macro is in the same template as this routine, you
can simply have this line instead

SetFormFieldInfo

Also, it may be that significant speed improvements are possible in the
SetFormFieldInfo routine.

> oDocSaveName = Template$(i) + ".doc"
> oDoc.SaveAs FileName:=saveDir + oDocSaveName,
> AddToRecentFiles:=False

This rather worries me, and suggests that you are getting mixed up between
documents and templates. There is far more to the difference between them
than a filename extension. This article may help you.

What do Templates and Add-ins store?
http://www.word.mvps.org/FAQs/Customization/WhatTemplatesStore.htm

> Else:
> WordBasic.CopyFileA templateDir + rifTemplate$(i) + ".dot",
[quoted text clipped - 4 lines]
>
> Next i

In addition, your code sample gives no clue as to where your documents are
being saved to. If the destination is on a remote network folder, then you
would be well advised to save the documents to a temporary folder on the
local drive, and then then all the documents have been saved, copy them to
the network drive. This is because saving a document in Word involves many
separate writes and reads from the disk. Doing this over the network
involves multiple waits for the data to be transmitted back & forth, whereas
when saving to the local disk you get the full advantage of any memory cache
on the disk controller. When the saving is done, a simple file copy to the
network is much quicker.

Signature

Regards
Jonathan West - Word MVP
www.intelligentdocuments.co.uk
Please reply to the newsgroup
Keep your VBA code safe, sign the ClassicVB petition www.classicvb.org

Reply to this Message

axeman - 29 Jul 2005 20:34 GMT

Comments inline:

> > Set oDoc = Documents.Open(templateDir + Template$(i) + ".dot",
> > Visible:=False)
[quoted text clipped - 4 lines]
> You don't need to two lines above oDoc will still refer to the right
> document even if the window is not showing.

For some reason if I don't set those two when the module runs it won't
update the fields in the documents with the info from the form. Also, after a
document has been processed and a user wants to view it I would like for it
to show up in Print Layout View. If I don't set it here it opens in Normal
view. That is, of course, with the Visible = False option turned on which
actually allows the program to run faster....

--------------------------------------------------

> > If oDoc.Bookmarks.Count >= 1 Then
> > Application.Run MacroName:="SetFormFieldInfo"
[quoted text clipped - 6 lines]
> Also, it may be that significant speed improvements are possible in the
> SetFormFieldInfo routine.

I didn't know that thanks for the heads up. Also, not all documents have the
same fields so there I have to do it that way. The only other way to it is to
eliminate all the If.Then..Else statements and add to the top of that macro
"On Error Resume Next". The only problem with that is that it won't catch any
errors at all.

-------------------------------------------------------

> > oDocSaveName = Template$(i) + ".doc"
> > oDoc.SaveAs FileName:=saveDir + oDocSaveName,
[quoted text clipped - 6 lines]
> What do Templates and Add-ins store?
> http://www.word.mvps.org/FAQs/Customization/WhatTemplatesStore.htm

I'll have to read this...

------------------------------------------------------------

> > Else:
> > WordBasic.CopyFileA templateDir + rifTemplate$(i) + ".dot",
[quoted text clipped - 15 lines]
> on the disk controller. When the saving is done, a simple file copy to the
> network is much quicker.

File is being saved locally to the same disk so that is not an issue.

Thanks and keep the suggestions coming!

Reply to this Message

axeman - 01 Aug 2005 14:19 GMT

Here is the code that sets the formfield info:

Code:

Private Sub SetFormFieldInfo()

' Sets formfields on documents to values on the userform

Dim TermDate, RIFEffectiveDate As String

If ActiveDocument.Bookmarks.Exists("RFName") = True Then
ActiveDocument.FormFields("RFName").Result =
Trim(formRIF.txtFName.Value)
End If

If ActiveDocument.Bookmarks.Exists("RFName2") = True Then
ActiveDocument.FormFields("RFName2").Result =
Trim(formRIF.txtFName.Value)
End If

If ActiveDocument.Bookmarks.Exists("RFName3") = True Then
ActiveDocument.FormFields("RFName3").Result =
Trim(formRIF.txtFName.Value)
End If

If ActiveDocument.Bookmarks.Exists("RFName4") = True Then
ActiveDocument.FormFields("RFName4").Result =
Trim(formRIF.txtFName.Value)
End If

If ActiveDocument.Bookmarks.Exists("RFName5") = True Then
ActiveDocument.FormFields("RFName5").Result =
Trim(formRIF.txtFName.Value)
End If

If ActiveDocument.Bookmarks.Exists("RLName") = True Then
ActiveDocument.FormFields("RLName").Result =
Trim(formRIF.txtLName.Value)
End If

If ActiveDocument.Bookmarks.Exists("RLName2") = True Then
ActiveDocument.FormFields("RLName2").Result =
Trim(formRIF.txtLName.Value)
End If

If ActiveDocument.Bookmarks.Exists("RLName3") = True Then
ActiveDocument.FormFields("RLName3").Result =
Trim(formRIF.txtLName.Value)
End If

If ActiveDocument.Bookmarks.Exists("RLName4") = True Then
ActiveDocument.FormFields("RLName4").Result =
Trim(formRIF.txtLName.Value)
End If

If ActiveDocument.Bookmarks.Exists("RLName5") = True Then
ActiveDocument.FormFields("RLName5").Result =
Trim(formRIF.txtLName.Value)
End If

If ActiveDocument.Bookmarks.Exists("REMPNO") = True Then
ActiveDocument.FormFields("REMPNO").Result =
Trim(formRIF.txtEENumber.Value)
End If

If ActiveDocument.Bookmarks.Exists("REMPNO2") = True Then
ActiveDocument.FormFields("REMPNO2").Result =
Trim(formRIF.txtEENumber.Value)
End If

If ActiveDocument.Bookmarks.Exists("REMPNO3") = True Then
ActiveDocument.FormFields("REMPNO3").Result =
Trim(formRIF.txtEENumber.Value)
End If

If ActiveDocument.Bookmarks.Exists("RSOCSEC") = True Then
ActiveDocument.FormFields("RSOCSEC").Result =
Trim(formRIF.txtSSN1.Value) + "-" + Trim(formRIF.txtSSN2.Value) + "-" +
Trim(formRIF.txtSSN3.Value)
End If

If formRIF.chkNotificationLetter.Value = True Then
If ActiveDocument.Bookmarks.Exists("RSADDRESS") = True Then
ActiveDocument.FormFields("RSADDRESS").Result =
Trim(formRIF.txtStreet.Value)
End If

If ActiveDocument.Bookmarks.Exists("RCITY") = True Then
ActiveDocument.FormFields("RCITY").Result =
Trim(formRIF.txtCity.Value)
End If

If ActiveDocument.Bookmarks.Exists("RSTATE") = True Then
ActiveDocument.FormFields("RSTATE").Result =
Trim(formRIF.txtState.Value)
End If

If ActiveDocument.Bookmarks.Exists("RZIP") = True Then
ActiveDocument.FormFields("RZIP").Result =
Trim(formRIF.txtZipCode.Value)
End If
End If

If ActiveDocument.Bookmarks.Exists("RNDATE1") = True Then
ActiveDocument.FormFields("RNDATE1").Result =
Trim(formRIF.txtNoticeDate.Value)
End If

If ActiveDocument.Bookmarks.Exists("RNDATE2") = True Then
ActiveDocument.FormFields("RNDATE2").Result =
Trim(formRIF.txtNoticeDate.Value)
End If

If ActiveDocument.Bookmarks.Exists("RSCSD") = True Then
ActiveDocument.FormFields("RSCSD").Result =
Trim(formRIF.txtSalContDate.Value)
End If

If ActiveDocument.Bookmarks.Exists("RTDATE") = True Then
TermDate = Trim(formRIF.txtSalContDate.Value)
TermDate = DateAdd("d", -1, TermDate)
ActiveDocument.FormFields("RTDATE").Result = TermDate
End If

If ActiveDocument.Bookmarks.Exists("RRIFDATE") = True Then
ActiveDocument.FormFields("RRIFDATE").Result =
Trim(formRIF.txtSalContEndDate.Value)
End If

If ActiveDocument.Bookmarks.Exists("RSRVHR") = True Then
ActiveDocument.FormFields("RSRVHR").Result =
Trim(formRIF.txtNumberWeeksSalCont.Value)
End If

If ActiveDocument.Bookmarks.Exists("RWORKSTATE") = True Then
ActiveDocument.FormFields("RWORKSTATE").Result =
Trim(formRIF.txtWorkState.Value)
End If

If ActiveDocument.Bookmarks.Exists("RPAYGRP") = True Then
ActiveDocument.FormFields("RPAYGRP").Result =
Trim(formRIF.cmbPayGroup.Value)
End If

If ActiveDocument.Bookmarks.Exists("RRESERVEBC") = True Then
ActiveDocument.FormFields("RRESERVEBC").Result =
Trim(formRIF.txtReserveBC.Value)
End If

If ActiveDocument.Bookmarks.Exists("RMASTERBC") = True Then
ActiveDocument.FormFields("RMASTERBC").Result =
Trim(formRIF.txtCurrentBC.Value)
End If

If formRIF.chkAmex.Value = True Then
If ActiveDocument.Bookmarks.Exists("AmexTotal") = True Then
ActiveDocument.FormFields("AmexTotal").Result =
Trim(formRIF.txtTotalBalanceDue.Value)
End If

If ActiveDocument.Bookmarks.Exists("AmexCC") = True Then
ActiveDocument.FormFields("AmexCC").Result =
Trim(formRIF.txtAmex.Value)
End If

If ActiveDocument.Bookmarks.Exists("AmexChk") = True Then
ActiveDocument.FormFields("AmexChk").Result =
Trim(formRIF.txtTravelerCheques.Value)
End If

If ActiveDocument.Bookmarks.Exists("AmexAirRail") = True Then
ActiveDocument.FormFields("AmexAirRail").Result =
Trim(formRIF.txtOutstandingAirRail.Value)
End If
End If

If ActiveDocument.Bookmarks.Exists("RTERMDATE") = True Then
RIFEffectiveDate = Trim(formRIF.txtSalContDate.Value)
RIFEffectiveDate = DateAdd("d", 1, RIFEffectiveDate)
ActiveDocument.FormFields("RTERMDATE").Result = RIFEffectiveDate
End If

If formRIF.cmbRIFType.ListIndex <= 5 Then
If ActiveDocument.Bookmarks.Exists("RTCODE") = True Then
ActiveDocument.FormFields("RTCODE").Result = "SN8"
ActiveDocument.FormFields("RTCODEDESC").Result = "Involuntary
Reduction In Force"
End If
Else:
If ActiveDocument.Bookmarks.Exists("RTCODE") = True Then
ActiveDocument.FormFields("RTCODE").Result = "SN7"
ActiveDocument.FormFields("RTCODEDESC").Result = "Voluntary
Reduction In Force"
End If
End If

End Sub

Reply to this Message

Jonathan West - 01 Aug 2005 14:44 GMT

Comments inline

> Here is the code that sets the formfield info:
>
[quoted text clipped - 5 lines]
>
> Dim TermDate, RIFEffectiveDate As String

Did you realise that the line above has declared TermDate as a Variant
rather than as a String?

> If ActiveDocument.Bookmarks.Exists("RFName") = True Then
> ActiveDocument.FormFields("RFName").Result =
> Trim(formRIF.txtFName.Value)
> End If

You don't need = True in there. The If-Then statement works by evaluating
the boolean expression and executing if it evaluates to True. Something
which evaluates to True is still going to be True when compared with True!

Also, since you are definitely wanting to enter a string, using Trim$ rather
than Trim will be marginally faster, as it avoids a temporary conversion to
a Variant

<snip>

> If ActiveDocument.Bookmarks.Exists("RSOCSEC") = True Then
> ActiveDocument.FormFields("RSOCSEC").Result =
> Trim(formRIF.txtSSN1.Value) + "-" + Trim(formRIF.txtSSN2.Value) + "-" +
> Trim(formRIF.txtSSN3.Value)
> End If

You should use the & operator instead of + to concatenate strings. Using +
can give you all kinds of wierd results if you happen to be relying on
implicit conversion between strings and numbers

Just to demonstrate, try the following, and see if you can work out why it
gives you the results it does.

Dim i as Long, j as Long, k as String
i = 1
j = 2
k = i + j
Debug.Print k
k = i & j
Debug.Print k
k = k + i + j
Debug.Print k
k = k & i & j
Debug.Print k

> If formRIF.chkNotificationLetter.Value = True Then
> If ActiveDocument.Bookmarks.Exists("RSADDRESS") = True Then
> ActiveDocument.FormFields("RSADDRESS").Result =
> Trim(formRIF.txtStreet.Value)
> End If

<snip>

Overall, you have a very large number of tests for whether a bookmark
exists. it might be that you can speed this by loading all the bookmarks
into a string at the start, like this

Dim strBookmarks
Dim oBookmark as Bookmark
For each oBookmark in ActiveDocument.Bookmarks
strBookmarks = strBookmarks & " " & oBookmark.Name"
Next oBookmark
strBookmarks = strBookmarks & " "

Then, every time you want to check the existence of a bookmark, you can
simply check the presence of the name in the string. So this

If ActiveDocument.Bookmarks.Exists("RFName") = True Then
ActiveDocument.FormFields("RFName").Result =
Trim(formRIF.txtFName.Value)
End If

would then become this

If Instr(strBookmarks, " RFName ") > 0 Then
ActiveDocument.FormFields("RFName").Result =
Trim(formRIF.txtFName.Value)
End If

Note the spaces on each end of the RFName string.

This might be quicker because searching a string is usually much quicker
than accessing the object model.

Signature

Regards
Jonathan West - Word MVP
www.intelligentdocuments.co.uk
Please reply to the newsgroup
Keep your VBA code safe, sign the ClassicVB petition www.classicvb.org

Reply to this Message

axeman - 01 Aug 2005 16:48 GMT

Ok I went ahead and used all your suggestions except the last one because it
makes the app run a lot slower. Do you have any other suggestions? Any
additional help is greatly appreciated...

Thanks,
Axe

Reply to this Message

Jonathan West - 01 Aug 2005 16:57 GMT

> Ok I went ahead and used all your suggestions except the last one because
> it
> makes the app run a lot slower. Do you have any other suggestions?

Not really. Opening and closing Word documents simply does take time, and
not much can be done to minimise that.

Occasionally I have to run macros that process several thousand Word files,
and I just accept that I have to leave it running for an hour or so and go
and have some lunch.

Signature

Regards
Jonathan West - Word MVP
www.intelligentdocuments.co.uk
Please reply to the newsgroup
Keep your VBA code safe, sign the ClassicVB petition www.classicvb.org

Reply to this Message

axeman - 02 Aug 2005 16:08 GMT

Alright thanks for the help!

Reply to this Message