Using MS Excel to manage multiple .my files

[roxy99]

Anyone working with muliple my files for numerous episodes knows how tedious it can be to update and manage them.

Here is an easy way to use excel.

Step 1:

Create an Excel document and on Sheet1, Row 1: Fill out the following headings in row 1:

A1 : Old mpg file name
B1: Rename mpg to
C1 to ?1: valid My tag names; eg: Title, Releasedate, Actors, Synopsis


Step 2:

Create two command buttons and link the following VB script

Private Sub CmdListMpg_Click()
directory = ThisWorkbook.Path
With Application.FileSearch
.NewSearch
.LookIn = directory
.Filename = "*.mpg"
.SearchSubFolders = False
.Execute
For i = 1 To .FoundFiles.Count
Cells(i + 1, 1) = Left(Right(.FoundFiles(i), Len(.FoundFiles(i)) - Len(directory) - 1) _
, Len(Right(.FoundFiles(i), Len(.FoundFiles(i)) - Len(directory) - 1)) - 4)
Next i
End With

End Sub

Private Sub CmdCreateMy_Click()
Set data = Sheets("Sheet1").Range("a1")
records = Application.CountA(Sheets("Sheet1").Range("A:A")) - 1
For i = 1 To Application.CountA(Sheets("Sheet1").Range("A:A")) - 1
    Application.StatusBar = "Processing Record " & i
        Name ThisWorkbook.Path & "\" & data.Offset(i, 0).Value & ".mpg" As ThisWorkbook.Path & "\" & data.Offset(i, 1).Value & ".mpg"
    Open ThisWorkbook.Path & "\" & data.Offset(i, 1).Value & ".my" For Output As #1
    Print #1, data.Offset(0, 2).Value & "=" & data.Offset(i, 2).Value
    Print #1, data.Offset(0, 3).Value & "=" & data.Offset(i, 3).Value
    Print #1, data.Offset(0, 4).Value & "=" & data.Offset(i, 4).Value
    Print #1, data.Offset(0, 5).Value & "=" & data.Offset(i, 5).Value
    Print #1, data.Offset(0, 6).Value & "=" & data.Offset(i, 6).Value
    Print #1, data.Offset(0, 7).Value & "=" & data.Offset(i, 7).Value
       
    Close #1
Next i
      
Application.StatusBar = ""
MsgBox records & " My files were created and saved in " & ThisWorkbook.Path

End Sub

Step 3:

Save the excel file into the directory where the mpg files are present where you want to create the my files.

Step 4
Click 'List MPG' button -->column A is filled up with all the the mpg file names (minus the .mpg extension

Step 5:

Give new names if you like in column B or just copy the same name over.

Step 6:

Fill out the rest of the table with the show data

Step 7:

Press the 'Generate my files' button.


PS- I've attached a sample xls with the VB script included already

PPS- You can modify the script to take .avi as input files by changing all .mpg to .avi in the VB script.