Archive for May, 2013

Split an Excel File – One file per worksheet

May 29, 2013

* Save the following macro into your personal macro workbook (PERSONAL.XLS), you may need to enable the developer ribbon first.

Sub SaveEachWS()

Dim ws As Worksheet

Dim loc As String

Dim loc2 As String

loc = InputBox(“Location To Save Files eg  c:\folder”)

loc2 = loc

If Mid(StrReverse(loc), 1, 1) = “\” Then

loc = StrReverse(Mid(StrReverse(loc), 2, 9999))

End If

For Each ws In ActiveWorkbook.Worksheets

ws.Copy

loc2 = loc & “\” & ws.Name & “.xls”

ActiveWorkbook.SaveAs Filename:=loc2

ActiveWorkbook.Close

Next ws

End Sub

*Open the file you want to split and run the macro SaveEachWS from the macro menu. You will be asked to provide a location, this location must exist, the macro won’t create folders on the fly.

Aggregating fields within a record

May 29, 2013

If for example you want to find a max across several fields within a record one way of doing it is via a PIVOT operation, here is another (possibly more intuitive) option

what we are doing here is turning the columns we want to aggregate into rows and then aggregating by this new column

DECLARE @data TABLE (Name VARCHAR(10),field1 int, field2 int, field3 int)

insert into @data VALUES

(‘Paul’,1,5,3),
(‘Scott’,41,35,32),
(‘Dan’,12,17,3)

SELECT Name,max(a) AS max_of_fields_1_to_3
FROM @data a
CROSS APPLY (VALUES (field1),(field2),(field3)) b(a)
GROUP BY Name