Solved: Call CMD.EXE and send instructions

[D_Marcel]

Hello Gurus,

I'm developing a code to send a .PDF file to each row in a Worksheet, but before perform the sub that calls the Outlook, I have to break 4 .PDF files that have multiple pages, each one in a different subfolder of a folder. I downloaded the application PDFTK and my idea is call cmd.exe from the VBA editor and send this command:

pdftk myfile.pdf burst

This instruction works fine when I call cmd manually, but for a reason that I don't know, I always have to perform the instruction in the same folder of the file.

For example, I call CMD, this is the prompt:

C:\Users\D_Marcel>

Then I have to perform this first:

cd Desktop\Pages\CTN\

I can send through a For Each Next the path of subfolders, but how can I send the commands? I spent much hours researching and testing, but nothing.... :dunno

Can anyone please please help me?
Thanks in advance,

[Kenneth Hobs]

You don't need cmd. Shell() will suffice. There are other Shell methods as well. If you want to use multiply DOS commands, then write the BAT file as a text file and then Shell() to it. Your BAT files can have input parameters like %1, %2, but writing as a text file, you don't need that.

To get around the need for cd, put the full drive/Path to the file to pass to pdftk.

Of course if you need the path to your Desktop or other environmental folders, we can get those without hard coding the paths.

Don't forget to use quotes to encapsulate the drive:\path\filenames if they have space characters.

For example, using pdfsam:
Sub Test()
Dim pdfsam As String, pdfFiles As String, pdfsamStr As String
Dim q As String
q = """"
pdfsam = "cmd /c java -jar " & q & _
"C:\Documents and Settings\Nashl\Desktop\pdfsam-1.0.3-out\lib\" & _
"pdfsam-console-1.1.5e.jar" & q
pdfFiles = "-f F:\Temp\temp.pdf"
pdfOut = "-o F:\Temp\"
pdfsamStr = pdfsam & " -p 1 " & pdfFiles & " " & pdfOut & " -s SPLIT"
Debug.Print pdfsamStr
Shell pdfsamStr, vbNormalFocus
End Sub
Here is another code snippet that I have posted here before.
'Using pdftk at: http://www.pdflabs.com/docs/install-pdftk/
'I have not tested this but the vba command might be something like:
Shell "u:\Material\Utility\pdftk\pdftk.exe X:\Book1.pdf owner_pw Hobs user_pw ken output X:\Book1_Secure.pdf", vbHide

[D_Marcel]

Hello Kenneth, thanks a lot for your response. After a few attempts, I did some tests and got the code below, it works well, but only to one of the four .PDF files:

Sub Stage_1()

Dim WorkArea as Range
Dim pdftkScript As String

Set Stage1 = Worksheets("Cockpit")
Set WorkArea = Stage1.Range(Cells(6, 1), Cells(Cells(Rows.Count, 1).End(xlUp).Row, 1))

For Each Entry In WorkArea
'Disconsider the header line
If Entry.Value <> "Nome do Arquivo" Then
EntryLenght = Len(Entry)
Company = Left(Right(Entry.Value, EntryLenght - 14), EntryLenght - 18)
pdftkScript = "pdftk " & TargetPath & "\" & Company & "\" & "Geral.pdf" & " burst output " _
& TargetPath & "\" & Company & "\" & "pagina_%01d.pdf"
Shell pdftkScript
End If
Next Entry

End Sub


In the first loop of the For Each Next, the .PDF file is burst sucessfully, but to the others three folders nothing happens. I tried also to use your code to PDFSam, just changing the name of the file in the subfolder lib, but nothing.

Am I doing something wrong?

[D_Marcel]

.

[Kenneth Hobs]

Is it overwriting the output file where the one file is the last file? Use, Debug.Print pdftkScript, after you assign the value so that you can see what the string is doing. If needed, copy that string in a CMD window four times and I think you will see the problem.

[D_Marcel]

Ha!! The Debug.Print was very useful, I realized that the difference between them was the spaces, then I did a test, replacing the " " for "_", and it worked! I added the Chr(34) in the String, now it's everything working very well:

For Each Entry In WorkArea
If Entry.Value <> "Nome do Arquivo" Then
EntryLenght = Len(Entry)
Company = Left(Right(Entry.Value, EntryLenght - 14), EntryLenght - 18)
pdftkScript = "pdftk " & Chr(34) & TargetPath & "\" & Company & "\" & "001 - Geral.pdf" & Chr(34) & " burst output " _
& Chr(34) & TargetPath & "\" & Company & "\" & "pagina_%01d.pdf" & Chr(34)
Debug.Print pdftkScript
Shell pdftkScript
End If
Next Entry

Thanks a lot Kenneth! Thread solved.