Auto playlist generator - update

474 views
Skip to first unread message

Jasper Warwick

unread,
Feb 3, 2020, 5:24:46 PM2/3/20
to Brennan Forum
I have done an update to my auto playlist generator. It now stores the year the album was released and this can be used to create playlists based on the date the album was produced (e,g, "70's albums",  "2019 albums"). 

The spreadsheet scans the B2 and builds tables of artist, album, and track, it then reads the ID3 tag of each track and stores it in the spreadsheet along with the date the album was loaded onto the B2 and the release date of the album. Pressing a button gets the spreadsheet to auto create playlists based on genre, newness and release date of the album. By default the B2 only stores basic information (Artist and Album) but it is possible using software such as ID3Tag to automatically add additional information (Genre and Date).

What is means is that I can now segment my music by a number of different criteria which makes accessing my library much easier.

If I want reminding of what I have bought recently I use the "album-new" playlist which lists the most recent 200 albums I loaded onto the system in date order, newest on top.

If I am in a 70's mood then using "Album - 70's" will give me a list of all albums released in the seventies

If I am in the mood for opera then the "Opera" playlist will give me list of artists that have operatic works in their folder. etc. etc.

I can also create my own custom playlists at artist, album, and track level on the spreadsheet which are then uploaded to the B2. The spreadsheet preserved any customer playlists so they don't get deleted if you re-scan 

If like me you have created an MP3 version of the library for use on a phone or in the car it will also generate M3U playlists.

If a playlist accidentally gets deleted or corrupted on the B2 then it takes a couple of minutes to upload a replacement set from a pen drive.

It requires Excel 2010 or later to work, the copy I have uploaded has my library on it so you can see how it works but it only takes a couple of button presses to clear the spreadsheet and load a new library into it.

There is a help section on the spreadsheet to assist with getting it working

Feel free to give it a go.

B2 - 1.jpg


B2 - 2.jpg



Brennan30.xlsm

PMB

unread,
Feb 4, 2020, 3:56:30 AM2/4/20
to Brennan Forum
Hi Jasper,

Thanks for the update.

BTW if you post your screenshots as attachments, rather than pasted into the text, we can view them at a larger scale.

Paul
Brennan Support.

Jasper Warwick

unread,
Feb 4, 2020, 4:11:35 AM2/4/20
to Brennan Forum
good point Paul, attached.
B2 - 1.jpg
B2 - 2.jpg

Dick Cooper

unread,
May 5, 2020, 1:30:01 PM5/5/20
to Brennan Forum
Hi
I used an ealier version to create playlists but somehow my playlists got corrupted and I need to rebuild. Do you have a version that will work with Office 2000 or do you know if the .xlsm version will work with OpenOfffice Calc please?
Thanks

Dick Cooper

unread,
May 5, 2020, 1:40:38 PM5/5/20
to Brennan Forum
... or I could try one of the online converters and see if that will produce something workable if you don't already have a .xls version yourself...

Jasper Warwick

unread,
May 5, 2020, 2:28:58 PM5/5/20
to Brennan Forum
Hi,

I don't think the xlsm version will work with open office. The later versions use data tables and those only came in with (I think 2007). There are a couple of options

If you want to post the the version you have been using I should be able to save it as an xls file that should work with earlier versions of office. 
I could also run the speadsheet for you and output the playlists which you could then import.
I also have some old copies of Office 2003 which might work better than office 2000 which I could let you have. PM me if interested.

Jasper

Jasper Warwick

unread,
May 5, 2020, 2:32:02 PM5/5/20
to Brennan Forum
As an aside I have just done a minor upgrade; the system now works out the total play duration in days of your library. Mine is 49.01 days!

I'll just check that there are no bugs in the playlist generation then I will post it.

Jasper.

Jasper Warwick

unread,
May 5, 2020, 6:33:47 PM5/5/20
to Brennan Forum
Here is the latest version 
Brennan32.xlsm

Dick Cooper

unread,
May 6, 2020, 8:07:04 AM5/6/20
to Brennan Forum
Hi Jasper

After more research in the Forums to work out what I did last time, I see I was using the Microsoft Open XML converter Moc.exe to open Brennan18.xlsm on a Windows XP computer and running the sheets using Excel 2003 ... https://groups.google.com/d/msg/brennanb2/p15eQH349Lo/bBj4rBsyBQAJ

Now I'm using Excel 2000 (I couldn't port Excel 2003 across) on a Windows 10 computer, no xlsm file will open because the converter won't run. As you predicted, OpenOffice doesn't work either.

HOWEVER... I uploaded Brennan18.xlsm to cloudconvert.com and when Brennan18.xls came back, it seems to work OK (brief test, don't have a Brennan backup to run it on as the B2 is still bumbling along making a USB backup)
BUT... converting Brennan30.xlsm gives me a file I can open and run but I get a runtime error 438 in Playlists code at the line
With Worksheets ("Tracks").ListObjects("Tracks")

SO... When the B2 has finished its backup sometime maybe I'll try to run the whole thing on Brennan18.xls. If not I still have the XP computer as a last resort.

Thanks again, and I'll post again when I've re-tried running the whole thing.

p.s.Oddly, the Find Genre worksheet doesn't run on either the converted 18 or 30.xls files, although that's not a problem since I know its offset already.

Dick Cooper

unread,
May 6, 2020, 1:14:17 PM5/6/20
to Brennan Forum
Hi again

My mistake - Find Genre does work, forgot the trailing backslash.

The spreadsheet doesn't seem to work on a B2 backup. Maybe because the folder arrangement is X:\hardfi\music\artistname  albumname\tracks - not - X:\...\artist\album\tracks as on the B2 itself. My backup created using Backup to C on Bennan - is this the wrong kind of backup?

Instead I'm now running the spreadsheet on the \\BRENNANB2\music\ folder and it will report on progress. Processing artist 11 or 406 after 10 minutes running Build Genre List - will leave overnight as network quite slow.

Regards


Daniel Taylor

unread,
May 6, 2020, 1:21:57 PM5/6/20
to Brennan Forum
On the B2, the Backup and Restore functions are there only to maintain backward compatibility with the old JB7 units.  If the JB7 is not a concern for you, you should be using the Export and Import commands.  I think the spreadsheet playlist functions will work on a B2 backup that was done with the Export command.

Jasper Warwick

unread,
May 6, 2020, 1:44:25 PM5/6/20
to Brennan Forum
Yes best use the export function, it will definitely not work on a B2 backup.

That is quite slow, on my PC ir takes about 90 minutes to do all the steps to completely process 15,000 tracks, I am afraid I built the system for ease of programming not speed of operation!

Dick Cooper

unread,
May 7, 2020, 7:42:58 AM5/7/20
to Brennan Forum
OK, thanks - I was looking at the Brennan18 help which referred to backup rather than export. Confusing, eh?

Using the Brennan18.xlsm which I converted online, running Excel 2000 on my Windows 10 machine, I got runtime error 91 'Object variable or With block variable not set' processing album 633 or 861 running the Build Album Playlist function. Debug show the line of code in Albums is 'For Each Ofile in .Items'. So I guess that doesn't work after all, although all the tables up to that point looked to be correctly populated.

So I am now making an export database instead of a backup database on the B2 and I'll try running that on my old XP machine with Excel 2003 which I know worked last time. I'll see if I get the same problem. If I don't post again it's because I've died of old age waiting for the B2 export (now on track 900 of 12,000 after 30 minutes), not because I've given up.

Thanks again for your help.

Dick Cooper

unread,
May 7, 2020, 8:00:04 AM5/7/20
to Brennan Forum
sorry for not being clear - I got that runtime error 91 running the Brennan18 spreadsheet directly on the B2 over the network, not on the 'backup'... now on track 1500 of 12,000 creating an export database on USB... (yawn)

Jasper Warwick

unread,
May 7, 2020, 8:06:32 AM5/7/20
to Brennan Forum
Thanks for point that out I will change the wording.

If it fell over after processing 633 albums it is a problem with Excel not handling a funny character in the file name or album name.

If you look at where it crashed, scroll down the list of albums until you find the one where the processing stopped. then look at the files contained in it. you might find that one of them has some strange characters or punctuation. What has happened is that Excel has (not) helpfully translated those which means that it has problems locating one of the files. Change the file name to something simpler and it should work. You don't need to re run everything just from the point it fell over.

Hope this helps, email me the file if you like and I can probably tell you where to look.

Jasper

Jasper Warwick

unread,
May 7, 2020, 9:05:37 AM5/7/20
to Brennan Forum
Dick, it looks like you were able to progress past the error which is great, sorry it is so slow for you!

Dick Cooper

unread,
May 7, 2020, 9:35:15 AM5/7/20
to Brennan Forum
No, I haven't progressed past the error, I'm just off on another tack and trying to create an exported database to use with my XP computer.

Here are screenshots of where the spreadsheet got to on the Windows 10 computer, and what Explorer says is in the folder on the B2. Looks OK to me. I saved the spreadsheet when it failed, and I'm running Build Album Playlist again (while I wait for the export) in case it's a glitch...



The Band.jpg
Stage Fright.jpg

Jasper Warwick

unread,
May 7, 2020, 10:53:10 AM5/7/20
to Brennan Forum
I think it may have crash on track 5 of Rock of Ages

Dick Cooper

unread,
May 7, 2020, 12:48:52 PM5/7/20
to Brennan Forum
Well, when I ran it again, it ran all the way through... so it was a glitch - maybe a hang on the network. I'll try again tomorrow after the export database finishes. I've made some changes to the B2 in the eatime to tidy up some errors the first run revealed (before it glitched).
Thanks again,

Dick Cooper

unread,
May 8, 2020, 7:12:37 AM5/8/20
to Brennan Forum
I ran Brennan18.xlsm on my Windows XP machine using the Microsoft Open XML converter to open Office 2003. Took about 20 minutes in total (12000 tracks, 860 albums, 400 artists) using a 128Gb SanDisk thumb drive export database instead of the network. Seem to have worked fine, just need to check the details. Many thanks indeed.

Meanwhile, I note in your first post you say "If a playlist accidentally gets deleted or corrupted on the B2 then it takes a couple of minutes to upload a replacement set from a pen drive" - do you know whether if I use the Load Playlists function it will REPLACE rather than ADD? And I don't have to delete all the broken playlists? If you don't know I'll delete them all first, although deleting playlists is what got me this problem in the first place...

Dick Cooper

unread,
May 8, 2020, 7:34:42 AM5/8/20
to Brennan Forum
Bad news/good news. I also (earlier today) tried to use Brennan30.xlsm using the same XML converter. All worked fine right up to the Create Playlist function which got a runtime 1004 error. Looking at the sheet, I see the error is in the last album and cell BD876 contains =(#REF!+#REF!++#REF!+SUM(#REF!)+SUM(#REF!))>0. All the Genre playlists and Album- playlists created to that point look correct, even the one for the last album.

This is not a big problem for me, since Brennan18 created the Genre playlists which is what I wanted, but you might like to look at the Brennan30 code because I wonder what will happen to albums released in 2020 (not that I have any yet). Snapshots below (sorry about quality, let me know if you need better).

Anyway, sorry to burden you with what might be a problem. At least I'm having a great day now I've created some playlists!

Thanks again




Runtime 1004.jpg
F876 LT 2020 ref.jpg
DataBodyRange.jpg

Jasper Warwick

unread,
May 8, 2020, 8:20:25 AM5/8/20
to Brennan Forum
I don't think V30 will work for you as it uses tables to manage tracks which did not come in to Excel until 2007. Any version of Excel will crash when it hits the tracks section of the workbook

Jasper Warwick

unread,
May 8, 2020, 8:24:15 AM5/8/20
to Brennan Forum
Use the "Load Playlist" function. it will replace all the playlists on your B2 with the copies that are on your pen drive. If you have created any playlists using the B2 features then these should be left intact on the B2. basically the Load Playlist function just copies the files on the pen drive to the B2, overwriting any files of the same name.

Dick Cooper

unread,
May 8, 2020, 8:33:41 AM5/8/20
to Brennan Forum
OK, that explains why Brennan30 doesn't work. No problem.

I still wonder about the hardcoded date of 2020 in cell BA876
=IF(AND($F876>=2010,$F876<2020,$BD876),1,)
what happens to albums released in 2020? Should there be a formula in BB876 for albums >=2020?

And there's hardcoded 2019 and 2018 in cells AT876 and AT877. For futureproofing would it be better to select today's year and put it in a hidden cell somewhere?

Cheers

Dick Cooper

unread,
May 8, 2020, 8:37:26 AM5/8/20
to Brennan Forum
Ok, thanks for update about Load Playlist. I DO need to remove any bejangled playlists that I'm not replacing. Now why can't the documentation say that (no, don't answer that). Thanks

Jasper Warwick

unread,
May 8, 2020, 8:38:54 AM5/8/20
to Brennan Forum
The formula errors in V30 are linked to my code that creates playlists based on the release year of the album. The error is possibly because a non numeric code was read in.

Jasper Warwick

unread,
May 8, 2020, 8:45:50 AM5/8/20
to Brennan Forum
Attached is a version of the latest software that has the tracks functionality removed. This should work on an older version of Excel if you want to try it.
Brennan33 No tracks.xlsm

Dick Cooper

unread,
May 8, 2020, 11:33:27 AM5/8/20
to Brennan Forum
OK, thanks, I will try that - sometime soon next time I power up the XP machine. Meanwhile I am celebrating that I have my playlists back (playlists for each Genre - just for each artist, not by albums - too many different albums).

Having a B2 is like keeping an elderly pet - it's quiet for ages and then it all suddenly kicks off.

Thanks once more.

Jasper Warwick

unread,
May 8, 2020, 4:56:04 PM5/8/20
to Brennan Forum
Re the 2020 year, yes there should be, it is just that I don't have any 2020 published albums so it is a bit academic :-)

If you are up for writing simple excel formula it can be a good way of assembling non genre based playlists. or combining genres together.

Dick Cooper

unread,
May 10, 2020, 7:52:28 AM5/10/20
to Brennan Forum
Hi

I converted the Brennan33 No tracks.xlsm through cloudconvert and ran with Excel 2000, worked fine up to Export M3U playlists which fell over with runtime 1004 (see attachments). Same error same place with Excel 2003 and XML converter on XP machine. No problem, since I don't need M3U playlists, and now I know not to do that. Just a thought, could you make the No tracks function a button or is the function embedded too deep? By the way, I was wrong, I do have an album released in 2020, not that it matters as I only wanted the Genre/Artists playlists. Which I've got now, thanks. So it's all good.

Thanks
Brennan33 runtime 1004.jpg
Brennan33 runtime 1004 debug.jpg

RichM

unread,
May 14, 2020, 9:59:27 AM5/14/20
to Brennan Forum
Jasper,

Further to a separate recent thread about Why Tag? I have had another run through of your spreadsheet to see how my partially complete tagging exercise pans out. As is mentioned above, Excel has problems with some characters, in particular for my collection it is Czech names - Dvorak and Janacek which I have as Dvořák and Janáček. It is a shame that the B2 can handle them but Excel, a de facto world standard, can not!

I can rename my albums - I assume that it doesn't have to be done at track level - but I don't suppose anybody out there has any other workrounds?

Thanks for the spreadsheet, BTW, I'm still getting into it.

Rich

Jasper Warwick

unread,
May 14, 2020, 11:45:36 AM5/14/20
to Brennan Forum
If you can send me any names that Excel has a problem with I can look at some fixes. Because it has not been a problem for me I have not really investigated the issue. I might try creating a test artist, album, and tracks with the problem characters and see if I can get Excel to work with it.

Jasper Warwick

unread,
May 14, 2020, 12:01:23 PM5/14/20
to Brennan Forum
Just looking at my collection I have quite a few tracks with non English accented characters;

117 Bach - Ach grosser König, gross zu allen Zeiten.flac
106 Selig,  Franz-Joseph - 6. Aria (Alt) 'Buß und Reu'.flac

Which the system seems to handle, I can't find any special characters in the folder names. I'll try with  Dvořák and Janáček as artist and album and let you know how I get on

Jasper Warwick

unread,
May 15, 2020, 6:51:31 AM5/15/20
to Brennan Forum
I have just build a test folder and files with Dvořák and Janáček and it is definitely not happy!

I have had a quite look at the problem on line and there is a alternative way to read file structures, I will have a look at converting my code and upload a version that will work with special characters.

Japser.

RichM

unread,
May 15, 2020, 12:31:04 PM5/15/20
to Brennan Forum
Jasper,

You are a star - I guess you like a challenge. I too have some umlauts and other symbols but seems to be the Czech ones it doesn't like. I'm pleased to say I don't have any Polish music.  Thanks for looking at this - in the meantime I am plugging away with MP3tag so I can take full advantage of your revised code.

Cheers,

Rich

Dick Cooper

unread,
May 16, 2020, 6:28:32 AM5/16/20
to Brennan Forum
Well, just when I thought I had my playlists cracked someone else finds a problem.

Me too, I had Danú, Déanta and Lúnasa all missing from my Celtic playlist, although they were on the B2 with the right names (although the B2 web interface sorts Déanta to the end of the Ds, after Dwight Yoakam). I've just added them manually to the playlist. No problem with Meridianu's album Senza Cchiù Terra in my European playlist though, because I only have the Genre/Artists playlists loaded not Genre/Albums.

Since reading this thread, I wonder if using accents on the B2 makes sense - it's pretty difficult to do a search for Déanta when you can't remember the ascii code for é. I remember that the JB7 used to completely mangle special characters, so I edited them out of the track names in most of my albums, long before I got the B2, which only retains accents in more recent acquisitions such as Déanta. Now I'm thinking I'm going back to edit out the accents... at least in artist names.

Good luck with the changes, your spreadsheet's been a game-changer.

Jasper Warwick

unread,
May 16, 2020, 6:41:55 AM5/16/20
to Brennan Forum
Hi,

I have almost fixed it!

The sheet now reads and processes filenames and folders with extended character set correctly, I just need to workout how to create the playlists in a form that the B2 will read. I think the B2 needs a utf-8 character set which is a variable width character set but so far my attempts to write a utf-8 file has resulted in a Unicode character set which B2 cannot handle.

Should have it fixed today though (in between weekend chores)

Jasper.

Jasper Warwick

unread,
May 16, 2020, 11:57:10 AM5/16/20