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 doesnt do anthing but when i run it with f5 it works great as long as i change the folder path everytime.

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

2024-02-16 15_13_13-Microsoft Excel - TM.jpg

2024-02-16 15_12_52-.jpg

[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!!!
2024-02-17 16_35_47-DOSARE.jpg

[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

2024-03-12 13_27_25-AnyDesk.jpg

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!