How do I create a .bat file for each row

[ktmman]

This is my first time with VBA so please bear with me

I am trying to open an mp4 file with potplayer with an command line to open at specific time in excel. There are multiple folders with hundreds of video files and I would like to open an .mp4 file at specific position. I figured the easiest solution to this would be to make a .bat file for each entry.

The command line in potplayer is

PotPlayerMini64.exe ["content"] [/switch]

so the following command will open "video.mp4" at 14 hours, 2 minutes and 6 seconds

PotPlayerMini64.exe "C:\Video output\2022-11-12\video.mp4" /seek=14:02:06

The following .bat file works perfectly

start C:\"Program Files"\DAUM\PotPlayer\PotPlayerMini64.exe "\\192.168.8.223\Vol\"Video output"\2022-11-12\2022-12-14 02-10-39.mp4" /seek=14:2:6

I have figured out how to generate a text file but there are so many variables that I can't figure out how to piece this thing together. The ideal process is to manually make the bat file after I've filled all the cells for that row and repeat the process for the second row.

 

[Legacy 143009]

This should work for you:

Option Explicit
Sub CreateAfile()
    Dim lRow As Long, i As Long
    lRow = Cells(Rows.Count, 4).End(xlUp).Row
    Dim filePath As String, fileName As String, fileExt As String, seekHour As String, seekMin As String, seekSec As String, outputPath As String
    Dim fs As Object, a As Object
   
    For i = 2 To lRow
      filePath = Cells(i, 3).Value
      fileName = Left(Cells(i, 4).Value, InStr(Cells(i, 4).Value, ".") - 1)
      fileExt = Right(Cells(i, 4).Value, InStr(Cells(i, 4).Value, ".") - 1)
      seekHour = Cells(i, 7).Value
      seekMin = Cells(i, 8).Value
      seekSec = Cells(i, 9).Value
      outputPath = Cells(i, 11).Value

      Set fs = CreateObject("Scripting.FileSystemObject")
      Set a = fs.CreateTextFile(outputPath & "\" & fileName & "x" & seekHour & "-" & seekMin & "-" & seekSec & ".bat", True)
      a.WriteLine ("start C:\Program Files\DAUM\PotPlayer\PotPlayerMini64.exe """ & filePath & "\" & fileName & fileExt & """ /seek=" & seekHour & ":" & seekMin & ":" & seekSec)
      a.Close
    Next
End Sub

 

[Legacy 143009]

A small adjustment to get more accurate file name:

Option Explicit
Sub CreateAfile()
    Dim lRow As Long, i As Long
    Dim filePath As String, fileName As String, fileExt As String, seekHour As String, seekMin As String, seekSec As String, outputPath As String
    Dim fs As Object, a As Object

    Set fs = CreateObject("Scripting.FileSystemObject")
    lRow = Cells(Rows.Count, 4).End(xlUp).Row

    For i = 2 To lRow
      filePath = Cells(i, 3).Value
      fileName = Left(Cells(i, 4).Value, InStrRev(Cells(i, 4).Value, ".") - 1)
      fileExt = Right(Cells(i, 4).Value, InStrRev(Cells(i, 4).Value, ".") - 1)
      seekHour = Cells(i, 7).Value
      seekMin = Cells(i, 8).Value
      seekSec = Cells(i, 9).Value
      outputPath = Cells(i, 11).Value

      Set a = fs.CreateTextFile(outputPath & "\" & fileName & "x" & seekHour & "-" & seekMin & "-" & seekSec & ".bat", True)
      a.WriteLine ("start C:\Program Files\DAUM\PotPlayer\PotPlayerMini64.exe """ & filePath & "\" & fileName & fileExt & """ /seek=" & seekHour & ":" & seekMin & ":" & seekSec)
      a.Close
    Next
End Sub

 

[ktmman]

Hi, thank you very much, it worked. For some reason .bat files don't understand spaces in location ("C:\Program Files\...") so I just moved the potplayer install to someplace else. Same thing with the outputPath and filePath. For some reason also the fileExt would put out the name of the file instead of the file extension. Thanks again, I can figure out the rest.

Option Explicit
Sub CreateAfile()
Dim lRow As Long, i As Long
Dim filePath As String, fileName As String, fileExt As String, seekHour As String, seekMin As String, seekSec As String, outputPath As String
Dim fs As Object, a As Object

Set fs = CreateObject("Scripting.FileSystemObject")
lRow = Cells(Rows.Count, 4).End(xlUp).Row

For i = 2 To lRow
filePath = Cells(i, 3).Value
fileName = Left(Cells(i, 4).Value, InStrRev(Cells(i, 4).Value, ".") - 1)
fileExt = Right(Cells(i, 4).Value, InStrRev(Cells(i, 4).Value, ".") - 1)
seekHour = Cells(i, 7).Value
seekMin = Cells(i, 8).Value
seekSec = Cells(i, 9).Value
outputPath = Cells(i, 11).Value

Set a = fs.CreateTextFile(outputPath & "\" & fileName & "x" & seekHour & "-" & seekMin & "-" & seekSec & ".bat", True)
a.WriteLine ("start C:\DAUM\PotPlayer\PotPlayerMini64.exe """ & filePath & "\" & fileName & ".mp4" & """ /seek=" & seekHour & ":" & seekMin & ":" & seekSec)
a.Close
Next
End Sub

 

[Legacy 143009]

Hi, thank you very much, it worked. For some reason .bat files don't understand spaces in location ("C:\Program Files\...") so I just moved the potplayer install to someplace else. Same thing with the outputPath and filePath.

Oh that's why you enclosed them between quotation marks. Maybe you should adjust your strings according that. For instance, double quotation mark means single quotation in vba. Ex: a.WriteLine ("start C:\""Program Files""\DAUM..

For some reason also the fileExt would put out the name of the file instead of the file extension. Thanks again, I can figure out the rest.

Thats weird. It finds the last "." and takes piece from the right.

 

[Legacy 143009]

Ok for extension use instead:

fileExt = Mid(Cells(i, 4).Value, InStrRev(Cells(i, 4).Value, "."), Len(Cells(i, 4).Value))