Improve company productivity with a Business Account.Sign Up

x
?
Solved

Run command line program in excel vba

Posted on 2012-04-08
6
Medium Priority
?
3,962 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 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
What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

 
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 2000 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

What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Here is why.
This following write-up describes a different way to copy Lotus Notes Calendar to Outlook. Along with this, we will also learn the reason behind this NSF to PST migration. Users can prefer different procedures as per their convenience.
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…
A query can call a function, and a function can call Excel, even though we are in Access. This is Part 2, and steps you through the VBA that "wraps" Excel functionality so we can use its worksheet functions in Access. The declaration statement de…

579 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