CODEWITHSUNDEEP
excelvbapowerpoint
Nils
Nils

Reputation: 930

Insuppressible Error when saving PowerPoint file in Excel VBA

Before this gets marked as a duplicate: It is not, as all the other saving errors seem to get a different MsgBox.

I am writing a macro that opens and closes a PowerPoint Presentation from Excel. Now I have the issue that when I am trying to save the PowerPoint file I get a pop up Message Box:

ErrorMessage

It says: "PowerPoint-Error while saving the file." My code:

Dim pptPres As PowerPoint.Presentation
Dim pptApp As PowerPoint.Application

Set pptApp = New PowerPoint.Application

strPath = "S:\Folderxy\"
strFile = "filename.pptm"
strSave = "newFilename"

Set pptPres = pptApp.Presentations.Open(strPath & strFile, False, True, True)

Application.DisplayAlerts = False


On Error GoTo Errorhandler_tryAgain

tryAgain:
    pptApp.DisplayAlerts = ppAlertsNone
    strSave = "Test123"
    pptPres.SaveAs strPath & strSave & ".pptx"
    pptPres.Close

Exit Sub

Errorhandler_tryAgain:
      Debug.Print "Errorhandler_tryAgain was opened!"
      Application.Wait DateAdd("s", 1, Now) 'delay in seconds
      GoTo TryAgain

First:

Even though I turned the DisplayAlerts off this one keeps popping up. However I can not easily reproduce this error. It occurs sometimes. Openening, closing and saving *.pptx files is part of a loop and surprisingly this error does not reoccur at the same file but it reoccurs about 2 times in a loop with 70 >files.

Second: When I manually click enter the RuntimeError 70: Permission Denied is thrown. But then the VBE goes into the debug mode and my Errorhandler is not handling it. The Errohandler is an infinitive loop as I am saving the file on a server and sometimes it fails to save. However when I manually tried to save the document (both, on the server and on the desktop) I got the same "PowerPoint-Error while saving the file." MsgBox.

Now my question is how do I either get rid of the saving error (which seems to be impossible) or how to surppress that error so that my macro does not stop everytime it occurs. As I would like to run the macro overnight.

In case anyone has experienced such a thing before and can help me out I would be very happy.

Thanks in advance

Upvotes: 2

Views: 630

Answers (2)

Siddharth Rout
Siddharth Rout

Reputation: 149277

Follow these two things and you should be ok...

  1. Mention the File Format while saving. For example pptPres.SaveAs strPath & strSave & ".pptx",24 '<~~ ppSaveAsOpenXMLPresentation. Also ensure the strPath & strSave & ".pptx" is the extact name of the fiel as you wanted it. Else tweak the variables accordingly.

  2. Always add DoEvents after you issue the save(or save as) statement and before the .Close statement so Excel can get enough time to finish it's tasks.

Upvotes: 1

John Korchok
John Korchok

Reputation: 4913

Application.Wait suspends all Excel activity including execution of your macro. It's not so useful for fixing timing problems. When I want to add a little time so that the program can finish I/O or clipboard tasks, Sleep is a better option. First add a declaration:

Public Declare PtrSafe Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As LongPtr)

Add error-trapping before the problem statement, including a statement to reset error handling after the problem:

TryCut1:
  On Error GoTo TooFast1
  objShape.TextFrame2.TextRange.Cut
  On Error GoTo -1

Then add this for error handling. It waits for 10 milliseconds, then tries again:

TooFast1:
  Sleep 10
  Resume TryCut1

Upvotes: 0

Related Questions