Make Excel VBA wait until operation is finished

debugcn Published at Dev

Wannes Demaeght

I have made a macro in VBA to split PDF files into single pages.

Sub SplitFiles()

'This needs PDFTK installed

Dim fso As New FileSystemObject
Dim fol As Folder
Dim fil As File
Dim i As Integer
Dim command As String

On Error Resume Next

Set fol = fso.GetFolder("C:\Users\wanne\OneDrive\Documents\Bijberoep\Scans\Splitsen\")

i = 1

For Each fil In fol.Files
    command = ("C:\Program Files (x86)\PDFtk\bin\pdftk.exe " & fil & " burst output C:\Users\wanne\OneDrive\Documents\Bijberoep\Scans\Klaar\Klaar" & i & "-%d.pdf")
    Shell command
    i = i + 1
Next fil

Application.Wait (Now + TimeValue("0:00:05"))

Kill "C:\Users\wanne\OneDrive\Documents\Bijberoep\Scans\Splitsen\*.pdf"
Kill "C:\Users\wanne\OneDrive\Documents\Bijberoep\Scans\Klaar\*.txt"

End Sub

The problem is that I had to give a timeout of 5 seconds, or else the files get deleted immediately, and no output is created.

How can I make excel wait until all files are processed?

Wannes Demaeght

Thanks Nathan, you've put me on the right path:

Sub SplitFiles()

'This needs PDFTK installed

Dim fso As New FileSystemObject
Dim fol As Folder
Dim fil As File
Dim i As Integer
Dim command As String
Dim number As Long

On Error Resume Next

Set fol = fso.GetFolder("C:\Users\wanne\OneDrive\Documents\Bijberoep\Scans\Splitsen\")
number = fol.Files.Count

i = 1
For Each fil In fol.Files
    command = ("C:\Program Files (x86)\PDFtk\bin\pdftk.exe " & fil & " burst output C:\Users\wanne\OneDrive\Documents\Bijberoep\Scans\Klaar\Klaar" & i & "-%d.pdf")
    Shell command
    If i = number Then
        Application.Wait (Now + TimeValue("0:00:02"))
        Kill "C:\Users\wanne\OneDrive\Documents\Bijberoep\Scans\Splitsen\*.*"
        Kill "C:\Users\wanne\OneDrive\Documents\Bijberoep\Scans\Klaar\*.txt"
        Shell ("C:\Program Files (x86)\View and Rename PDF\viewAndRename.exe")
    Else
    i = i + 1
    End If
Next fil

End Sub

This code works, but I hade to declare a variable as long first, or it would get stuck in a loop.

Still a timeout of 2 seconds, because not all files got deleted without the delay (probably because they were still being processed by pdftk)

Collected from the Internet

Please contact [email protected] to delete if infringement.