Latest Macro Spreadsheet

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

Moderator: kirkm

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

Latest Macro Spreadsheet

Postby kirkm » Sun May 29, 2011 11:40 am

Hi All,

The latest macro spreadsheet is now available for usage. Basically it's the same as the existing one, but with the 1960s Extra's included and a renumbering utility.

There's also the usual data additions and corrections.

The renumbering utility will run once if you have any of the 1960,61,62, 65,66, or 1967 mp3's, labels or lyric files that need renumbering. However it will only work with files it can identify so if you have more (i.e. any that show as Additional if you run that years checksum) they may require changing manually.

It will move (and keep) these files into a backup folder that you specify. Then copy them back to the project folders with the numbers changed in both the filename and ID3 tags. A log file will be created in the backup folder. New checksum files confirming the changed file will be available tomorrow.

I hope it works for everyone without any problems. I'll wait a few days before adding the link to the website, but for now

Normal Version:
http://www.britburn.co.uk/files/spreads ... 99888m.zip

Whitburn Add-On Version:
http://www.britburn.co.uk/files/spreads ... 9888mw.zip

Note the included BackEnd mdb file is required and should be saved to the same folder as the Excel file.
Cheers - Kirk

Glinty
Number One
Posts: 161
Joined: Sat Jun 09, 2007 5:47 pm

Re: Latest Macro Spreadsheet

Postby Glinty » Mon May 30, 2011 12:02 am

Hi Kirk,

I downloaded the Normal Version and un-zipped and opened it expecting the renumbering utility to run, but of course it didn't. I should have remembered that the first time you open a macro version of the spreadsheet you have to set up the User Configuration to tell the spreadsheet where you files are Doh!!! Anyway after doing this I opened the spreadsheet a second time and the renumbering utility ran perfectly. Thanks for all your hard work and I now look forward to some more of your extra tracks.

There is one small problem with the new spreadsheet. The weekly chart calendar does not open when clicking on the Britburn Logo on the Britburn Worksheet, it's ok on the 1990's and Sheet Music worksheets.

Cheers
Glinty

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

Re: Latest Macro Spreadsheet

Postby kirkm » Mon May 30, 2011 3:23 am

Hi Glinty,

Well spotted ! Right click the logo and click on 'Assign Macro. Paste this into the Macro name
text box

BritburnSpreadsheetBeta.999888mw.xls!TheCharts

Click OK, click away from the logo. Now it should work.

Not setting paths automatically is very strange and something I can't replicate here. You're not the first one who's said it doesn't happen - and it should!

When it opens, Private Sub Workbook_Open is the first thing that runs. After checking the Excel version and Backend, it checks if your System temp folder is in Config Sheet cell A5. If it's not it puts it there.

If this is your first run with a new version, User Config will be empty, so it looks for a file called britburn.ini in your temp folder. If found, all the details are copied in for you, otherwise it aks if you want to enter them manually.

After you've put them in it saves a new britburn.ini. If the details came from the ini file itself there's no need to Save it as it's already there. And it should never be missing because if it is, you have to enter the details and the file is created. The theory being you only need to enter the data once, or when you change something.

And I can't make it fail! If you want to see if you have an ini file, in the immediate window in Excel paste
? Environ$("Temp")

Look in that folder for 'britburn.ini'.

If it's not there, change something in User Config and answer Yes to the Save prompt.
(Restore it back later)

Does this create a 'britburn.ini' or not ?

Does anyone else have this problem, or does it work for you?

Cheers - Kirk

Glinty
Number One
Posts: 161
Joined: Sat Jun 09, 2007 5:47 pm

Re: Latest Macro Spreadsheet

Postby Glinty » Mon May 30, 2011 10:52 am

Hi Kirk,

The Chart Calendar is now working ok, but it wouldn't let me paste into the macro name box so I typed it in omitting the "w" because I'm not using the whitburn version.
I wasn't aware that the user config was automatic and have allways set it manually like we had to in the early days. One of the things I allways change is the location of the Temp folder to a folder called C:\Britburn\Temp and this is where the "britburn.ini" file is located. I assume this is the cause of my problem. In future I will leave it set to your default location. The unusual thing is that in this case when I went to set up the user config it was set to D:\Britburn and the D drive is my DVD RAM drive???

Cheers
Glinty

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

Re: Latest Macro Spreadsheet

Postby kirkm » Tue May 31, 2011 3:39 am

Hi Glinty,

Just shows the best laid plans... quite hillarious. By changing the project temp location (as is possible and quite OK to do) the ini file is not saved to the enviromental temp location.

That wee change should fix that. Another bug smitten :D

Cheers - Kirk

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

Re: Latest Macro Spreadsheet

Postby kirkm » Tue May 31, 2011 3:44 am

Udpdated spreadsheet coming!

The links in my message 2 days ago are still valid, but there's been a number of late additions and a few logic fixes to warrant an update. Please consider v999888 as temporary and get the updated one due tonmorrow or the next day. Thanks!

Cheers - Kirk

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

Re: Latest Macro Spreadsheet

Postby kirkm » Tue May 31, 2011 9:31 am

Normal Version
http://www.britburn.co.uk/files/spreads ... 99889m.zip

Whitburn Add-On
http://www.britburn.co.uk/files/spreads ... 9889mw.zip

If you have renumbered your files using the version from 2 days ago, delete the BackEnd.mdb
in the zipfile and keep the one you have.

Cheers - Kirk

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

Re: Latest Macro Spreadsheet

Postby kirkm » Wed Jun 01, 2011 4:38 am

Hi All,

Gee this is almost embarrasing :oops:

The last two versions 999888 aand 889 inherited a bug while developing the renumber routine.
It only affects Importing files. Fixed in this one -

Non-Whitburn
http://www.britburn.co.uk/files/spreads ... 99890m.zip

Whitburn
http://www.britburn.co.uk/files/spreads ... 9890mw.zip

My apologies!

Cheers - Kirk


Return to “The Spreadsheet”

Who is online

Users browsing this forum: No registered users and 1 guest