# How do I create a .bat file for each row



## ktmman (Dec 29, 2022)

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.


----------



## Flashbond (Dec 29, 2022)

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


----------



## Flashbond (Dec 29, 2022)

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 (Dec 29, 2022)

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


----------



## Flashbond (Dec 29, 2022)

ktmman said:


> 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..`


ktmman said:


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


----------



## Flashbond (Dec 30, 2022)

Ok for extension use instead:

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


----------

