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

sht.Copy

ActiveSheet.Cells.Copy

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

b222d-vba





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.











photo18-05-2013114521
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;


Knife

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.


photo18-05-2013114605


photo18-05-2013114650











photo18-05-2013115102
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.


photo18-05-2013115838_mod


photo18-05-2013115912_mpd


Step Three : Remove Top Cover


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


photo18-05-2013120117


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.


photo18-05-2013120131mod


photo18-05-2013120205


photo18-05-2013120224


Step Five : Remove Keyboard


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


photo18-05-2013120309


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.


photo18-05-2013120407_mod


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.


photo18-05-2013120542_mod


photo18-05-2013120613


photo18-05-2013120719


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


photo18-05-2013120815


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".


photo18-05-2013121136_mod


photo18-05-2013121206_mod


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.


photo18-05-2013121236


photo18-05-2013121243


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.


photo18-05-2013121950


photo18-05-2013122053


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.


Capacitors


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.











photo18-05-2013122228
Three capacitors in clear view look pretty healthy here











photo18-05-2013122154
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!


photo18-05-2013123346