Solved

Run command line program in excel vba

Posted on 2012-04-08
6
3,547 Views
Last Modified: 2012-04-08
I'm trying to use a command line tool called pdf toolkit to merge files in VBA.
I've successfully run it in a command window so I thought it would be easy to call a shell to run it in vba.
Sub MergePDF()
    Dim sDir As String
    Dim sFileA As String
    Dim sFileB As String
    Dim sOutput As String

    sDir = Range("B2")
    sFileA = Range("B3")
    sFileB = Range("B4")
    sOutput = Range("B5")
    
    Open sDir & "\" & "temp.bat" For Output As #1
    Print #1, "cd " & sDir
    Print #1, "pdftk " & sFileA & " " & sFileB & " cat output " & sOutput
    Close #1
    Sleep 5000
    Shell sDir & "\" & "temp.bat"

End Sub

Open in new window


This runs without error but does not create the merge file. If I double click the temp.cmd file generated it successfully creates the merge file. So the syntax of the file should be ok. Can anyone give me a suggestion?
0
Comment
Question by:stopher2475
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
6 Comments
 
LVL 2

Author Comment

by:stopher2475
ID: 37821520
0
 
LVL 17

Expert Comment

by:Anuroopsundd
ID: 37821521
0
 
LVL 42

Expert Comment

by:dlmille
ID: 37821715
There's nothing innately wrong with your shell command, except that perhaps the shell is terminating before your pdftk program has a chance to complete, OR running from VBA it cannot find the path to the pdftk program.

I would suggest trying this - note the two lines running the .bat file - one with and one without cmd /c.  cmd /c may also be needed.  I've also seen success with just a short sleep after the command line - so this is a bit of a shot-gun approach to try it all at once, and you can pare back the Sleep, then the CMD/C to see what was needed to ensure it worked properly.

Sub MergePDF()
    Dim sDir As String
    Dim sFileA As String
    Dim sFileB As String
    Dim sOutput As String

    sDir = Range("B2")
    sFileA = Range("B3")
    sFileB = Range("B4")
    sOutput = Range("B5")
    
    Open sDir & "\" & "temp.bat" For Output As #1
    Print #1, "cd " & sDir
    Print #1, "c:\fullpath\pdftk.exe " & sFileA & " " & sFileB & " cat output " & sOutput 'it may need the full path to the .exe
    Close #1
    Sleep 5000
    Shell "cmd /c " & sDir & "\" & "temp.bat" 'or comment this out and try un-commenting the next line
    'Shell sDir & "\" & "temp.bat"
    Sleep 5000

End Sub

Open in new window


Dave
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 2

Author Comment

by:stopher2475
ID: 37821886
I couldn't get either of those to work so I did a test using
Call Shell(Environ("comspec") & " /k ""C:\test\temp.bat""", 1)

Open in new window

So that the DOS window would stay open. I got the error message:
c:\Windows\system32\pdftk.exe is not recognized as an internal or external command, operable program or batch file.
I was able to get it to run the merge correctly by copying the two files "libiconv2.dll" and "pdftk.exe" to the directory and updating the batch file to use that executable. The post sleep command is also necessary for it to work correctly. Is there something I need to to on the machine to register the toolkit?
0
 
LVL 42

Accepted Solution

by:
dlmille earned 500 total points
ID: 37822132
I was wondering whether a /k would be in order for you to just see what happened in the shell command.

I downloaded the pdftk.exe file (sorry, I didn't recognize that was YOUR post as the second post).  I copied the files to my C:\WINDOWS\SYSTEM32 directory.

Using the below code  modification, it worked just fine - no sleep necessary before or after (for me, anyway) - it ran (on a 40 page + 40 page PDF merge) without any command switches, either...

Option Explicit
Private Declare Sub sapiSleep Lib "kernel32" _
        Alias "Sleep" _
        (ByVal dwMilliseconds As Long)

Sub Sleep(lngMilliSec As Long)
    If lngMilliSec > 0 Then
        Call sapiSleep(lngMilliSec)
    End If
End Sub
Sub MergePDF()
    Dim sDir As String
    Dim sFileA As String
    Dim sFileB As String
    Dim sOutput As String

    sDir = Range("B2")
    sFileA = Range("B3")
    sFileB = Range("B4")
    sOutput = Range("B5")
    
    Open sDir & "\" & "temp.bat" For Output As #1
    Print #1, "cd " & sDir
    Print #1, "c:\windows\system32\pdftk " & sFileA & " " & sFileB & " cat output " & sOutput
    Close #1
    'Sleep 5000
    
    'Shell "cmd /c " & sDir & "\" & "temp.bat"
    
    Shell sDir & "\" & "temp.bat"
    
    'Sleep 5000
End Sub

Open in new window


So, back to my original comment - there appears to be nothing innately wrong with your command, though I've seen instances where using SHELL CMD /C helps with shelling out to Acrobat, for example.

------------
If you're getting the error:

c:\Windows\system32\pdftk.exe is not recognized as an internal or external command, operable program or batch file.

Perhaps, you have mis-typed something in the command line (upload your .bat text so I can see it), or you didn't copy the pdftk.exe to the c:\windows\system32 directory (check and see if you can find it and the .dll file there).

See attached which I'm using and it works right now.

Dave
testpdftk-r1.xls
0
 
LVL 2

Author Comment

by:stopher2475
ID: 37822349
I tried with your code. It's not working for me until I change the C:\WINDOWS\SYSTEM32 directory to point it to the copy of the files I put in the application directory. Like I said I can click on the batch file after it's created and it makes the merge file. It just can't see the pdftk from the vba shell object. I have to leave the sleep in as well to get it to run. I think it must be a security issue. Maybe it's a UAC thing.
Thanks for the help. The toolkit is small so I'm ok with copying it to the application directory if that gets it to run reliably.
0

Featured Post

[Live Webinar] The Cloud Skills Gap

As Cloud technologies come of age, business leaders grapple with the impact it has on their team's skills and the gap associated with the use of a cloud platform.

Join experts from 451 Research and Concerto Cloud Services on July 27th where we will examine fact and fiction.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
Code that checks the QuickBooks schema table for non-updateable fields and then disables those controls on a form so users don't try to update them.
In this fifth video of the Xpdf series, we discuss and demonstrate the PDFdetach utility, which is able to list and, more importantly, extract attachments that are embedded in PDF files. It does this via a command line interface, making it suitable …
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

623 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question