Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming,microsoft.public.powerpoint,microsoft.public.office.developer.vba
|
|||
|
|||
Excel to Powerpoint Slow Execution
Hello to all,
My problem is this, I am creating Charts on the fly, sending them to Powerpoint and doing some little formating and then closing and saving powerpoint. I loop through this for 60 graphs. The problem is not that the code is not working but that Excel as problems creating the Powerpoint.application object. It can take up to 30 sec for excel to create the activex component. Here is the code : Dim oPPApp As PowerPoint.Application Dim PPpres As PowerPoint.Presentation Dim pSlide As PowerPoint.Slide Dim sFileName As String Dim iSlide As Integer ' Slide Index AccessTime = Now() On Error GoTo ErrCheck Set oPPApp = CreateObject("Powerpoint.Application") ErrCheck: Set oPPApp = CreateObject("Powerpoint.Application") Resume Next MsgBox Format(Now() - AccessTime, "hh:mm:ss.s") sFileName = (...Some File...) oPPApp.Visible = msoCTrue Set PPpres = oPPApp.Presentations.Open(sFileName) I am looking for a way to make the object creation faster. If anybody can help me, this would be greatly appreciated. Thanks Francis |
#2
Posted to microsoft.public.excel.programming,microsoft.public.powerpoint,microsoft.public.office.developer.vba
|
|||
|
|||
Excel to Powerpoint Slow Execution
I don't know what else is going on, but both your instantiation of the
PP application and your error handling are themselves error-prone. First, you have two back-to-back CreateObject statements. Luckily for you, PP is a single-instance app. If you used the same code with XL you would have any number of instances of XL in memory with no way to terminate them (except through CTRL-ALT-DEL). Second, you execute a 'Resume Next' statement w/o the existence of an error. That, in itself is an error! Create a watch expression for err.number and err.description then step through the code and you will see where the Resume Next line itself generates an error! A much safer way to instantiate the PP application (or any other similar app for that matter) would be: Sub StartOtherApp() Dim ppApp As PowerPoint.Application, IStartedPP As Boolean On Error Resume Next Set ppApp = GetObject(, "powerpoint.application") On Error GoTo 0 If ppApp Is Nothing Then IStartedPP = True Set ppApp = CreateObject("powerpoint.application") End If 'Do my stuff If IStartedPP Then ppApp.Quit End If Set ppApp = Nothing End Sub -- Regards, Tushar Mehta www.tushar-mehta.com Excel, PowerPoint, and VBA add-ins, tutorials Custom MS Office productivity solutions In article , says... Dim oPPApp As PowerPoint.Application Dim PPpres As PowerPoint.Presentation Dim pSlide As PowerPoint.Slide Dim sFileName As String Dim iSlide As Integer ' Slide Index AccessTime = Now() On Error GoTo ErrCheck Set oPPApp = CreateObject("Powerpoint.Application") ErrCheck: Set oPPApp = CreateObject("Powerpoint.Application") Resume Next MsgBox Format(Now() - AccessTime, "hh:mm:ss.s") sFileName = (...Some File...) oPPApp.Visible = msoCTrue Set PPpres = oPPApp.Presentations.Open(sFileName) |
#3
Posted to microsoft.public.excel.programming,microsoft.public.powerpoint,microsoft.public.office.developer.vba
|
|||
|
|||
Excel to Powerpoint Slow Execution
Thank you Tushar Mehta for your explanation. I new there was
something wrong with my instances of Powerpoint. I changed my powerpoint declaration to your method but could still get Error 429 Cannot create activex component. I know that the problem comes from the fact that opening and closing powerpoint in a loop can do this type of problem. To remedy this, I used the sleep method to give the application time to create the object and everything works great. It only takes between 0.5 and 1.2 sec per slide, which is for me excellent speed because of all the formating that I am doing. So here is the sleep function for those who would need it and the update to your code. Private Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long) Dim oPPApp As PowerPoint.Application Dim PPpres As PowerPoint.Presentation Dim pSlide As PowerPoint.Slide Dim IStartedPP As Boolean 'AccessTime = Now() On Error Resume Next Set oPPApp = GetObject(, "Powerpoint.Application") On Error GoTo 0 If oPPApp Is Nothing Then IStartedPP = True Sleep (200) Set oPPApp = CreateObject("Powerpoint.Application") Sleep (200) End If 'MsgBox Format(Now() - AccessTime, "hh:mm:ss.s") Thanks again for your quick reply |
#4
Posted to microsoft.public.excel.programming,microsoft.public.powerpoint,microsoft.public.office.developer.vba
|
|||
|
|||
Excel to Powerpoint Slow Execution
Why create powerpoint every time. Open it once. Do your work multiple
times. Close it once. (if you need to open file in powerpoint, do that, but don't keep creating and closing powerpoint). -- Regards, Tom Ogilvy "Francis de Brienne" wrote in message om... Thank you Tushar Mehta for your explanation. I new there was something wrong with my instances of Powerpoint. I changed my powerpoint declaration to your method but could still get Error 429 Cannot create activex component. I know that the problem comes from the fact that opening and closing powerpoint in a loop can do this type of problem. To remedy this, I used the sleep method to give the application time to create the object and everything works great. It only takes between 0.5 and 1.2 sec per slide, which is for me excellent speed because of all the formating that I am doing. So here is the sleep function for those who would need it and the update to your code. Private Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long) Dim oPPApp As PowerPoint.Application Dim PPpres As PowerPoint.Presentation Dim pSlide As PowerPoint.Slide Dim IStartedPP As Boolean 'AccessTime = Now() On Error Resume Next Set oPPApp = GetObject(, "Powerpoint.Application") On Error GoTo 0 If oPPApp Is Nothing Then IStartedPP = True Sleep (200) Set oPPApp = CreateObject("Powerpoint.Application") Sleep (200) End If 'MsgBox Format(Now() - AccessTime, "hh:mm:ss.s") Thanks again for your quick reply |
#5
Posted to microsoft.public.excel.programming,microsoft.public.powerpoint,microsoft.public.office.developer.vba
|
|||
|
|||
Excel to Powerpoint Slow Execution
1. You are creating the Powerpoint object twice.
2. Create the object outside of the loop. 3. If you are running NAV, disable the NAV Office plug-in, but do NOT disable NAV's AutoProtect. 4. Use With and End With to remove unnecessary references to objects, e.g.: With oPPApp .visible = msoCTrue Set PPpres = .Presentation.Open(sFileName) End With 5. Likely, there are other code improvements possible in the full code. -- http://www.standards.com/; See Howard Kaikow's web site. "Francis de Brienne" wrote in message om... Hello to all, My problem is this, I am creating Charts on the fly, sending them to Powerpoint and doing some little formating and then closing and saving powerpoint. I loop through this for 60 graphs. The problem is not that the code is not working but that Excel as problems creating the Powerpoint.application object. It can take up to 30 sec for excel to create the activex component. Here is the code : Dim oPPApp As PowerPoint.Application Dim PPpres As PowerPoint.Presentation Dim pSlide As PowerPoint.Slide Dim sFileName As String Dim iSlide As Integer ' Slide Index AccessTime = Now() On Error GoTo ErrCheck Set oPPApp = CreateObject("Powerpoint.Application") ErrCheck: Set oPPApp = CreateObject("Powerpoint.Application") Resume Next MsgBox Format(Now() - AccessTime, "hh:mm:ss.s") sFileName = (...Some File...) oPPApp.Visible = msoCTrue Set PPpres = oPPApp.Presentations.Open(sFileName) I am looking for a way to make the object creation faster. If anybody can help me, this would be greatly appreciated. Thanks Francis |
#6
Posted to microsoft.public.excel.programming,microsoft.public.powerpoint,microsoft.public.office.developer.vba
|
|||
|
|||
Excel to Powerpoint Slow Execution
Create the Powerpoint object only once, outside of the loop.
-- http://www.standards.com/; See Howard Kaikow's web site. "Francis de Brienne" wrote in message om... Thank you Tushar Mehta for your explanation. I new there was something wrong with my instances of Powerpoint. I changed my powerpoint declaration to your method but could still get Error 429 Cannot create activex component. I know that the problem comes from the fact that opening and closing powerpoint in a loop can do this type of problem. To remedy this, I used the sleep method to give the application time to create the object and everything works great. It only takes between 0.5 and 1.2 sec per slide, which is for me excellent speed because of all the formating that I am doing. So here is the sleep function for those who would need it and the update to your code. Private Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long) Dim oPPApp As PowerPoint.Application Dim PPpres As PowerPoint.Presentation Dim pSlide As PowerPoint.Slide Dim IStartedPP As Boolean 'AccessTime = Now() On Error Resume Next Set oPPApp = GetObject(, "Powerpoint.Application") On Error GoTo 0 If oPPApp Is Nothing Then IStartedPP = True Sleep (200) Set oPPApp = CreateObject("Powerpoint.Application") Sleep (200) End If 'MsgBox Format(Now() - AccessTime, "hh:mm:ss.s") Thanks again for your quick reply |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Help - VBA Code execution in Excel | New Users to Excel | |||
Slow Excel Navigation with Up / Down Arrow and slow scrolling | Excel Discussion (Misc queries) | |||
Extremely Slow VBA Execution Speed | Excel Programming | |||
slow macro execution | Excel Programming |