Spreadsheet v1.00

Autoplay, reporting, all those clever things it does. Discuss.

Moderator: kirkm

kirkm
Platinum Record (Admin)
Platinum Record (Admin)
Posts: 694
Joined: Wed Mar 07, 2007 8:50 am

Spreadsheet v1.00

Postby kirkm » Thu Feb 02, 2012 5:21 am

Hi All,

The much-hyped version 1.0 of the macro spreadsheet is finally ready for testing.
This is a major advance on any previous version and finally is what was envisaged some
seven or eight years ago. Whew! It certainly has been a huge challenge for everyone involved.

It's almost guaranteed there'll be a few teething problems, but hopefully nothing that can't be
easily and quickly fixed. We've overcome a huge bug which ground things to a halt for a while but
thankfully that's now resolved.

The biggest change is in the Click events, the new 'Nipper' search engine and the speed of data between Excel and the Access backend file. Nipper searches in this version apply just to the 'active' Sheet, Britburn (including the 1990s), EPs or SheetMusic but will have a Global option for everything in the next version.

An ID number is now assigned to every Artist - this wil be used by both the Website and the spreadsheet as we move towards more interaction and porting of data between the two. What this means, if you search for an Artist you will get every match, not just hits where the spelling or format are the same.

User Configuration has an extra Option - Layout - where you can set colours and positions for the various Forms. The defaults are for a screen resolution of 1280x1024. The colours may not be quite to your liking and if you have some ideas, let's know and we can change the default settings.

Saving User Configuaration now does NOT save the entire Workwook, it just writes an ini file. If you want to save any changes you must do this manually, in Excel. Also any data changes will need to be applied to the Backend as well.

There's quite a bit of data - especially 1980s and later - still to come and the team will continue this as fast as it's entered and post regular upgrades.

This thing is fun, fast and VERY powerful ! :D

If you'd like a copy to test, please PM or email me and say whether you prefer xls or xslm. It will be on the website in about a fortnight or so, depending on test results.

Cheers - Kirk

ukcharts
Platinum Record (Admin)
Platinum Record (Admin)
Posts: 383
Joined: Thu Jan 01, 1970 1:00 am

Re: Spreadsheet v1.00

Postby ukcharts » Thu Feb 02, 2012 10:21 pm

Hi Kirk,

Congratulations on reaching this milestone. The amount of work you've put into it is incredible, obviously a labour of love. I hope everyone appreciates it.

Who'd have thought it, all those years ago (it seems longer :wink: )
=-=-=-=-=-=-=
U K C H A R T S
-- webmaster --
=-=-=-=-=-=-=

kirkm
Platinum Record (Admin)
Platinum Record (Admin)
Posts: 694
Joined: Wed Mar 07, 2007 8:50 am

Re: Spreadsheet v1.00

Postby kirkm » Fri Feb 03, 2012 12:10 am

Thanks Bert.... guess it was too! Kept me off the streets and out of trouble!

It does have a lot of potential, and next is the 'Member Of' and 'AKA' combinations and how to thread them in intelligently.

BTW I'm sure I left a msg here about a Stop in it which might occur - and if it does just to press F5 to continue. Will wait for a few more things to fix then get another one ready.

Cheers - Kirk

BoroButch
Number One
Posts: 71
Joined: Sat Jun 09, 2007 10:01 pm
Contact:

Re: Spreadsheet v1.00

Postby BoroButch » Tue Feb 07, 2012 11:38 pm

Hi Kirk,

I'm absolutely loving V1, I keep making Woo! noises every time I click on something and another window opens revealing as much information as you could ever need. :D

I'm still having one problem, which I had with the beta version as well. It wont play mp3s for "uEP59_007a - The Lady Is A Tramp - Frank Sinatra", when I click on it, the display at the bottom shows "0x mp3s", hence it wont play the files, even though the files are there. I tried changing the file names from "uEP59_007a - The Lady Is A Tramp - Frank Sinatra - 01 The Lady Is A Tramp" to "uEP59_007a - The Lady Is A Tramp - 01 Frank Sinatra - The Lady Is A Tramp", but still didn't find them.
Is anyone else having the same problem?

kirkm
Platinum Record (Admin)
Platinum Record (Admin)
Posts: 694
Joined: Wed Mar 07, 2007 8:50 am

Re: Spreadsheet v1.00

Postby kirkm » Wed Feb 08, 2012 2:55 am

Hi Boro,

Glad you asked that ! This was a coding error on my part and took a number of hours to sort out.
Just the sort of thing that needed fixing. The problem was conditional though. I'm assuming if you d-click EP59_007 then click the Play Icon on 'main control Form' you get all 4 tracks? Likewise if you d-click Frank Sinatra, you can play both the full EP, or each track from the Reports Form?

The problem only arises d-clicking individual tracks on the main control Form ?

However, you say when I click on it, the display at the bottom shows "0x mp3s" by 'it' you mean either the Pefix or the Title ?

What's significant about EP59_007 is , it doesn't actually exist ! It, and a few others, use the non-EP files from the Singles collection. If you look at column K (Duration) there's a comment saying 'Also u58_201' and it's those resource files that are accessed.

These offset-numbers may be a bad thing, but they're entrenched now and the reason was to save space on not double up unnecessarily on duplicate files.

Have you got 58_201a-d in the 1958 folder ? if you have, clicking Prefix or Title for EP59_007 should show 4 mp3's. Similarly for 60_001 and 60_003.

One other thing to try - d-click Frank Sinatra (EP59_007) then the info button on the Form. Choose 'mp3's'. You should see a list like this:
----
MP3 Query - Albums by Artist like 'Frank Sinatra'

Missing:

Found:
EP59_007a (Using 58_201a) The Lady Is A Tramp
EP59_007b (Using 58_201b) Witchcraft
EP59_007c (Using 58_201c) Come Fly With Me
EP59_007d (Using 58_201d) Tell Her You Love Her
EP59_010a Come Dance With Me
EP59_010b Something's Gotta Give
EP59_010c The Song Is You
EP59_010d The Last Dance
EP60_044a It Happened In Monterey
EP60_044b Blue Hawaii
EP60_044c Brazil
EP60_062a Begin The Beguine
EP60_062b I've Got A Crush On You
EP60_062c I Concentrate On You
EP60_062d All Or Nothing At All
EP60_086a Ain'tcha Ever Comin' Back
EP60_086b April In Paris
EP60_086c How Deep Is The Ocean
EP60_086d The Song Is You
EP60_089a You Make Me Feel So Young
EP60_089b It Happened In Monterey
EP60_089c Anything Goes
EP60_089d How About You?
EP61_018a All The Way
EP61_018b Chicago
EP61_018c I Didn't Know What Time It Was
EP61_018d I Could Write A Book
EP61_034a Just In Time
EP61_034b Dancing In The Dark
EP61_034c Cheek To Cheek
EP61_034d Baubles, Bangles And Beads
-----
The reason they don't play is a bit hard to explain, but was a muck up in assigning the offsets. the fix involved a large number of changes, so a new spreadsheet is the easiest solution. But will be another day as other changes are pending.

Although they're not on the web site yet the EP checksums (from the recent NG post) will re-create any missing placeholders (EP59_007 is a placeholder) and the 1958 checksum will tell you what mp3's are missing.

By the way, the spreadsheet only reads up the the first " - " and the last 3 characters in the filename. The rest can be anything.

Cheers - Kirk

BoroButch
Number One
Posts: 71
Joined: Sat Jun 09, 2007 10:01 pm
Contact:

Re: Spreadsheet v1.00

Postby BoroButch » Wed Feb 08, 2012 11:43 am

Thanks Kirk,

Just checked my 1958 folder and I didn't have 1958 extras. I have now downloaded them and it works fine now, even d-clicking on EP title displays 4x mp3s and plays them all. What confused me was I had the EP mp3s in the 1959 folder instead of a nfo place holder. :roll:

kirkm
Platinum Record (Admin)
Platinum Record (Admin)
Posts: 694
Joined: Wed Mar 07, 2007 8:50 am

Re: Spreadsheet v1.00

Postby kirkm » Thu Feb 09, 2012 1:21 pm

Glad that's all sorted Steve. :D

Another wee problem just found - d-clicking any title (or cell) where the text contains a square bracket will fail to generate a report. Just testing out a solution... stay tuned !

Cheers - Kirk

PS Happens only if Absolute match = off but that is the default.

kirkm
Platinum Record (Admin)
Platinum Record (Admin)
Posts: 694
Joined: Wed Mar 07, 2007 8:50 am

Re: Spreadsheet v1.00

Postby kirkm » Tue Apr 03, 2012 11:53 am

Hi All,

As the final release date for v1.0 nears, if anyone has any corrections or new data for inclusion, please let me know ?

Thanks - Kirk


Return to “The Spreadsheet”

Who is online

Users browsing this forum: No registered users and 1 guest