J
JS
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'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