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

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
>
>
>

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" <TomOgilvy@discussions.microsoft.com> wrote in message
news:244F1E0A-546A-4383-AD05-1DB9F71633AC@microsoft.com...
> 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
> >
> >
> >

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" <TomOgilvy@discussions.microsoft.com> wrote in message
> news:244F1E0A-546A-4383-AD05-1DB9F71633AC@microsoft.com...
> > 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
> > >
> > >
> > >
>
>
>

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" <TomOgilvy@discussions.microsoft.com> wrote in message
news:BF78DCA4-FDCB-4F18-9465-44A9626E17F9@microsoft.com...
> 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" <TomOgilvy@discussions.microsoft.com> wrote in message
> > news:244F1E0A-546A-4383-AD05-1DB9F71633AC@microsoft.com...
> > > 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
> > > >
> > > >
> > > >
> >
> >
> >

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" <jfs@amcham.com.br> wrote in message
news:eSzVsO0TGHA.736@TK2MSFTNGP12.phx.gbl...
> 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
>
>