I’m not sure if it’s just me, or if it is a phenomenon common to Access programmers in general, but I find myself constantly exporting data to Excel, regardless of who the client might be.  I get hired to create a database so that they are not relying on clunky attempts at database emulation in Excel – yet when it’s all said and done, there is nearly always a justifiable requirement for an output to an Excel spreadsheet.

In the next few installments, I’ll show how I use some handy user-defined functions to easily place a button on just about ANY form with a RecordSource to export the form’s data to a nicely formatted Excel spreadsheet with only a couple of lines as demonstrated below (plus any required validation and/or standard error trap code you may want to include).

Set rst = Me.RecordsetClone

Call ExportRSCloneToExcel(rst)

The next few blogs will review the following functions:

• GetExcel() – covered in this blog

• WriteFieldsToExcel() – cycles through a recordset’s field list and adds it to a spreadsheet

• FormatReportGeneric() – does some basic, generic formatting of an Excel table

• ExportRSCloneToExcel() – see explanation above

It all starts with the GetExcel function, a simple function that allows me to create an Excel spreadsheet with a single line like the following:

Set objXL = GetExcel(FileName:=”C:\MyPath\MyFile.xlsx”,CreateNew:=True)

This simple line of code creates a new Excel object and adds a workbook to it.  If the optional CreateNew argument is False or omitted, the workbook named in the FileName argument is opened.  If the file doesn’t exist, it simply raises a 1004 error to the calling procedure.

I use this often to open Excel workbooks so that I can retrieve and edit data from them.  In this series, we’ll assume we are creating new workbooks in each instance, but the code would work equally as well with existing workbooks to which you want to add data.

Let’s begin with the GetExcel() function.  Note that you'll have to set a reference to the Microsoft Excel xx Object Library (xx being your installed version) under Tools==>References in your VBA Project window:

Function GetExcel(Optional FileName As String, Optional CreateNew As Boolean = False) As Object

    Dim objXL As Object    

    On Error GoTo errHandler

   ' Create an Excel Object

    Set objXL = CreateObject("Excel.Application")

    If FileName = "" Or CreateNew Then ' For new workbooks, add one to the collection


    Else ' For existing workbooks, open the one by the name you passed

        objXL.Workbooks.Open FileName

    End If

    Set GetExcel = objXL ' Return the new workbook

    Exit Function


    Err.Raise Err.Number ' Any issues get passed back up to the calling procedure

End Function

The next installment will review the WriteFieldsToExcel() function.  

Until then, happy coding!

The previous post discussed how single quotes (like apostrophes) can confuse SQL code and cause syntax errors.  Single quote errors are perhaps more common (emphasis on "perhaps") but they do not hold an exclusive right to the Heartache and Discontent trophy. Double quotes appear in text strings, as well, and though the issues they cause are similar, they deserve separate attention.


For this second example, suppose you have a bit of code that includes text from a Comments field.  Because single quotes are common, especially in comments where contractions (don't or isn't) and possessive nouns (Fred's or customer's) are used, you may choose to wrap such text strings in double quotes.  MS Access allows double quotes in SQL.  (SQL Server... not so much.)

strSQL = "SELECT * FROM tblCustomers WHERE Comments= " & """" & me.txtComments & """"

First of all, I typically create a public constant in my declarations module for the entire project that includes the following:

Const Q As String = """"

Thus I can rewrite my code this way:

strSQL = "SELECT * FROM tblCustomers WHERE LName = " & q & me.txtComments & q

Let's further suppose that the comment includes the following:

 The customer quoted Tom as being "a top-notch salesman"

Now we have a similar issue as in the last post.  The resultant SQL code has an early termination of the quote and then it has a few incorrect command words and then another opening quote without a closing quote.  We've got more errors than a little league game.

The FixQuotes() function, used in conjunction with the Quote() function, can help here, too.

The FixQuotes() function escapes the single double quote using four double quotes.  SQL sees the four double quotes as an actual double quote (again, NOT the same as two single quotes) instead of the termination of a quote string.  

Thus we can rewrite our code to look like this:

strSQL = "SELECT * FROM tblCustomers WHERE Comments = Quote(me.txtComments, true)

Note the inclusion of the optional boolean argument.   This tells Quote (and, in turn, FixQuotes) to replace double quotes instead of single quotes.  

I hope this helps!  Happy coding...

Fancy Message Box

How many times have you wished that Microsoft had not done away with the fancy formatted Message Box that we loved in Access 97?

I cannot count the times, myself.

Here is my solution - a replacement function that performs an Eval() on the classic MsgBox() function as a string expression. The Eval function then returns the formatted message box like we all love and miss (at least those of us who used it all the time).

The code is fairly simple. Taking a cue from a forum entry on the Microsoft Access Help Center, I created the function below that replaces the Prompt with appropriate code to create the formatted message.

A couple of important notes:

First, I used the tilde (~) instead of the at (@) sign because e-mail addresses are often included in my messages.  Substitute your favorite seldom-if-ever-used character if the tilde doesn't work for you.

Second, I have VERY often encountered issues with single quotes in string variables.  Apostrophes appear in contractions, Irish names, possessive nouns, and a variety of other situations that may end up as part of a text string that you want to put in quotes.  The Quote() function is a simple one-liner that references another short function called FixQuotes().  These two functions (also included below) ensure that quotes inside quotes don't confuse the issue and cause premature termination of quoted text because of a spurrious quote in the middle of your text string.

Happy coding!

' Replace MsgBox with a formatted version like the old-style messages

' The message box at the top used the following code:

' Msg_Box "Formatted message boxes were great!~Why did Microsoft get rid of them?", vbOKOnly Or vbExclamation, "FORMATTED MESSAGE BOX"

Function Msg_Box(Prompt As String, Optional Buttons As VbMsgBoxStyle, Optional Title As String = "Microsoft Access")

    Dim strPrompt As String
    Dim aPrompt As Variant

    ' If there is a delimiter, then replace the delimiter with the appropriate symbols
    ' otherwise, just send the prompt out as it is.

    If InStr(Prompt, "~") > 0 Then
        aPrompt = Split(Prompt, "~")
        strPrompt = aPrompt(0) & "@" & aPrompt(1) & "@@"
        strPrompt = Prompt
    End If
    ' Wrap the prompt and other parameters in an Eval to get the appropriate
    ' formatted output. The Quote function ensures that any apostrophes or
    ' other single-quotes are handled without crashing the output.

    Msg_Box = Eval("MsgBox(" & Quote(strPrompt) & "," & Buttons & "," & Quote(Title) & ")")

End Function

Function Quote(StringToQuote As Variant, Optional DoubleQuote As Boolean = False) As String

    Quote = "'" & FixQuotes(StringToQuote, DoubleQuote) & "'"

End Function

Function FixQuotes(strToFix As Variant, Optional DoubleQuote As Boolean = False) As String

    If DoubleQuote Then
        FixQuotes = Replace(Nz(strToFix), """", "'")
        FixQuotes = Replace(Nz(strToFix), "'", "''")
    End If

End Function

I mentioned the FixQuotes function in the last blog, but it deserves a bit more focused attention.  The issue of quotes in text strings that ultimately get passed as variables to query strings is one that has caused much consternation in my experience. Since single quotes and double quotes can both appear in code for various reasons, the FixQuotes() function will get TWO doses of attention.  


For this first example, suppose you have a bit of code that constructs a WHERE clause based on a customer's last name:

strSQL = "SELECT * FROM tblCustomers WHERE LName = '" & me.txtLName & "'"

This snippet will work swimmingly as long as the customer's name isn't something like "O'Neil".  In such a case, the resultant SQL code that gets generated will have a critical flaw that will cause a syntax error:

SELECT * FROM tblCustomers WHERE LName = 'O' Neil'

The statement now tells the query engine to look for someone who's last name is "O" and then perform the "Neil" command.  Oh, and then open another quote, but don't close it.

The query engine responds with, "Wha...??"  or some similar "I can't do that, Dave" -type response.

The FixQuotes() function, used in conjunction with the Quote() function, attempts to mitigate such issues.   First, let's talk about the FixQuotes() function.

Function FixQuotes(strToFix As Variant, Optional DoubleQuote As Boolean = False) As String

    If DoubleQuote Then

        FixQuotes = Replace(Nz(strToFix), """", "'")


        FixQuotes = Replace(Nz(strToFix), "'", "''")

    End If

End Function

The FixQuotes() function simply escapes the single quote using two single quotes.  SQL sees the pair of single quotes (NOT the same as a single double-quote!!) as an actual single quote instead of the termination of a quote string.  We will address the optional DoubleQuotes argument in the next segment.

Function Quote(StringToQuote As Variant, Optional DoubleQuotes As Boolean = False) As String

    Quote = "'" & FixQuotes(StringToQuote, DoubleQuotes) & "'"

End Function

The Quote() function is just a handy programming keystroke saver that incorporates the FixQuotes() function to make it easier to wrap strings in quotes.  Thus:

FixQuotes("O'Neil") returns O''Neil and Quote("O'Neil") returns 'O''Neil'

For example, the following snippets show the graduating simplification of the code:

x = "O'Neil"

This one returns an error

strSQL = "SELECT * FROM tblCustomers WHERE LName = '" & x & "'" 

This one is OK, but a bit cumbersome:

strSQL = "SELECT * FROM tblCustomers WHERE LName = '" & FixQuotes(x) & "'"

Quote() wraps the string variable in quotes, fixing any quotes in the string

strSQL = "SELECT * FROM tblCustomers WHERE LName = " & Quote(x)

Adding these two functioins to your utility module will save you from SQL errors caused by spurrious quotes in your text strings plus simplify and streamline your code.  Look for Part 2 where we'll get into the double quote replacement aspect of these functions.

There are quite a few tutorials on the web about recursive functions - I've even posted one myself - that use the old standby Factorial of 5 explanation. It's a great example and illustrates the concept very well.

OK, fine, so we can use recursive functions to do factorials, but there are calculators and pre-written functions that handle that, so why do I need to understand recursion?

Minesweeper opening grid

Opening game

Minesweeper after clicking a square

After first move

What might help is another less common real-world example. Let's take a look at Microsoft's® standby game, Minesweeper®. It's a game that most people are at least somewhat familiar with. This quick tutorial will hopefully help you see how recursion works in the code behind this great little game.

Minesweeper starts it's beginner game with a 9x9 grid of squares. When the user clicks on the center square (red "X"), the program must look at all 8 adjacent squares (in yellow) and determine how many mines surround that square. It happens that no matter where you click first, the eight surrounding squares will not contain mines. It's just the way the game is programmed. Live with it. However, many of those squares may have one or more mines surrounding them. Now we begin to get recursive...

Here's how it works:

The eight squares surrounding the red "X" each have squares surrounding them. Each one of them must be examined in turn to see how many mines surround them. In the case of our example, the square directly below the red "X" had zero mines surrounding it. Now the program must check each of the squares surrounding that square.

So... first we must have a function that checks surrounding squares for mines. It could happen that one of the squares we're checking needs to have its surrounding squares checked for mines. The same function that checks adjacent squares for mines needs to include a function call to check adjacent squares for mines - it may need to call itself.

The code is a bit more involved than what we can go into in a short blog, but here is a recap of the concept:

If one started on the square with the black dot, coordinates (5,3) in an (x,y) grid, one would need to check square (4,2) to see if it's a bomb. If it isn't, one would need to see how many bombs surround (4,2), so the program would call itself with new coordinates. This time, the program would check (3,1) to see if it's a bomb. If it isn't, the program would need to see how many bombs surround (3,1), so the program would call itself with new coordinates to check (2,1) to see if it's a bomb. If it IS, it would increment the counter in the stack iteration for (3,1) and move to (4,1) to check to see if it's a bomb. Once all the squares surrounding (3,1) have been checked and all the bombs surrounding it accounted for, we can return a value to the stack iteration for (4,2) and move on to (5,2) which is directly above the black dot and start the whole process over again.

Yes, it's more complex than that, really. A lot of head-scratching went into programming MineSweeper, simple game though it is. It still makes for a good example of how Recursive Functions work and what they can be used for besides finding factorials.