(Utility) Excel Template to EXPORT to MMD files

English Scripts
1

There’s a thread in General (http://forum.mairlist.com/index.php/topic,3356.0.html) where the project of an Excel Template to export (i.e. create) MMD files was resurrected: hence this thread!

I’ll be writing this soon, so here are the ‘rules:’
[ul][li]The spreadsheet format will be identical to my ‘import from MMD files’ Template (http://forum.mairlist.com/index.php/topic,3265.0.html).[/li]
[li]You MUST NOT insert or remove any columns, or change the ORDER of the columns (unless you rewrite the VBA to match! ;)).[/li]
[li]Only the items which have a column can be written to the MMD files created by the Template.[/li]
[li]The Template will write the MMD files to a directory you choose. Remember that the resulting MMDs need to be moved to either the same directory as the corresponding audio files, or in your ‘central’ MMD directory (if you have set one up).[/li]
[li]The Template will write ALL the MMD files into the SAME directory. If your Excel list refers to audio files from different directories, consider splitting it into multiple spreadsheets, especially if you plan to move the resulting MMDs to the same directory as the corresponding audio file.[/li]
[li]The Template WILL overwrite any existing MMD files WITHOUT any warning. Make sure ALL your existing MMDs are backed up BEFORE running the Template![/li][/ul]

I’ll post beta versions of the Template here as I write them over the next few days.

BFN
CAD

2

Actually, the template WILL check for MMDs in your chosen target directory and you will have the option to quit at that point.

This is mostly written now, and is writing convincing-looking MMDs. It’s working very well with h:mm:sss.ttt time formats (for cue points), so I just need to test it with the other two options (sss.ttt and sssttt) and it should be good to go!

BFN
CAD

3

Brilliant Cad - looking forward to it!

Just a small comment - you say the Template will write the MMD files to a directory you choose - can this be back to the original directory they were read from as the source path is already known?

Reason? It would then be possible to read in all current MMDs, use Excel to sort in different orders to spot duplications/make bulk changes etc and then write them all back again to the same files. Perhaps you were already planning this and I have misinterpreted your post.

Cheers

Ron.

p.s. Can I be the first to wish you a Happy New Year (assuming that you do New Year more than you do Christmas ;D)

4

Yes, Ron, you can do that and they will overwrite your originals, but I would strongly recommend that you write the MMDs to a different directory from the one they currently occupy. Several reasons: won’t overwrite your originals ‘by mistake;’ allows you to CHECK their contents BEFORE you try to use them ‘for real;’ and prevents any possibility of ‘access denied’ messages if mAirList or anything else currently has any of your original MMD files open.

It’s up to you, though: if you DO want to write the files back on top of your originals, feel free!

And yes, of course de-duplication, bulk changes etc. were always part of the import/export plan (unless you’re like Torben and refuse to install any MS apps like Excel!).

The other main use for the exporter (particularly) is to take data from ‘other’ playout programs/schedulers and have a means to write them directly to n MMD files, thus minimising the pain of transferring from one system to another. To me, this has always been a HUGE gap in playout systems. Don’t authors WANT you to move to their system? OK then, why oh why do almost NONE of them provide a proper ‘import your audio and cue point data’ application?

I’ll forgive Torben for not doing this himself :D, since a) mAirList is WONDERFUL, and b) for once, I do have all the info, to be able to write my own ‘add-ons’ (admittedly in VBA and Excel) to read and write audio ‘metadata’ to and from the ‘native’ format (MMD files).

Incorrect assumption. I’m not into ‘mass hysteria’ events like Hogmanay, Xmas, death of Princess Diana :o …

I did enjoy parties and even the OLD New Year celebrations in the Royal Mile in Edinburgh (in the 1970s) when I was considerably younger, but not nowadays.

If I’m celebrating anything at all (very infrequent occurrence) it’ll be something that’s meaningful to ME, such as finally fixing some annoying fault on my pinball machines, or successfully testing some bit of code or other. :wink:

BFN
CAD

5

Happily awaiting the script Cad! :slight_smile:

-Beau

6

OK folks, here is the EXPORT template for Excel.

You need to put the unZIPped XLT file into your MS Templates directory, which is usually:
C:\Documents and Settings[i]username[/i]\Application Data\Microsoft\Templates

Then, as with the companion Import from MMDs template, you use it by opening Excel and clicking:
File, New…, From Templates/On My Computer, then clicking the template. This will be remembered, so next time you’ll see a link direct to the template under Recently Used Templates on the same Task Pane.

Once you have the new worksheet open, you PASTE data into it.

If you have already used the Import template to ‘capture’ the contents of your MMD files so you can make a mass change or even a tweak or two, you can simply copy and paste rows (the layout of the two templates is identical).

If you are taking data from another playout system, first import that data into a PLAIN Excel worksheet; then take a SEPARATE copy of it. Then re-arrange the columns in that second worksheet so they match the Export MMDs layout as closely as possible.

A few rules, especially if you are bringing in ‘foreign’ data:
[ul][li]You MUST have the CORRECT name of the audio file or MMD file in column A. The path is irrelevant and is ignored during processing.[/li]
[li]All cue point and duration values are ABSOLUTE (e.g. 3:20 and NOT 2.00 [from end])[/li]
[li]All cue point and duration values must be in the SAME format. This can be m:ss.ttt or sssttt or sss.ttt (all TEXT STRINGS) or it can be an Excel date/time value (where 1.000 = 1 day, and so on), but ALL times must be in the SAME format.[/li]
[li]All Fade Durations MUST be in milliseconds (number or string), so 2 seconds would be 2000.[/li]
[li]All Fixed Times MUST be in 24-hour hh:mm:ss format.[/li]
[li]To ‘set’ an Option (like Linked), type Y (blank means ‘off!’)[/li]
[li]IconFilename MUST be typed RELATIVE TO the PC running mAirList. On a network, this might be a drive letter pointing to a shared drive.[/li]
[li]There is minimal error checking (in the current ‘beta’ version!). The template code will believe you when you select the time value format you are using for cue points etc. Therefore it will produce faulty MMDs and may well crash if you specify the wrong format.[/li]
[li]The template is already formatted so that regardless of the time format you use, you will see milliseconds (even if you use Excel date/time serial numbers).[/li]
[li]All MMD files created by running the macro in the template will be written to the SAME folder. If any MMD files already exist in that folder, you’ll see a dialog with options to overwrite the existing files (if they have the same names) or cancel: so you can select a different folder.[/li][/ul]

I’ve tested this with all four types of time format and it has produced accurate MMD files from data stored in each type (and created using the Import template!).

Please let me know if you find any problems with it that I haven’t already fixed.

In due course, I’ll produce a PDF write-up and a new version with more error checking.

PS: If you’ve used my Import template, you’ll notice that this one runs a LOT faster! :wink:

[Template removed: updated V1.1 is in a post below!]

BFN
CAD

7

For anyone interested, I’ve almost finished a conversion of the Excel template to OpenOffice Calc (purely as an experiment). It’s all working except that the time conversions are returning small decimal numbers (?). To be investigated and fixed. :slight_smile:

I might convert the Excel Import template to OpenOffice as well, but only if there is genuine interest. OO Basic does lots of things VERY differently to VBA, so it’s a bit of a mind-bender doing the conversion.

BFN
CAD

8

To complement V6.0 of my Import template for Excel (and mAirList V2.2.2 and later), here is a new version (V1.1) of my Export template, which contains a Track column and (obviously!) writes this into the MMD files it creates.

(PS to OpenOffice users: I will similarly update the Calc version of this template in the near future. Stay tuned! An Import template for Calc is next on my ‘to-do’ list after that.)

BFN
CAD

Export MMD Contents V1.1.zip (52.2 KB)