Progress

Well almost a year on and I'm pleased that what I said in my first post still holds true. I have changed jobs to become a Data Manager following on from the progress I made in my previous role. I have since started using Access a lot and really improved my knowledge of SQL. Using VBA for access has also let me appreciate more the need for friendly interfaces. Not only does it mean that using software becomes easier for those not of a particularly technical mind but it also improves the quality of the data gathered. I've also written some small segments in VBScript which has been fun and helped me branch out and starting thinking about how I would write programs that aren't designed to function and enhance an existing program.

Recently I've started playing around with Betfair again but not with any particular strategy in mind apart from gaining enough points to be able to download a catalogue of historical data. The aim in the future is to then start building some models and test it using the data.

Converting .xlsx to .xls

Option Explicit
Sub Convert_to972003()
    Dim orgwb As Workbook
    Dim mypath As String, strfilename As String
    Dim nname As String

    '--> Error Handling
    On Error GoTo WhatHappened
   
    '--> Disable Alerts
    With Application
        .DisplayAlerts = False
        .ScreenUpdating = False
    End With

    '--> Specify location of workbooks
    mypath = "C:\xxx"
    strfilename = Dir(mypath & "\*.xlsx", vbNormal)

    '--> Check the specified folder contains files
    If Len(strfilename) = 0 Then Exit Sub
   
    '--> Start Loop, end when last file reached
    Do Until strfilename = ""
   
    '--> Open a workbook
        Set orgwb = Application.Workbooks.Open _
        (mypath & "\" & strfilename)

        '--> Create new Filename, Save in new File Format and Close
        nname = Replace(strfilename, ".xlsx", ".xls")
        orgwb.SaveAs mypath & "\" & nname, FileFormat:=xlExcel8
        orgwb.Close
        strfilename = Dir()
    Loop
   
    '--> Enable Alerts
    With Application
        .DisplayAlerts = True
        .ScreenUpdating = True
    End With
 
Exit Sub

WhatHappened: MsgBox Err.Description

End Sub

Does the job but the execution is very slow. 10 seconds for 20 small files locally, must be a quicker way than  Looping Open/SaveAs/Close, just don't know it yet..

Populating a workbook with sheets from a template

A simple little problem, populating a workbook with multiple sheets based on a template sheet, naming them appropriately and filling in some basic details:

Sub PopulateWB()
 
    Dim i As Integer
    Dim tmp As Worksheet, lstws As Worksheet
    Set tmp = Sheets("Template")
    Set lstws = Sheets("Sheet1")
 
    Application.ScreenUpdating = False
 
    '--> Set Number of Bundles Below
    '--> Create worksheets for each bundle and complete basic details
    For i = 2 To x
        tmp.Copy Before:=Sheets("Temporary PSD Report")
        ActiveSheet.Name = lstws.Cells(i, 1)
        ActiveSheet.Cells(1, 2) = lstws.Cells(i, 1)
        ActiveSheet.Cells(2, 2) = lstws.Cells(i, 2)
    Next
 
    Application.DisplayAlerts = False
    lstws.Delete
    Application.DisplayAlerts = True
 
    MsgBox ("Now Save This Workbook for the appropriate month and delete the populate code")
 
    Application.ScreenUpdating = True
 
End Sub

Printing selected data from multiple worksheets

Was asked to write a macro to print out data from every sheet in a workbook, simple enough the only problem being the data was in chunks in each workbook and of variable size. I came up with this to select only the top chunk of data in each sheet, copy it to a temporary sheet where they are arranged and printed, then deleting the temporary sheet.

Option Explicit
Sub PrintTopRangeWS()
    Dim ws As Worksheet, tmp As Worksheet
    Dim LR As Long, LCol As Long
    '--> Create temporary sheet to sort data
    Application.DisplayAlerts = False
    On Error Resume Next
    ThisWorkbook.Sheets("Temp").Delete
    ThisWorkbook.Sheets.Add.Name = "Temp"
    Application.DisplayAlerts = True
    
    Set tmp = Sheets("Temp")


    '--> Define top range and copy to temp sheet
    For Each ws In ThisWorkbook.Worksheets
        With ws
            If Not ws.Name = "Temp" Then
            LR = .Range("A1:A" & Rows.Count).End(xlDown).Row
            LCol = .Cells(, Columns.Count).End(xlToLeft).Column
            .Range("A1" & ":" & Split(Cells(1, LCol).Address, "$")(1) & LR).Copy     
            tmp.Cells(Rows.Count, "A").End(xlUp).Offset(1, 0).PasteSpecial _
            Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
            End If
        End With
    Next
    'Print consolidated data
    tmp.PrintOut
    
'Delete temporary sheet
    Application.DisplayAlerts = False
    tmp.Delete
    Application.DisplayAlerts = True
End Sub

Getting Started

How I developed an interest in coding

I started a new jon in January this year as an admin assistant. I took the job to escape from the unsociable hours of bar work and for a slightly higher rate of pay.  Initially I did what I was asked to and didn’t really question the methods by which things were done having never worked in an office environment before. Very quickly I realised that many of the tasks performed every month dealing with figures were done in an incredibly inefficient way.

For example, a spreadsheet arrives every month containing about 600+ rows of data; each row is identified by a 4 digit code. I was given a few lists of these identifying numbers and asked to find and export the corresponding data in the monthly spreadsheet to new sheets. Their current method of doing this before I arrived involved a printed list of the identifying numbers, a ruler, and manually looking through the excel file before copying and pasting individual lines to a new file.  I knew that there must be a more efficient way to do this; the only problem was I had no idea how, having no coding experience at the time. I started googling the problem and discovered the ozgrid forums. Very quickly I found some relevant code and set about trying to use it for my monthly spreadsheet. Eventually after cobbling together bits of code from various sources I had semi workable solution. I set up my list of identifying numbers in sheet 2 of the workbook. If the numbers matched the cell in the first sheet was highlighted in red. Using macros I managed to write code to sort the highlighted cells to the top of the sheet before I then copied them to a new sheet manually. At the time this seemed like a fantastic achievement given the way it was done before. After a little more thought I realised that this was also an inefficient way of doing this task.

I brought up the subject with my friend Shaun, a developer, over a drink on a Friday night. We had a lengthy discussion about the whole idea of coding and I was pointed in the direction of Stack Overflow. I popped my code up and received some excellent feedback very quickly from Siddharth Rout (http://siddharthrout.wordpress.com/about/). I couldn’t believe the quality of the reponse I got. Immediately I knew this was a much better way to learn coding than googling problems and trying to cobble together code from different resources. Since then I’ve started from the beginning and begun teaching myself VBA for excel at the office and in my own time. Shaun also pointed me in the direction of www.codeacademy.com and www.udacity.com so I’m also getting to grips with Javascript and Python.

Initially I just saw this job as a way out from a job I hated but it’s allowed me to realise a passion. Coding is the first thing I’ve been generally interested in and eager to learn more about for a long time and I can’t see my enthusiasm disappearing anytime soon.