redshoes.co.nz

Kiwi living in Bermuda. Homemade charcuterie, foraging, wine, cats and island life.

Drinks of Frasier: Part 1 – Stoli Gibson on the rocks with not two, not four, but three pearl onions.

Season 1, episode 3 – Dinner at Eight.

Martin, Frasier and Niles are having dinner at The Timber Mill:

Waitress: Hi, can I get you guys something from the bar?
Frasier: [weary] Oh dear God, yes.
Niles: I’ll have a Stoli Gibson on the rocks, with three pearl
onions.
Frasier: [firmly] If you bring him two, if you bring him four – he’ll
send it back.
Waitress: And for you?
Frasier: The same.

You can see the cocktail when Frasier gets his drink in this scene:

The Crane boys receiving their drinks

The Crane boys receiving their drinks

Recipe: Stoli Gibson with Three Pearl Onions

2 1/2 oz Stolichnaya vodka

1/2 oz dry vermouth

Three pearl onions to garnish

Shake or stir the vodka and vermouth with ice, and strain into an ice filled rocks glass. Garnish with three pearl
onions on a skewer.

Verdict
It’s kind of terrible. I have no problem with strong cocktails, and Stoli is actually my go-to for vodka based drinks. But it doesn’t work here – there is a bitterness that the vermouth and pearl onion juice doesn’t go anywhere near covering. We’ve had evidence that Niles is a martini man, but I can’t imagine him enjoying this. Even less when you consider the way it was probably mixed at The Timber Mill…

"Drinks of Frasier" part 1 - Stoli Gibson on the rocks with three pearl onions

“Drinks of Frasier” part 1 – Stoli Gibson on the rocks with three pearl onions.  Sorry Niles, this one isn’t for me.

Leaf macro

Taken largely blind with some cheap eBay macro tubes in Mum’s garden. Turned out interesting, certainly $12 worth of interesting anyway.6730165373_60788071f7_o

Exporting Access tables to Excel

There are pre-built Access functions to export data to Excel, such as the DoCmd.TransferSpreadsheet or DoCmd.OutputTo method, but anything but the most basic reports will require you to customise the Excel output more than these functions will allow. This code shows you how to create an instance of Excel and export the contents of the table into a new Excel workbook.  You can then format and manipulate the contents within Excel.

Public Sub ExportTableToExcel(strTableName As String)
     Dim oApp     As Object
     Dim xlSH     As Object
     Dim xlWB     As Object
     Dim rs       As Recordset
     Dim i        As Long

     'Check to see if Excel is open and get a handle to the Application
     On Error Resume Next
     Set oApp = GetObject(, "Excel.Application")
     On Error GoTo errorHandler
     'If oApp has not been set then there is no instance of Excel open.
     'Late binding the Excel application prevents issues with
     'compatibility as users may have different versions of Excel installed.
     If oApp Is Nothing Then Set oApp = CreateObject("Excel.Application")

     Set xlWB = oApp.Workbooks.Add
     Set xlSH = xlWB.Sheets(1)
 
     Set rs = CurrentDb.TableDefs(strTableName).OpenRecordset
 
    'Add in the header field names, the CopyFromRecordset command only
    'copies the data.
     For i = 0 To rs.Fields.Count - 1
          xlSH.Cells(1, i + 1).Value = rs.Fields(i).Name
     Next i

    'Paste in the data.
    xlSH.Range("A2").CopyFromRecordset rs

    'Format the worksheet
    With xlSH
        .Rows(1).Cells.Font.Bold = True
        .Columns.ColumnWidth = 100
        .Columns.AutoFit
        .Rows.AutoFit
    End With

exitRoutine:
    On Error Resume Next
    '-----------------------------------------------------------------------
    'When the Excel.Application object is created it is hidden by default.
    'You need to make sure it is set to visible as if there are errors or the
    'User runs the macro a number of times, they can end up with multiple hidden
    'Instances of Excel running in the background, which will only show up when
    'They are force quit.
    '------------------------------------------------------------------------
    oApp.Visible = True

    'Clear object variables and make sure recordset is closed
    rs.Close
    Set rs = Nothing
    Set xlSH = Nothing
    Set xlWB = Nothing
    Set oApp = Nothing
Exit Sub
errorHandler:
    MsgBox ("An error occured: " & Err.Number & " " + Err.Description), vbCritical, "Error"
    Err.Clear
    Resume exitRoutine
    Resume
End Sub

Download Sample Access database with code to export tables to Excel

Excel VBA Snake Game

Another quick one…

Excel snake game

Excel snake game

 Download Excel Snake

The Moon

Gibbous Moon picture, Sydney, 19-06-2011

A car alarm started going off at 5am this morning, forcing me out of bed… on the upside I had a chance to take pictures of the nearly full moon.  Click for full size.

Excel VBA Popup Calendar

Example of the popup calendar when used in a spreadsheet.
Example of the popup calendar when used inline on a spreadsheet.

The VBA editor does not have a calendar control available to it by default – however it is possible to use the MS Access calendar control.  This tutorial is on how to set up a calendar control that can be accessed via a UserForm, or inline from a spreadsheet.

Download the following for an example of how to use the control either to populate a cell on a worksheet, or a control on a form.

 

1. Setting up a reference to the MSCAL.ocx calendar control.

You will need to set up a reference to the MSCAL.ocx file.  Open the “References” dialog (Tools > References in the VBA editor).

Click “Browse” and select the MSCAL.OCX file (this should be located in the root directory of your Microsoft Office installation.

Click “Open”.  This will now appear in the “Available References” window as Microsoft Calendar Control xx.x.  Make sure the checkbox to the left is enabled.

 

2. Copy the Userform and Class modules to the workbook.

Copy the “PopupCalendar” class module and the “frmPopupCalendar” module into the target workbook (you can do this by dragging them to the target book in the VBA editor).

 

3. Add subroutines

Add the following function into a module – this is a will display the form, and return a date object.

Function showCalendar() As Date
Dim pcPopup As PopupCalendar
Dim dtDate As Date

‘create a new PopupCalendar object
Set pcPopup = New PopupCalendar
‘sets the date as today
pcPopup.setDateToday

‘This will show the PopupCalendar as a modal form, meaning that
‘execution of the function will not continue until the form is closed.
pcPopup.Show

‘As the form will now be closed, we can access the date that was selected
dtDate = pcPopup.calDate
Set pcPopup = Nothing

‘return the date
showCalendar = dtDate
End Function

4. Add Controls and Event Handlers

Add a Command Button control to the spreadsheet (use the Control Toolbox, accessed via the ‘View > Toolbars > Control Toolbox’ menu item.

Right-click on the control and select the “View Code” popup menu item.


Example of a worksheet with a Command Button control added

This will take you to the code for the Click event of the command button object.  Enter the following code:

Private Sub CommandButton1_Click()
‘Make sure the text before the underscore is the same as the name of your Command Button otherwise the event will not fire.

Dim dtDate As Date
‘Calls the showCalendar function we added before
dtDate = showCalendar()

If dtDate <> 0 Then
‘Note this will be the range that is populated with the date
‘The format statement is used to format the date returned (default is mm/dd/yyyy)
Me.Range(“B6”).Text = Format(dtDate, “dd/mm/yyyy”)
Else:
‘As will this
Me.Range(“B6”).Text = vbNullString
End If
End Sub

 

That’s it.  You can download the example Excel file here: PopupCalendar.xls

Excel Game of Life

Made this over lunchtime yesterday… it’s not pretty, but just seemed to be an obvious idea.  Only tested in Excel 2003.

Download Life.xls

Excel VBA Countdown Timer

Countdown.xls

Countdown preview

Excel VBA countdown clock.  Compatible with Excel 2007.

« Older posts

© 2019 redshoes.co.nz

Theme by Anders NorenUp ↑