1

I have a program in an Excel workbook that opens a Powerpoint-File, updates the links within this file and closes it after that. This works fine. Here is my problem: When the links are updated an Excel file with the source data is opened. After Powerpoint is closed this file stays open. I want it to get closed because I repeat this process for many files and I can't end up with hundreds of open Excel files.

I tried the following:

WBKs=Application.Workbooks.count
For i = WBKs to 1 Step -1
    If Workbooks(i).Name<>ThisWorkbook.Name then
        Workbooks(i).close savechanges:=False
    End if
Next i

Now comes the weird part. Whenever I just run my code, WBKs always returns 1 and the Excel file only pops up after the code is finished. If I go through my code in debug mode it works. The workbook pops up as soon as I enter debug mode.

I tried Applicatio.Wait in the hope that the file would show after a second. The file only showed after the code was finished.

I tried a Do While Loop to wait until the file is open. Excel crashes because I never leave the loop.

Edit: I tried DoEvents as suggested. Does not work either.

Community
  • 1
  • 1
user3480701
  • 85
  • 1
  • 2
  • 8
  • 2
    `DoEvents` after closing the powerpoint file. – GSerg Mar 17 '15 at 09:05
  • "When the links are updated an Excel file with the source data is opened." - are you opening this file with VBA code? Or it's automatic? – kolcinx Mar 17 '15 at 09:19
  • It is opened automatically. – user3480701 Mar 17 '15 at 09:51
  • If DoEvents does not work, even when used several times in the right place, then have a separate cleanup macro and shedule its execution a second after the main macro with `Application.OnTime`. – GSerg Mar 17 '15 at 10:57
  • That does the trick! Thanks a lot. However now another problem was created: The file that is opened contains macros. Using `Application.DisplayAlerts=False` I originally surpressed the message that thesse macros might be dangerous. Now this obciously does not work anymore. I try to work around it. Help is appreciated. – user3480701 Mar 17 '15 at 12:45

1 Answers1

-1

This is just a workaround, but try using a brute force after x times your macro has run. Store that x somewhere in workbook, save. And kill excel process (all instances, including self) :

Dim sKill As String

sKill = "TASKKILL /F /IM excel.exe"
Shell sKill, vbHide

Found here : VBA script to close every instance of Excel except itself

When running your macro next time, you will use that x as a starting point for next PPT file to update.

Community
  • 1
  • 1
kolcinx
  • 2,183
  • 1
  • 15
  • 38
  • Thanks for your answer. Unfortunatly the original Excel file has to stay open. I am not the end-user of the tool, thus it has to be user friendly. – user3480701 Mar 17 '15 at 11:25