The Challenge

Microsoft Access excels (no pun intended) at form and report creation. If you need to generate user-friendly reports and statements from your data, Access has a great collection of tools that enables you to design whatever template you need and fill it with data from your tables. Even when I need to use a client’s pre-printed forms or letter templates, it’s a relatively simple matter to import the template as an image into an Access report, overlay the proper Access data fields and generate great looking printouts as needed.

As companies continue to go paperless, however, sometimes printed reports aren’t the answer and there are a number of alternatives. Access can send its output to PDF like any other program and even generate e-mails with the PDF as an attachment. If the form is supplied in Word, you can still import it into Access as I described above or use the mail merge functions in Access to complete merge fields in the original Word file.

Another situation I ran into recently was one where the client sent me a form that had been designed in an Excel worksheet. This was not done with Excel’s data entry form features but simply by merging and formatting the necessary worksheet cells and adding logos so that the worksheet itself could be filled in with the necessary information. The form itself was too big to be printed on standard paper and was obviously meant to be submitted electronically. Access data can be easily transferred to Excel by writing to a new file but, in this case, specific values needed to be placed in specific worksheet cells so I started looking into how to do that with Access and VBA.

The Process

Filling out the information in the worksheet is only part of the process. We’re working with a template that should remain blank and not be overwritten so the program needs to make a copy first. The entire algorithm should be something like this.

1. Copy the Excel workbook containing the template to another file and name that file with some identifying information.
2. Open an instance of Excel along with the new workbook.
3. Insert the required information into specific cells in the worksheet containing the template.
4. Optionally, display the worksheet.
5. Save the Excel workbook with the new data.

For the example, I’ll use a very simple Excel worksheet form showing some basic information for a customer including their contact info and the date of their first order.

Usually, you might fill this with data from a table query but, for simplicity, I have a form in Access where I can type some test data to fill into the worksheet with a command button that will start the process.

The Code

Let’s start with the click event of the command button on the form. We just want the user to fill in the information and send it to Excel with one click.

Because we’ll be calling Excel from the VBA environment and working with its properties, we need to add a reference to the Microsoft Excel Object Library by selecting Tools > References from the top menu in VBA and checking the box next to the item.

If you’re designing this to be run on multiple computers, you will need to ensure that each machine is running the same version of Access or at least test the program thoroughly on each computer to ensure that any differences in the version or the location of the referenced code don’t cause it to fail. It is possible to do this without setting a reference by using late binding as explained later but there’s a pretty good chance that, if you’re using Office 365 in your organization, your computers are all on the latest version.

In the click event for the form’s command button, we can start adding some code. We’ll start by declaring a few Excel objects to work with.

Private Sub cmdExcel_Click()

Dim appExcel As Excel.Application
Dim wb As Excel.Workbook
Dim ws As Worksheet
Dim strFile As String

This demonstrates part of the object model for Excel with the actual application at the top. The application can host multiple workbooks, each of which can contain many individual worksheets. We’ll be working with one of each. Now we need to set these objects to the actual workbook.

strFile = "C:\Data\CustomerRecord" & Me.txtCustomer & ".xlsx"
FileCopy "C:\Data\CustomerRecord.xlsx", strFile

Set appExcel = New Excel.Application
Set wb = appExcel.Workbooks.Open(strFile)
Set ws = wb.Sheets(1)

The file copy operation is quick and dirty here; I’m just adding the customer name from the Access form to the file name and copying it with the FileCopy method. You can use whatever naming is best for you and you could also set another reference to the Microsoft Scripting Runtime and declare a new FileSystemObject which will give you extra features to manipulate file names and verify directories if needed. If your template file is already open in Excel, you will probably get a file permission error.

Then the appExcel object is set to a new instance of Excel itself which will open Excel in the computer’s memory but won’t show it immediately. The workbook object opens a specific workbook and the worksheet object selects the first worksheet in that workbook. If you’ve done a certain amount of programming, you might be used to item numbering being zero-based but, in this case, the worksheets are numbered starting with 1.

Once that’s in place, it’s time to start writing values to Excel by referencing the worksheet’s collection of cells.

ws.Cells(4, 4).Value = Me.txtCustomer
ws.Cells(6, 4).Value = Me.txtAddress1
ws.Cells(8, 4).Value = Me.txtAddress2
ws.Cells(10, 4).Value = Me.txtCity
ws.Cells(12, 4).Value = Me.txtState
ws.Cells(14, 4).Value = Me.txtZip
ws.Cells(16, 4).Value = Me.txtFirstOrder

appExcel.Visible = True

wb.Save

Note that the cells are referenced by coordinate and not by the standard cell references we’re used to when using Excel. So, by counting down and then to the left, cell D4 in our example worksheet shown above becomes cell 4, 4.

By setting the Visible property of the Excel application to True, you can actually see the new worksheet. Then you can save the changes to the workbook by using it’s Save command in VBA.

For safety, you should release the object references in VBA after everything is finished to ensure that the Excel instance does not stay resident in memory after you close the workbook.

Set ws = Nothing
Set wb = Nothing
Set appExcel = Nothing

To review, here is the complete code:

Private Sub cmdExcel_Click()

Dim appExcel As Excel.Application
Dim wb As Excel.Workbook
Dim ws As Worksheet
Dim strFile As String

'Copy template file to new file.
strFile = "C:\Data\CustomerRecord" & Me.txtCustomer & ".xlsx"
FileCopy "C:\Data\CustomerRecord.xlsx", strFile

'Set objects
Set appExcel = New Excel.Application
Set wb = appExcel.Workbooks.Open(strFile)
Set ws = wb.Sheets(1)

'Write to cells
ws.Cells(4, 4).Value = Me.txtCustomer
ws.Cells(6, 4).Value = Me.txtAddress1
ws.Cells(8, 4).Value = Me.txtAddress2
ws.Cells(10, 4).Value = Me.txtCity
ws.Cells(12, 4).Value = Me.txtState
ws.Cells(14, 4).Value = Me.txtZip
ws.Cells(16, 4).Value = Me.txtFirstOrder
appExcel.Visible = True

'Save
wb.Save

'Cleanup
Set ws = Nothing
Set wb = Nothing
Set appExcel = Nothing

End Sub

That’s not too scary, is it? It’s actually pretty simple for what it’s doing between two different applications – the most trial and error will probably be getting the cell references right on larger forms but that’s what testing is for.

Late Binding

If you’re working on different machines and have problems because of different versions of Access, you could use late binding. This means that you declare the Excel objects simply as generic objects and ask the VBA complier to trust you that Excel is somewhere on the system and will respond when called to. Again, make sure you test this thoroughly on all target machines.

To use late binding, simply replace some of the declarations in the above code with these.

Dim appExcel As Object
Dim wb As Object
Dim ws As Object

Set appExcel = CreateObject("Excel.Application")

The rest of the code stays the same but you won’t get any Intellisense dropdowns when calling object methods like wb.Save because VBA doesn’t have anything to reference for that. Again, you’re just asking it to trust you that everything will work when the program runs. On some systems, you might notice that performance is a little slower.

Comeau Software Solutions can help you with your Microsoft Access project or find alternative solutions for your database needs. If you need assistance and would like more information, please contact us for details.