Create BAT file that opens CMD then directory and runs program from that directory

[mihailovici]

Hi guys
Do you have any idea how to make a bat file that does the following ?

1. Opens CMD
2. Once in CMD opens the directory: C:\MIHAI\DOC\ASIG\DOSARE
3. Once in that directory runs the command: pdftk.exe *.pdf cat output combined.pdf
4. Exits CMD

Any help would be greatly appreciated.
Thanks.

[Logit]

Untested :


Sub CreateBatchFile()
Dim filePath As String
Dim fileNumber As Integer
Dim batchCommands As String
filePath = "C:\PathToYourFolder\CombinePDFs.bat"
fileNumber = FreeFile
batchCommands = "cd /d C:\MIHAI\DOC\ASIG\DOSARE" & vbCrLf & _
"pdftk.exe *.pdf cat output combined.pdf" & vbCrLf & "exit"
Open filePath For Output As #fileNumber
Print #fileNumber, batchCommands
Close #fileNumber
Shell filePath, vbNormalFocus
End Sub

[arnelgp]

copy CombinePDF function to a Module:


' arnelgp
' do not Alter this subroutine!

Public Sub CombinePDF(ByVal sPath As String, sOutFile As String)
sPath is the path (folder) where your all pdfs exists
sOutfile is the name of your output pdf (example, "Output.pdf")
Dim cmd As String
sPath = Replace$(sPath & "\", "\\", "\")
If Len(Dir$(sPath & sOutFile)) Then Kill sPath & sOutFile
cmd = "pdftk.exe " & sPath & "*.pdf cat output " & sPath & sOutFile
Shell cmd, vbHide
End Sub

' this is the test sub to test CombinePDF() function


Private Sub t()
Dim sPath As String
sPath = "C:\MIHAI\DOC\ASIG\DOSARE"
'call the combine pdf routine
Call CombinePDF(sPath, "Output.pdf")
End Sub

[mihailovici]

Hi ArnelGP and thanks a lot
Your code works for me with a small issue
after i run it once i have to change the directory


sPath = "C:\MIHAI\DOC\ASIG\DOSARE"

to


sPath = "C:\MIHAI\DOC\ASIG\DOSARE"

and then again to


sPath = "C:\MIHAI\DOC\ASIG\DOSARE"

and it goes on and on

I mention I use Microsoft Office 2007

Also when i create a button in the ribbon to run the macro it doesn't do anything but when i run it with f5 it works great as long as i change the folder path every time.

You have any idea about how to fix this?
Thanks a lot again
God Bless!

[arnelgp]

you use the Public sub on your ribbon button:

Sub OnActionButton(control As IRibbonControl)
' Callbackname in XML File "onAction"
' Callback for event button click
' Callback fuer Button Click
Select Case control.ID
Case "yourButtonNameHere"
CombinePDF "C:\MIHAI\DOC\ASIG\DOSARE", "Output.pdf"
Case Else
End Select
End Sub

[mihailovici]

Thanks again ArnelGP for taking your time with me

I modified the code and i dont have to switch from
sPath = "C:\MIHAI\DOC\ASIG\DOSARE" to sPath = "C:\MIHAI\DOC\ASIG\DOSARE \"
it works fine everytime i press run f5 but when i make button on ribbon still does nothing




' arnelgp
' do not Alter this subroutine!

Public Sub CombinePDF(ByVal sPath As String, sOutFile As String)
' sPath is the path (folder) where your all pdfs exists
' sOutfile is the name of your output pdf (example, "Output.pdf")
Dim cmd As String
sPath = Replace$(sPath & "\", "\\", "\")
If Len(Dir$(sPath & sOutFile)) Then Kill sPath & sOutFile
cmd = "pdftk.exe " & sPath & "*.pdf cat output " & sPath & sOutFile
Shell cmd, vbHide
End Sub

' this is the test sub to test CombinePDF() function


Private Sub t()
Dim sPath As String
sPath = "C:\MIHAI\DOC\ASIG\DOSARE"
'call the combine pdf routine
Call CombinePDF(sPath, "Output.pdf")
End Sub

Sub ARNEL()

End Sub


Sub OnActionButton(control As IRibbonControl)
' Callbackname in XML File "onAction"
' Callback for event button click
' Callback fuer Button Click
Select Case control.ID
Case "yourButtonNameHere"
CombinePDF "C:\MIHAI\DOC\ASIG\DOSARE", "Output.pdf"
Case Else
End Select
End Sub

[arnelgp]

do you have the Correct ID for your button?
what you can do is use Debug.Print to show what button name is being pressed:


Sub OnActionButton(control As IRibbonControl)
debug.print control.ID
'rest of code


on immediate window you will see which button was pressed.

[mihailovici]

I still need to change path every time and open the macro and press F5

31357

31358

[arnelgp]

change your macro to:


'!arnelgp
'!do not Alter this subroutine!

Sub ActualCombine(ByVal sPath As String, sOutFile As String)
' sPath is the path (folder) where your all pdfs exists
' sOutfile is the name of your output pdf (example, "Output.pdf")
Dim cmd As String
sPath = Replace$(sPath & "\", "\\", "\")
If Len(Dir$(sPath & sOutFile)) Then Kill sPath & sOutFile
cmd = "pdftk.exe " & sPath & "*.pdf cat output " & sPath & sOutFile
Shell cmd, vbHide
End Sub

' this is the test sub to test CombinePDF() function


Public Sub CombinePDF()
Dim sPath As String
sPath = "C:\MIHAI\DOC\ASIG\DOSARE"
'call the combine pdf routine
Call ActualCombine(sPath, "Output.pdf")
MsgBox "Pdfs combined to Output.pdf"
End Sub



now use CombinePDF to your customized ribbon.

[mihailovici]

Thank you very much!! It works.

Is there a way to delete the initial pdf files ?

[arnelgp]

it is already deleting the old one, see the code:



If Len(Dir$(sPath & sOutFile)) Then Kill sPath & sOutFile

[mihailovici]

Hi!
Sorry, its not deleting the old ones.
The first code you gave me combines all the jpg files into a single pdf file named outpu.pdf
This code then combines all the pdf files including outpu.pdf into a single final pdf file named output.pdf
Anyway this is great and I thank you so much!!!
31359

[arnelgp]

change the subs to this, it will combine then delete the initial pdfs.


Sub ActualCombine(ByVal sPath As String, sOutFile As String)
' sPath is the path (folder) where your all pdfs exists
' sOutfile is the name of your output pdf (example, "Output.pdf")
Dim sFile As String
Dim cmd As String
Dim dict As Object
Dim i As Integer, j As Integer
Set dict = CreateObject("scripting.dictionary")
sPath = Replace$(sPath & "\", "\\", "\")
'put all the .pdfs in the dict object
sFile = Dir$(sPath & "*.pdf")
While Len(sFile) <> 0
dict(sFile) = 1
sFile = Dir$
Wend
j = -1
'check if there is any pdf
If dict.Count <> 0 Then
For i = 0 To dict.Count - 1
'find Output.pdf
'delete it only if there are other pdfs,
'otherwise, leave it
If dict.keys()(i) = sOutFile And dict.Count > 1 Then
Kill sPath & sOutFile
j = i
End If
Next
End If
' if we delete Output.pdf, remove it from the
' pdf list
If j > -1 Then
dict.Remove (dict.keys()(j))
End If
' recheck it again
If dict.Count = 1 And dict.keys()(0) = sOutFile Then
Else
cmd = "pdftk.exe " & sPath & "*.pdf cat output " & sPath & sOutFile
Shell cmd, vbHide
'now delete all pdfs on the list
For i = 0 To dict.Count - 1
Kill sPath & dict.keys()(i)
Next
End If
Set dict = Nothing
End Sub


' this is the test sub to test CombinePDF() function


Public Sub CombinePDF()
Dim sPath As String
sPath = "C:\MIHAI\DOC\ASIG\DOSARE"
'call the combine pdf routine
Call ActualCombine(sPath, "Output.pdf")
MsgBox "Pdfs combined to Output.pdf"
End Sub

[mihailovici]

Thanks again ArnelGP
It deletes all the pdf files including the output.pdf....

[arnelgp]

ok no problem, goodluck.


Public Function ActualCombine(ByVal sPath As String, sOutFile As String) As Integer
'
' sPath is the path (folder) where your all pdfs exists
' sOutfile is the name of your output pdf (example, "Output.pdf")
Dim sFile As String
Dim cmd As String
Dim dict As Object
Dim i As Integer, j As Integer
Set dict = CreateObject("scripting.dictionary")
sPath = Replace$(sPath & "\", "\\", "\")
'put all the .pdfs in the dict object
sFile = Dir$(sPath & "*.pdf")
While Len(sFile) <> 0
dict(sFile) = 1
sFile = Dir$
Wend
j = -1
'check if there is any pdf
If dict.Count <> 0 Then
For i = 0 To dict.Count - 1
'find Output.pdf
'delete it only if there are other pdfs,
'otherwise, leave it
If dict.keys()(i) = sOutFile And dict.Count > 1 Then
Kill sPath & sOutFile
j = i
End If
Next
End If
' if we delete Output.pdf, remove it from the
' pdf list
If j > -1 Then
dict.Remove (dict.keys()(j))
End If
' recheck it again
If (dict.Count = 0) Then
ElseIf (dict.Count = 1 And dict.keys()(0) = sOutFile) Then
Else
cmd = "pdftk.exe " & sPath & "*.pdf cat output " & sPath & sOutFile
Shell cmd, vbHide

'now delete all pdfs on the list
For i = 0 To dict.Count - 1
Kill sPath & dict.keys()(i)
Next
ActualCombine = dict.Count
End If


Set dict = Nothing
End Function




' this is the test sub
' to test CombinePDF() function
Public Sub CombinePDF()
Dim sPath As String
sPath = "C:\MIHAI\DOC\ASIG\DOSARE"
'sPath = Environ$("userprofile") & "\documents\"
'call the combine pdf routine
If ActualCombine(sPath, "Output.pdf") <> 0 Then
MsgBox "Pdfs combined to Output.pdf"
Else
MsgBox "No new pdf have been combined"
End If
End Sub

[mihailovici]

Ty ArnelGP

i got it

i modified count from -1 to -2
and it works now


Public Function ActualCombine(ByVal sPath As String, sOutFile As String) As Integer'
' sPath is the path (folder) where your all pdfs exists
' sOutfile is the name of your output pdf (example, "Output.pdf")
Dim sFile As String
Dim cmd As String
Dim dict As Object
Dim i As Integer, j As Integer
Set dict = CreateObject("scripting.dictionary")
sPath = Replace$(sPath & "\", "\\", "\")
'put all the .pdfs in the dict object
sFile = Dir$(sPath & "*.pdf")
MsgBox sFile
While Len(sFile) <> 0
dict(sFile) = 1
sFile = Dir$
Wend
j = -1
'check if there is any pdf
If dict.Count <> 0 Then
For i = 0 To dict.Count - 2
'find Output.pdf
'delete it only if there are other pdfs,
'otherwise, leave it
If dict.keys()(i) = sOutFile And dict.Count > 1 Then
Kill sPath & sOutFile
j = i
End If
Next
End If
' if we delete Output.pdf, remove it from the
' pdf list
If j > -1 Then
dict.Remove (dict.keys()(j))
End If
' recheck it again
If (dict.Count = 0) Then
ElseIf (dict.Count = 1 And dict.keys()(0) = sOutFile) Then
Else
cmd = "pdftk.exe " & sPath & "*.pdf cat output " & sPath & sOutFile
Shell cmd, vbHide
'now delete all pdfs on the list
For i = 0 To dict.Count - 2
Kill sPath & dict.keys()(i)
Next
ActualCombine = dict.Count
End If
Set dict = Nothing
End Function


' this is the test sub to test CombinePDF() function


Public Sub CombinePDF()
Dim sPath As String
sPath = "C:\MIHAI\DOC\ASIG\DOSARE\"
'sPath = Environ$("userprofile") & "\documents\"
'call the combine pdf routine
If ActualCombine(sPath, "Output.pdf") <> 0 Then
MsgBox "Pdfs combined to Output.pdf"
Else
MsgBox "No new pdf have been combined"
End If
End Sub

[mihailovici]

Hi Arnel,

I had to move my macros on a computer at work that uses Windows 7 and Excel 2007

When i use your macro to convert more pdfs into 1 i get the error:
Run time error 53
File not found

31406

Any idea what am I doing wrong?
Thanks a lot!

[arnelgp]

google what is runtime error 53, that is the hint and you will solved this if you know what is the hint.

[mihailovici]

Thanks Arnel
Searched and it seems i installed the wrong version of ImageMagick and the installation path was not the same.
It works now.
:)
God Bless!