I have a program (see program below) that sometimes takes several minutes to
run .
I wonder if there is any way to speed up the program.

Thanks in advance.
Maperalia





Option Explicit

Sub OpenReport()
Dim wordApp As Object
Dim fNameAndPath As String
Dim Filename As String
Dim sPath As String
Dim WO As String
Dim Progname As String
Dim myDateTime As String

Application.ScreenUpdating = False
WO = Worksheets("Template").Range("A1")
myDateTime = Format(Worksheets("Template").Range("A2").Value, "yyyymmdd")
Filename = "" & WO & "_M_" & myDateTime & ""

Dim sDocName As String
sDocName = Replace(sPath, ".xls", ".doc")

fNameAndPath = "S:\Test\" & Filename & ".doc"
Set wordApp = CreateObject("Word.Application")
wordApp.Documents.Open (fNameAndPath)
wordApp.Visible = True
wordApp.Activate


End Sub

Although I can't see how it would help, when in doubt I always put in

Application.Calculation = xlManual

I don't understand your code though. For example why do you add null strings to
the front and back of Filename?

And what's up with the sPath variable? You don't assign it, but use it to call
a Replace(...) function that doesn't seem to be part of VBA so I presume it's a
UDF of yours? What's it do?

I'd start by just putting MsgBox breaks scattered into the code until you can
figure out which line (or lines) are taking minutes to execute.

Good luck...

Bill
----------------------
maperalia wrote:
I have a program (see program below) that sometimes takes several minutes to
run .
I wonder if there is any way to speed up the program.

Thanks in advance.
Maperalia





Option Explicit

Sub OpenReport()
Dim wordApp As Object
Dim fNameAndPath As String
Dim Filename As String
Dim sPath As String
Dim WO As String
Dim Progname As String
Dim myDateTime As String

Application.ScreenUpdating = False
WO = Worksheets("Template").Range("A1")
myDateTime = Format(Worksheets("Template").Range("A2").Value, "yyyymmdd")
Filename = "" & WO & "_M_" & myDateTime & ""

Dim sDocName As String
sDocName = Replace(sPath, ".xls", ".doc")

fNameAndPath = "S:\Test\" & Filename & ".doc"
Set wordApp = CreateObject("Word.Application")
wordApp.Documents.Open (fNameAndPath)
wordApp.Visible = True
wordApp.Activate


End Sub

Bill;
Thanks very much!!!. I have tried it but I do not have any different than
before....
I will keep my program as is because I do not know is server or my computer..

Best regards.
Maperalia

"Bill Martin" wrote:

Although I can't see how it would help, when in doubt I always put in

Application.Calculation = xlManual

I don't understand your code though. For example why do you add null strings to
the front and back of Filename?

And what's up with the sPath variable? You don't assign it, but use it to call
a Replace(...) function that doesn't seem to be part of VBA so I presume it's a
UDF of yours? What's it do?

I'd start by just putting MsgBox breaks scattered into the code until you can
figure out which line (or lines) are taking minutes to execute.

Good luck...

Bill
----------------------
maperalia wrote:
I have a program (see program below) that sometimes takes several minutes to
run .
I wonder if there is any way to speed up the program.

Thanks in advance.
Maperalia





Option Explicit

Sub OpenReport()
Dim wordApp As Object
Dim fNameAndPath As String
Dim Filename As String
Dim sPath As String
Dim WO As String
Dim Progname As String
Dim myDateTime As String

Application.ScreenUpdating = False
WO = Worksheets("Template").Range("A1")
myDateTime = Format(Worksheets("Template").Range("A2").Value, "yyyymmdd")
Filename = "" & WO & "_M_" & myDateTime & ""

Dim sDocName As String
sDocName = Replace(sPath, ".xls", ".doc")

fNameAndPath = "S:\Test\" & Filename & ".doc"
Set wordApp = CreateObject("Word.Application")
wordApp.Documents.Open (fNameAndPath)
wordApp.Visible = True
wordApp.Activate


End Sub

And have you tried using the MsgBox commands (or the debugger) to step through
the code to see where all the time is actually consumed?

Bill
------------------------
maperalia wrote:
Bill;
Thanks very much!!!. I have tried it but I do not have any different than
before....
I will keep my program as is because I do not know is server or my computer..

Best regards.
Maperalia

"Bill Martin" wrote:


Although I can't see how it would help, when in doubt I always put in

Application.Calculation = xlManual

I don't understand your code though. For example why do you add null strings to
the front and back of Filename?

And what's up with the sPath variable? You don't assign it, but use it to call
a Replace(...) function that doesn't seem to be part of VBA so I presume it's a
UDF of yours? What's it do?

I'd start by just putting MsgBox breaks scattered into the code until you can
figure out which line (or lines) are taking minutes to execute.

Good luck...

Bill
----------------------
maperalia wrote:

I have a program (see program below) that sometimes takes several minutes to
run .
I wonder if there is any way to speed up the program.

Thanks in advance.
Maperalia





Option Explicit

Sub OpenReport()
Dim wordApp As Object
Dim fNameAndPath As String
Dim Filename As String
Dim sPath As String
Dim WO As String
Dim Progname As String
Dim myDateTime As String

Application.ScreenUpdating = False
WO = Worksheets("Template").Range("A1")
myDateTime = Format(Worksheets("Template").Range("A2").Value, "yyyymmdd")
Filename = "" & WO & "_M_" & myDateTime & ""

Dim sDocName As String
sDocName = Replace(sPath, ".xls", ".doc")

fNameAndPath = "S:\Test\" & Filename & ".doc"
Set wordApp = CreateObject("Word.Application")
wordApp.Documents.Open (fNameAndPath)
wordApp.Visible = True
wordApp.Activate


End Sub