Solved

Run command line program in excel vba

Posted on 2012-04-08
6
3,467 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
Independent Software Vendors: 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

Enroll in May's Course of the Month

May’s Course of the Month is now available! Experts Exchange’s Premium Members and Team Accounts have access to a complimentary course each month as part of their membership—an extra way to increase training and boost professional development.

Question has a verified solution.

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

Suggested Solutions

Phishing attempts can come in all forms, shapes and sizes. No matter how familiar you think you are with them, always remember to take extra precaution when opening an email with attachments or links.
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
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…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

734 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