21 May 2013

Split Excel Worksheets to Individual Files

Earlier today I found myself putting a lot of data into a workbook with multiple worksheets within it, what I wanted to do was create an individual Excel file for each of the worksheets - perhaps you have been in a position like this yourself?

The good news, it's very easy to do.  I came across the VBA script below and don't take any credit for it - follow my short instructions below and you'll find this is a very straight forward procedure.  Note this is for Microsoft Excel and does not require any third party software.

  1. Create a new folder and save your master document in there.

  2. With the document open, name each sheet the name you'd like each file to be called.

  3. Once ready, hold the ALT key on your keyboard and press F11.

  4. On the Visual Basic window, select Insert > Module.

  5. Paste the VBA script shown below.

  6. Press the F5 key on your keyboard.

  7. Job done!

Sub Splitbook()

MyPath = ThisWorkbook.Path

For Each sht In ThisWorkbook.Sheets



ActiveSheet.Cells.PasteSpecial Paste:=xlPasteValues

ActiveSheet.Cells.PasteSpecial Paste:=xlPasteFormats

ActiveWorkbook.SaveAs _

Filename:=MyPath & "\" & sht.Name & ".xls"

ActiveWorkbook.Close savechanges:=False

Next sht

End Sub


Note on the fourth line from the bottom, change the .xls to .xlsx if you are working with a newer Excel format.

When I have some time, I'll create a video demonstrating this.

18 May 2013

Commodore Amiga 500 Plus Battery Removal

The first games console we owned as a family was a Sega Mega Drive in the early 90's - around the same time that the Commodore Amiga 500 was going out of production.  However, something has always interested me in the Amiga 500 and this week I decided to pick one up.

I sourced an Amiga 500 Plus locally using Gumtree and bought from the original owner with all the original boxes, manuals, receipts, games and also an Inkjet Printer.  It was sold as working and considering I've been looking at these for a while I was surprised at it's good condition - she kept it boxed and out of sunlight which I think contributes to this.  All in all, it's been well looked after and was exactly what I'm looking for.

My Commodore Amiga 500 Plus

Now, despite the fact I've never owned one does not mean I know little about it -I've done my research on these and understand the common faults and things to look out for. Before even looking for an A500 for sale I knew that if I came across the "Plus" model that potentially the internal clock battery could have leaked and caused corrosion of the motherboard tracks - for many of this age this is ruining the motherboards and although the tracks can be repaired it's often resulting in the units being disposed of.

So, sure enough - I got a Plus model.  The internal clock battery on the motherboard could have leaked but given the console worked fine I decided to take a punt without opening the console when buying it (The original warranty stickers and screws were all intact).  As I went ahead with the battery removal procedure I decided to photograph the process and write this short guide that shows you how to do it!

What you'll need;


Philips Screwdriver

Flathead Screwdriver

Wire Cutter

Yes, a wire cutter.  For those who would like to do a cleaner job may wish to remove the motherboard and de-solder the battery - but I took the quick and easy route.

Step One : Warranty Stickers

On the rear of the console and on the bottom there are two warranty stickers that are photographed below. Proceed to cut these as shown - the one that does not conceal a screw you may get away with but I'd recommend cutting it as it's for a good reason.



This sticker does not hide a screw

Step Two : Remove Top Cover Screws

The top cover is held on with six screws as shown below - working from the bottom of the console, remove these.



Step Three : Remove Top Cover

With the six screws removed, flip the console back to it's usual state and lift the cover off.


Step Four : Disconnect Keyboard Cable

With the top cover removed you will now see a single multicoloured cable for the keyboard. Disconnect this keyboard from the motherboard as shown below. Note, the keyboard cable can be accidently replaced the wrong way round - mines has three pins at one side and four at the other making this difficult but if this happens the console will not boot.




Step Five : Remove Keyboard

There are no clips or screws holding the keyboard in.  With the cable disconnected it can be simply lifted out.


Step Six : Remove EMI Shield

Before covering this step I'd like to mention that if the EMI Shield is not screwed back into place afterwards that the console will not boot - take care not to lose the screws.

Remove the four screws as shown below.


Note the four metal tabs shown below, using a flathead screwdriver lift these and have them sitting as straight as possible. When putting these back down, they can be pushed by finger.




With the four screws removed and four tabs raised, the EMI shield can be lifted off.


Step Seven : Locate Battery

With the EMI shield removed we have full visibility of the motherboard shown in the photo below.  Below that, I've highlighted something odd - have a look at the names of the chips on the board "Denise", "Paula" and "Fat Angus".



Step Eight : Remove Battery

The first thing I noticed with a sigh of relief was that the battery condition in my console looked pretty good but given that it could be 25 years old I wasn't taking a chance.  You can see this below, and observe the pins holding it into place - a single pin on one side and dual at the other.



So, armed with a pair of wire cutters - snip the connections to the motherboard and lift out the battery. Try and make it a clean cut and avoid damage to the PCB.



And that's it - job done.  No need to worry about that old battery leaking and damaging your console motherboard.  Before rushing and putting it together please consider a couple of extra points below.


While you have the console open, have a visual inspection of the capacitors.  What you are looking for is a nice flat top and no sign of leakage or crusting on the top.  Swollen/leaking capacitors can cause a range of minor or major faults that you may not have experienced yet, they are easily replaced if you are experienced with a soldering iron.

Three capacitors in clear view look pretty healthy here

If you see capacitors tilted like this one, it's not a concern and does not need corrected

Case Screwing

Do remember when putting the screws back into place that the plastics and casing are old and are likely become fragile - replace the screw slowly and when you feel tension don't tighten too hard. Take care not to split the plastic threads - replacement cases may be hard to come by and are no longer manufactured.

Thanks for reading my guide, I hope it helps you and keeps more A500+ consoles going around for a while yet!