Solved

Run command line program in excel vba

Posted on 2012-04-08
6
3,251 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
  • 3
  • 2
6 Comments
 
LVL 2

Author Comment

by:stopher2475
ID: 37821520
0
 
LVL 17

Expert Comment

by:Anuroopsundd
ID: 37821521
0
 
LVL 41

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
What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

 
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 41

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

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

In a previous article published here at Experts Exchange, Signature Image with Transparent Background (http://www.experts-exchange.com/Web_Development/Document_Imaging/A_12380-Signature-Image-with-Transparent-Background.html), I explained how to cre…
The Adobe PDF proprietary file format is recognized as secure and formulated. But these PDF files are also prone to corruption and any external threat like virus attacks, improper storage can hit PDF file integrity.This type of damages can make cruc…
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
Sometimes we receive PDF files that are in the wrong orientation. They may be sideways or even upside down. This most commonly happens with scanned or faxed documents. It is possible to rotate the view of these PDFs with the free Adobe Reader produc…

746 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now