ExcelBanter - Embedded Excel macro runs 100X slower

ExcelBanter (https://www.excelbanter.com/)

- Excel Programming (https://www.excelbanter.com/excel-programming/)

- - Embedded Excel macro runs 100X slower (https://www.excelbanter.com/excel-programming/357024-embedded-excel-macro-runs-100x-slower.html)

Embedded Excel macro runs 100X slower

Hi All,
I've bumped into an interesting situation. Upon creating and running a macro
(below) to programmtically change PPT-embedded Excel sheets, it runs MUCH
slower (~100x slower) than if I run the macro from pure Excel. I can only
think it has to do with bad Excel referencing (e.g.
support.microsoft.com/kb/178510/EN-US/), or... Also, Task Manager shows CPU
is ~98% Idle (no Excel CPU!!!) I have a feeling that the Replace method is
the problem, however I've tried all the tricks I know and nogo.
Thanks os much for your attention and help. Rgds, JS

=============================
Sub EmbeddedExcel_Replace_All_File2()
Dim Shp As Shape
Dim Sld As Slide
Dim xlApp As Excel.Application
Dim oWorkbook As Excel.Workbook
Dim oWorksheet As Excel.Worksheet
Dim SldNum As Long
tStart = Time
Set xlApp = New Excel.Application
For Each Sld In Application.ActivePresentation.Slides
For Each Shp In Sld.Shapes
If Shp.Type = msoEmbeddedOLEObject Then
If Shp.OLEFormat.ProgID = "Excel.Sheet.8" Then
Set oWorkbook = Shp.OLEFormat.Object
Set oWorksheet = oWorkbook.ActiveSheet
With xlApp
Open "C:\Documents and Settings\Administrator\Desktop\Macros\excel.txt"
For Input As #1
Do While Not EOF(1)
Input #1, sFirst, sLast
oWorksheet.Cells.Replace What:=sFirst, Replacement:=sLast,
LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=True
Loop
Close #1
End With
oWorkbook.Close (True)
Set oWorkbook = Nothing
Set oWorksheet = Nothing
TimeF = Time
End If 'Shp.Type
End If 'Shp.OLEFormat.ProgID
Next Shp
Next Sld
tEnd = Time
MsgBox "Start=" & tStart & " | End=" & tEnd & " | Lap=" &
FormatDateTime(TimeValue(tEnd) - TimeValue(tStart))
xlApp.Quit
End Sub

Embedded Excel macro runs 100X slower

Why do you do
Set xlApp = New Excel.Application

and open a new copy of excel, then never use it. Nothing references the
xlApp in your with statement. I don't know if it is the cause of the
problem, but it certainly isn't making it faster.

--
Regards,
Tom Ogilvy

"JS" wrote:

Hi All,
I've bumped into an interesting situation. Upon creating and running a macro
(below) to programmtically change PPT-embedded Excel sheets, it runs MUCH
slower (~100x slower) than if I run the macro from pure Excel. I can only
think it has to do with bad Excel referencing (e.g.
support.microsoft.com/kb/178510/EN-US/), or... Also, Task Manager shows CPU
is ~98% Idle (no Excel CPU!!!) I have a feeling that the Replace method is
the problem, however I've tried all the tricks I know and nogo.
Thanks os much for your attention and help. Rgds, JS

=============================
Sub EmbeddedExcel_Replace_All_File2()
Dim Shp As Shape
Dim Sld As Slide
Dim xlApp As Excel.Application
Dim oWorkbook As Excel.Workbook
Dim oWorksheet As Excel.Worksheet
Dim SldNum As Long
tStart = Time
Set xlApp = New Excel.Application
For Each Sld In Application.ActivePresentation.Slides
For Each Shp In Sld.Shapes
If Shp.Type = msoEmbeddedOLEObject Then
If Shp.OLEFormat.ProgID = "Excel.Sheet.8" Then
Set oWorkbook = Shp.OLEFormat.Object
Set oWorksheet = oWorkbook.ActiveSheet
With xlApp
Open "C:\Documents and Settings\Administrator\Desktop\Macros\excel.txt"
For Input As #1
Do While Not EOF(1)
Input #1, sFirst, sLast
oWorksheet.Cells.Replace What:=sFirst, Replacement:=sLast,
LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=True
Loop
Close #1
End With
oWorkbook.Close (True)
Set oWorkbook = Nothing
Set oWorksheet = Nothing
TimeF = Time
End If 'Shp.Type
End If 'Shp.OLEFormat.ProgID
Next Shp
Next Sld
tEnd = Time
MsgBox "Start=" & tStart & " | End=" & tEnd & " | Lap=" &
FormatDateTime(TimeValue(tEnd) - TimeValue(tStart))
xlApp.Quit
End Sub

Embedded Excel macro runs 100X slower

Hi Tom, thanks for your reply.
I thought the With xlApp ... End with would do it, but I guess I'm wrong.
I've tried using
xlApp.oWorksheet.Cells.Replace What:=sFirst..... but this is invalid. Do you
know I should do this?
Thanks, JS

"Tom Ogilvy" wrote in message
...
Why do you do
Set xlApp = New Excel.Application

and open a new copy of excel, then never use it. Nothing references the
xlApp in your with statement. I don't know if it is the cause of the
problem, but it certainly isn't making it faster.

--
Regards,
Tom Ogilvy

"JS" wrote:

Hi All,
I've bumped into an interesting situation. Upon creating and running a
macro
(below) to programmtically change PPT-embedded Excel sheets, it runs
MUCH
slower (~100x slower) than if I run the macro from pure Excel. I can
only
think it has to do with bad Excel referencing (e.g.
support.microsoft.com/kb/178510/EN-US/), or... Also, Task Manager shows
CPU
is ~98% Idle (no Excel CPU!!!) I have a feeling that the Replace method
is
the problem, however I've tried all the tricks I know and nogo.
Thanks os much for your attention and help. Rgds, JS

=============================
Sub EmbeddedExcel_Replace_All_File2()
Dim Shp As Shape
Dim Sld As Slide
Dim xlApp As Excel.Application
Dim oWorkbook As Excel.Workbook
Dim oWorksheet As Excel.Worksheet
Dim SldNum As Long
tStart = Time
Set xlApp = New Excel.Application
For Each Sld In Application.ActivePresentation.Slides
For Each Shp In Sld.Shapes
If Shp.Type = msoEmbeddedOLEObject Then
If Shp.OLEFormat.ProgID = "Excel.Sheet.8" Then
Set oWorkbook = Shp.OLEFormat.Object
Set oWorksheet = oWorkbook.ActiveSheet
With xlApp
Open "C:\Documents and
Settings\Administrator\Desktop\Macros\excel.txt"
For Input As #1
Do While Not EOF(1)
Input #1, sFirst, sLast
oWorksheet.Cells.Replace What:=sFirst, Replacement:=sLast,
LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=True
Loop
Close #1
End With
oWorkbook.Close (True)
Set oWorkbook = Nothing
Set oWorksheet = Nothing
TimeF = Time
End If 'Shp.Type
End If 'Shp.OLEFormat.ProgID
Next Shp
Next Sld
tEnd = Time
MsgBox "Start=" & tStart & " | End=" & tEnd & " | Lap=" &
FormatDateTime(TimeValue(tEnd) - TimeValue(tStart))
xlApp.Quit
End Sub

Embedded Excel macro runs 100X slower

I don't see any role or need for xlapp in your code. I would just remove it
and the With statements.

--
Regards,
Tom Ogilvy

"JS" wrote:

Hi Tom, thanks for your reply.
I thought the With xlApp ... End with would do it, but I guess I'm wrong.
I've tried using
xlApp.oWorksheet.Cells.Replace What:=sFirst..... but this is invalid. Do you
know I should do this?
Thanks, JS

"Tom Ogilvy" wrote in message
...
Why do you do
Set xlApp = New Excel.Application

and open a new copy of excel, then never use it. Nothing references the
xlApp in your with statement. I don't know if it is the cause of the
problem, but it certainly isn't making it faster.

--
Regards,
Tom Ogilvy

"JS" wrote:

Hi All,
I've bumped into an interesting situation. Upon creating and running a
macro
(below) to programmtically change PPT-embedded Excel sheets, it runs
MUCH
slower (~100x slower) than if I run the macro from pure Excel. I can
only
think it has to do with bad Excel referencing (e.g.
support.microsoft.com/kb/178510/EN-US/), or... Also, Task Manager shows
CPU
is ~98% Idle (no Excel CPU!!!) I have a feeling that the Replace method
is
the problem, however I've tried all the tricks I know and nogo.
Thanks os much for your attention and help. Rgds, JS

=============================
Sub EmbeddedExcel_Replace_All_File2()
Dim Shp As Shape
Dim Sld As Slide
Dim xlApp As Excel.Application
Dim oWorkbook As Excel.Workbook
Dim oWorksheet As Excel.Worksheet
Dim SldNum As Long
tStart = Time
Set xlApp = New Excel.Application
For Each Sld In Application.ActivePresentation.Slides
For Each Shp In Sld.Shapes
If Shp.Type = msoEmbeddedOLEObject Then
If Shp.OLEFormat.ProgID = "Excel.Sheet.8" Then
Set oWorkbook = Shp.OLEFormat.Object
Set oWorksheet = oWorkbook.ActiveSheet
With xlApp
Open "C:\Documents and
Settings\Administrator\Desktop\Macros\excel.txt"
For Input As #1
Do While Not EOF(1)
Input #1, sFirst, sLast
oWorksheet.Cells.Replace What:=sFirst, Replacement:=sLast,
LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=True
Loop
Close #1
End With
oWorkbook.Close (True)
Set oWorkbook = Nothing
Set oWorksheet = Nothing
TimeF = Time
End If 'Shp.Type
End If 'Shp.OLEFormat.ProgID
Next Shp
Next Sld
tEnd = Time
MsgBox "Start=" & tStart & " | End=" & tEnd & " | Lap=" &
FormatDateTime(TimeValue(tEnd) - TimeValue(tStart))
xlApp.Quit
End Sub

Embedded Excel macro runs 100X slower

Hi Tom, OK. I've removed from the macro:
'Dim xlApp As Excel.Application
'Set xlApp = New Excel.Application
' With xlApp
' End With
'xlApp.Quit

However, there is no difference in performance - it is still 100x slower
than if I run this in excel (right-click object, Worksheet Object - Edit,
Tools - Macro, run pure Excel macro). Do you have any idea why this is so
slower?
Again, thanks for your attention and time. Rgds, JS

"Tom Ogilvy" wrote in message
...
I don't see any role or need for xlapp in your code. I would just remove
it
and the With statements.

--
Regards,
Tom Ogilvy

"JS" wrote:

Hi Tom, thanks for your reply.
I thought the With xlApp ... End with would do it, but I guess I'm
wrong.
I've tried using
xlApp.oWorksheet.Cells.Replace What:=sFirst..... but this is invalid. Do
you
know I should do this?
Thanks, JS

"Tom Ogilvy" wrote in message
...
Why do you do
Set xlApp = New Excel.Application

and open a new copy of excel, then never use it. Nothing references
the
xlApp in your with statement. I don't know if it is the cause of the
problem, but it certainly isn't making it faster.

--
Regards,
Tom Ogilvy

"JS" wrote:

Hi All,
I've bumped into an interesting situation. Upon creating and running
a
macro
(below) to programmtically change PPT-embedded Excel sheets, it runs
MUCH
slower (~100x slower) than if I run the macro from pure Excel. I can
only
think it has to do with bad Excel referencing (e.g.
support.microsoft.com/kb/178510/EN-US/), or... Also, Task Manager
shows
CPU
is ~98% Idle (no Excel CPU!!!) I have a feeling that the Replace
method
is
the problem, however I've tried all the tricks I know and nogo.
Thanks os much for your attention and help. Rgds, JS

=============================
Sub EmbeddedExcel_Replace_All_File2()
Dim Shp As Shape
Dim Sld As Slide
Dim xlApp As Excel.Application
Dim oWorkbook As Excel.Workbook
Dim oWorksheet As Excel.Worksheet
Dim SldNum As Long
tStart = Time
Set xlApp = New Excel.Application
For Each Sld In Application.ActivePresentation.Slides
For Each Shp In Sld.Shapes
If Shp.Type = msoEmbeddedOLEObject Then
If Shp.OLEFormat.ProgID = "Excel.Sheet.8" Then
Set oWorkbook = Shp.OLEFormat.Object
Set oWorksheet = oWorkbook.ActiveSheet
With xlApp
Open "C:\Documents and
Settings\Administrator\Desktop\Macros\excel.txt"
For Input As #1
Do While Not EOF(1)
Input #1, sFirst, sLast
oWorksheet.Cells.Replace What:=sFirst, Replacement:=sLast,
LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=True
Loop
Close #1
End With
oWorkbook.Close (True)
Set oWorkbook = Nothing
Set oWorksheet = Nothing
TimeF = Time
End If 'Shp.Type
End If 'Shp.OLEFormat.ProgID
Next Shp
Next Sld
tEnd = Time
MsgBox "Start=" & tStart & " | End=" & tEnd & " | Lap=" &
FormatDateTime(TimeValue(tEnd) - TimeValue(tStart))
xlApp.Quit
End Sub

Embedded Excel macro runs 100X slower

Maybe your macro (when run in ppt) is calling across process boundaries (ppt
to Excel) and this is what's adding the performance hit.
Calling across processes involves marshalling and this is a lot slower,
particularly if you make a large number of calls.

Running the same code directly in XL would be expected to be much faster.

--
Tim Williams
Palo Alto, CA

"JS" wrote in message
...
Hi All,
I've bumped into an interesting situation. Upon creating and running a
macro
(below) to programmtically change PPT-embedded Excel sheets, it runs MUCH
slower (~100x slower) than if I run the macro from pure Excel. I can only
think it has to do with bad Excel referencing (e.g.
support.microsoft.com/kb/178510/EN-US/), or... Also, Task Manager shows
CPU
is ~98% Idle (no Excel CPU!!!) I have a feeling that the Replace method is
the problem, however I've tried all the tricks I know and nogo.
Thanks os much for your attention and help. Rgds, JS

=============================
Sub EmbeddedExcel_Replace_All_File2()
Dim Shp As Shape
Dim Sld As Slide
Dim xlApp As Excel.Application
Dim oWorkbook As Excel.Workbook
Dim oWorksheet As Excel.Worksheet
Dim SldNum As Long
tStart = Time
Set xlApp = New Excel.Application
For Each Sld In Application.ActivePresentation.Slides
For Each Shp In Sld.Shapes
If Shp.Type = msoEmbeddedOLEObject Then
If Shp.OLEFormat.ProgID = "Excel.Sheet.8" Then
Set oWorkbook = Shp.OLEFormat.Object
Set oWorksheet = oWorkbook.ActiveSheet
With xlApp
Open "C:\Documents and
Settings\Administrator\Desktop\Macros\excel.txt"
For Input As #1
Do While Not EOF(1)
Input #1, sFirst, sLast
oWorksheet.Cells.Replace What:=sFirst, Replacement:=sLast,
LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=True
Loop
Close #1
End With
oWorkbook.Close (True)
Set oWorkbook = Nothing
Set oWorksheet = Nothing
TimeF = Time
End If 'Shp.Type
End If 'Shp.OLEFormat.ProgID
Next Shp
Next Sld
tEnd = Time
MsgBox "Start=" & tStart & " | End=" & tEnd & " | Lap=" &
FormatDateTime(TimeValue(tEnd) - TimeValue(tStart))
xlApp.Quit
End Sub