Cindy,
In answer to your questions:
In which application is the VBA code? And, in all this, how are you linking
to
the other application, in order to manipulate it? In which version of
Windows?
And what kinds of anti-virus or other document management software is running?
We are running Windows 2000 with Service Pack 4, and Office 2000.
The only antivirus software running is Vet anti virus.
When opening the document in word directly, it only takes 3-4 seconds to open.
The excel code is stored with the personnal.xls file in Excel.
I am using "Early Binding" to access the word application. Code below.
On Error Resume Next
Set wdApp = GetObject(, "Word.Application")
If wdApp Is Nothing Then
Set wdApp = CreateObject("Word.Application", "")
End If
With wdApp
.Visible = True
End With
On Error GoTo 0
With wdApp
.DisplayAlerts = wdAlertsNone
.Documents.Open
Filename:="F:\ENQUIRY\Pricing\Under_Development\Estimate.doc" '[!! This step
takes about 1 minute !!]
Set Estimate = ActiveDocument
With Estimate
.MailMerge.OpenDataSource Name:=WorkPath & "\WkplEstimate.xls", _
ConfirmConversions:=False, ReadOnly:=True, LinkToSource:=True, _
AddToRecentFiles:=False, PasswordDocument:="",
PasswordTemplate:="", WritePasswordDocument:="",
WritePasswordTemplate:="", Revert:=True, _
Format:=wdOpenFormatAuto, Connection:="Entire Spreadsheet",
SQLStatement:="", SQLStatement1:="" [!! This step takes about
1 minute!!]
... etc etc
The opening of the word application itself is instant. It is the Document
Open, and mailmerge connection that each take about 1 minute to run. I
thought there may be a problem with excel trying to open 2 copies of the
personnel.xls file, however I made it read only to resolve this issue but it
did not seem to help.
Any suggestions would be most appreciated.
Regards,
Simon
Cindy M -WordMVP- said:
Hi =?Utf-8?B?V2VkZ2V0YWls?=,
I have an excel [2000] file that I use to generate a mail merge data set for
word.
Using VBA, I create a excel file with one [1] sheet, then open word, and an
existing mail merge document with all the fields. I then relink the merge
document to the new data file and merge.
This all works but takes approx. 1 minute to the file, and another minute to
relink the new data file. If I open the document through word manually, it
only takes about 3-5 seconds to open the word document, similar to relink.
In which application is the VBA code? And, in all this, how are you linking to
the other application, in order to manipulate it? In which version of Windows?
And what kinds of anti-virus or other document management software is running?
Generally, automation of an application from outside that application is going
to be slower than executing an action within the application. In addition, if
you're actually, say, starting Word up, then you also have to account for the
loading time. A minute seems a bit long, but if there are lots of "layers"
that have to be resolved (security software, loading addins, loading the VBA
stuff before automation can begin, opening the document, making the link),
then "appox. 1 minute" is certainly within reason.
Cindy Meister
INTER-Solutions, Switzerland
http://homepage.swissonline.ch/cindymeister (last update Jun 8 2004)
This reply is posted in the Newsgroup; please post any follow question or
reply in the newsgroup and not by e-mail
