Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Run command line program in excel vba

Posted on 2012-04-08
6
Medium Priority
?
3,704 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
Veeam and MySQL: How to Perform Backup & Recovery

MySQL and the MariaDB variant are among the most used databases in Linux environments, and many critical applications support their data on them. Watch this recorded webinar to find out how Veeam Backup & Replication allows you to get consistent backups of MySQL databases.

 
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

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Question has a verified solution.

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

If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
After seeing numerous questions for Dynamic Data Validation I notice that most have used Visual Basic to solve the problem. This suggestion is purely formula based and can be used in multiple rows.
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …

886 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