Macro running EXTREMELY slow

[Dave.Punk.Emo]

I'm using macros in Excel to automatically open Word and update linked
parts of a word document to Excel cells, and everytime I do this, it
runs exceedling slow, and sometimes just hangs and crashes.

I also have a similar problem when using another macro to print out
word documents and excel sheets.

It might just be that the computer is not up to scratch, but if it
could be a coding problem, help would be appreciated!

Here's the stats for the pc:

Pentium 4 - 1.6Ghz
512RAM
XP Pro

Guess that's all that's really relevant...

 

[Gary Keramidas]

Guess that's all that's really relevant...

except for your code.

 

[Dave.Punk.Emo]

Guess that's all that's really relevant...

except for your code.
--

Gary










- Show quoted text -

OK, fair point.. I just really wanted to know if it was just how crap
the pc was... here's the code:

Updating:


Sub Button13_Click()
Dim wdApp As Object
Dim wdDoc As Object
Dim sFname As String

sFname = "R:\SALES\Quote Generators\Quotes\2 Pass Tray\2 Pass Tray
Quote.doc" 'File Location

If sFname = "R:\SALES\Quote Generators\Quotes\2 Pass Tray\2 Pass
Tray Quote.doc" Then

Set wdApp = CreateObject("Word.Application")

wdApp.Documents.Open Filename:="R:\SALES\Quote Generators
\Quotes\2 Pass Tray\2 Pass Tray Quote.doc" 'Opens File
wdApp.Visible = True
SendKeys "{Left} {Enter}" 'Updates File
End If
End Sub


Printing:

Sub Button12_Click()
'Prints Rating Sheet to PDF
Sheet9.PrintOut Copies:=1, ActivePrinter:="Adobe PDF",
Collate:=True

'Prints Costing Sheet to PDF
Sheet8.PrintOut Copies:=1, ActivePrinter:="Adobe PDF",
Collate:=True



'Prints Guarantee to PDF
Dim wdApp As Object
Dim wdDoc As Object
Dim sFname As String

sFname = "R:\ADMINISTRATON\Standard Guarantee.doc" 'File Location

If sFname = "R:\ADMINISTRATON\Standard Guarantee.doc" Then

Set wdApp = CreateObject("Word.Application")


wdApp.Documents.Open Filename:="R:\ADMINISTRATION\Standard
Guarantee.doc" 'Opens File
wdApp.ActivePrinter = "Adobe PDF" 'Sets Adobe PDF to printer
wdApp.PrintOut 'Prints File
Application.Wait Now + TimeValue("00:00:04") 'Holds open for 4
seconds
wdApp.activedocument.Close savechanges:=False 'closes document
and doesn't save changes
wdApp.Quit 'closes Word

Set wdApp = Nothing
End If

'Prints Quote as PDF

sFname = "R:\SALES\Quote Generators\Quotes\2 Pass Tray\2 Pass Tray
Quote.doc" 'File Location

If sFname = "R:\SALES\Quote Generators\Quotes\2 Pass Tray\2 Pass
Tray Quote.doc" Then

Set wdApp = CreateObject("Word.Application")


wdApp.Documents.Open Filename:="R:\SALES\Quote Generators
\Quotes\2 Pass Tray\2 Pass Tray Quote.doc" 'Opens File
wdApp.ActivePrinter = "Adobe PDF" 'Sets Adobe PDF to printer
SendKeys "{Left} {Enter}, False" 'Updates File
wdApp.PrintOut 'Prints File
Application.Wait Now + TimeValue("00:00:04") 'Holds open for 4
seconds
wdApp.activedocument.Close savechanges:=False 'closes document
and doesn't save changes
wdApp.Quit 'closes Word

Set wdApp = Nothing
End If
End Sub

 

[Jim Cone]

Have you ever checked in Task Manager to see if there are multiple
instances of Word running?
I don't do much programming with Word,
but the following modified code may work a little better.
(it is untested)
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware
(Excel Add-ins / Excel Programming)

'----
Sub Button13_Click()
Dim wdApp As Object
Dim sFname As String

'File Location
sFname = "R:\SALES\Quote Generators\Quotes\2 Pass Tray\2 Pass Tray Quote.doc"
Set wdApp = CreateObject("Word.Application")
'Opens File
wdApp.Documents.Open Filename:="R:\SALES\Quote Generators\Quotes\2 Pass Tray\2 Pass Tray Quote.doc"
wdApp.Visible = True
SendKeys "{Left} {Enter}" 'Updates File
Set wdApp = Nothing
'The Word application is left open.
End Sub
'----------

'Printing:
Sub Button12_Click()
'Prints Rating Sheet to PDF
Sheet9.PrintOut Copies:=1, ActivePrinter:="Adobe PDF", Collate:=True
'Prints Costing Sheet to PDF
Sheet8.PrintOut Copies:=1, ActivePrinter:="Adobe PDF", Collate:=True

'Prints Guarantee to PDF
Dim wdApp As Object
Dim wdDoc As Object
Dim sFname As String

sFname = "R:\ADMINISTRATON\Standard Guarantee.doc" 'File Location
Set wdApp = CreateObject("Word.Application")
'Opens File"
Set wdDoc = wdApp.Documents.Open(Filename:="R:\ADMINISTRATION\Standard Guarantee.doc")
wdApp.ActivePrinter = "Adobe PDF" 'Sets Adobe PDF to printer
wdDoc.PrintOut 'Prints File
Application.Wait Now + TimeValue("00:00:04") 'Holds open for 4 seconds
wdDoc.Close savechanges:=False 'closes document and doesn't save changes
Set wdDoc = Nothing

'Prints Quote as PDF
sFname = "R:\SALES\Quote Generators\Quotes\2 Pass Tray\2 Pass Tray Quote.doc"
'Opens File
Set wdDoc = wdApp.Documents.Open(Filename:="R:\SALES\Quote Generators\Quotes\2 Pass Tray\2 Pass Tray Quote.doc")
SendKeys "{Left} {Enter}, False" 'Updates File
wdDoc.PrintOut 'Prints File
Application.Wait Now + TimeValue("00:00:04") 'Holds open for 4 seconds
wdDoc.Close savechanges:=False 'closes documentand doesn't save changes
Set wdDoc = Nothing
wdApp.Quit 'closes Word
Set wdApp = Nothing
End Sub
'----------

in message





- Show quoted text -

OK, fair point.. I just really wanted to know if it was just how crap
the pc was... here's the code:
-snip-

 

[Dave.Punk.Emo]

Have you ever checked in Task Manager to see if there are multiple
instances of Word running?
I don't do much programming with Word,
but the following modified code may work a little better.
(it is untested)
--
Jim Cone
San Francisco, USAhttp://www.realezsites.com/bus/primitivesoftware
(Excel Add-ins / Excel Programming)

'----
Sub Button13_Click()
Dim wdApp As Object
Dim sFname As String

'File Location
sFname = "R:\SALES\Quote Generators\Quotes\2 Pass Tray\2 Pass Tray Quote.doc"
Set wdApp = CreateObject("Word.Application")
'Opens File
wdApp.Documents.Open Filename:="R:\SALES\Quote Generators\Quotes\2 Pass Tray\2 Pass Tray Quote.doc"
wdApp.Visible = True
SendKeys "{Left} {Enter}" 'Updates File
Set wdApp = Nothing
'The Word application is left open.
End Sub
'----------

'Printing:
Sub Button12_Click()
'Prints Rating Sheet to PDF
Sheet9.PrintOut Copies:=1, ActivePrinter:="Adobe PDF", Collate:=True
'Prints Costing Sheet to PDF
Sheet8.PrintOut Copies:=1, ActivePrinter:="Adobe PDF", Collate:=True

'Prints Guarantee to PDF
Dim wdApp As Object
Dim wdDoc As Object
Dim sFname As String

sFname = "R:\ADMINISTRATON\Standard Guarantee.doc" 'File Location
Set wdApp = CreateObject("Word.Application")
'Opens File"
Set wdDoc = wdApp.Documents.Open(Filename:="R:\ADMINISTRATION\Standard Guarantee.doc")
wdApp.ActivePrinter = "Adobe PDF" 'Sets Adobe PDF to printer
wdDoc.PrintOut 'Prints File
Application.Wait Now + TimeValue("00:00:04") 'Holds open for 4 seconds
wdDoc.Close savechanges:=False 'closes document and doesn't save changes
Set wdDoc = Nothing

'Prints Quote as PDF
sFname = "R:\SALES\Quote Generators\Quotes\2 Pass Tray\2 Pass Tray Quote.doc"
'Opens File
Set wdDoc = wdApp.Documents.Open(Filename:="R:\SALES\Quote Generators\Quotes\2 Pass Tray\2 Pass Tray Quote.doc")
SendKeys "{Left} {Enter}, False" 'Updates File
wdDoc.PrintOut 'Prints File
Application.Wait Now + TimeValue("00:00:04") 'Holds open for 4 seconds
wdDoc.Close savechanges:=False 'closes documentand doesn't save changes
Set wdDoc = Nothing
wdApp.Quit 'closes Word
Set wdApp = Nothing
End Sub
'----------









OK, fair point.. I just really wanted to know if it was just how crap
the pc was... here's the code:
-snip-- Hide quoted text -

- Show quoted text -

Yeah, I have checked for multiple instances, and there is just the one
instance running. I'll give that a shot and get back to you on how it
worked. Thanks for the help.