Interacting with Microsoft Excel

Vertex42 The Excel Nexus

Professional Excel Templates

Get Instant Access

Microsoft Excel is a great program for playing what-if scenarios with data because it lets you plug data and formulas into cells in whatever manner you wish. Excel isn't good, however, at managing large volumes of data. For large volumes of data, you need a database like Microsoft Access.

Microsoft Access can certainly do any math calculations that Excel can do. Playing what-if scenarios with data in Access is not so easy, though, because you need to get queries and/or forms involved. It's just plain difficult to experiment with what-if scenarios in Access.

Sometimes, the data you need for your worksheet might actually come from an Access database. For example, say you manage all your orders in an Access database. Every now and then you want to grab the total sales from all your orders into a worksheet and use that value to play around with your data.

You could, of course, just open Excel and type in the total sales value — or even copy and paste it from some form in Access. Optionally, you could automate the whole thing by creating a button on some Access form that opens the worksheet and plugs in the total sales amount for you. Look at an example of Automation that does just that.

Creating the worksheet

The first step is to create an Excel worksheet that contains a blank cell that gets its values from Access. Give the cell a name so that you can refer to that cell by name in VBA code. For example, in Figure 14-10, I create a worksheet named My Sheet.xls. Cell B3 in that worksheet is named FromAccess.

To name a cell or range in Excel, click the cell or select the cells that you want to name. Then type a name into the Name box (where FromAccess appears in Figure 14-10) and press Enter. For more information, search Excel's Help for name cells.

For the sake of example, say I save that worksheet in my My Documents folder with the name My Sheet.xls. In Windows XP, where I'm currently working under the user account name Alan, the complete path to that worksheet is

C:\Documents and Settings\Alan\My Documents\My Sheet.xls

That's important to know because VBA won't be able to find the worksheet without the complete path and filename.

Figure 14-10:

Sample Excel worksheet with a cell named From Access.

Figure 14-10:

Sample Excel worksheet with a cell named From Access.

Vba Multiplication Table Programming
Cell named FromAccess

Creating a query and a form

After you create and save the worksheet, you can close Excel and open Access. In Access, you need to create a query that can do the calculations and also create a form that can display the appropriate value to copy to the Excel sheet. For this example, I create a totals query in Access that totals all the sales for each product from tables named Products and Order Details in a sample database. Figure 14-11 shows that query, named Order Summary Totals Qry, in Design view (left) and Datasheet view (right).

Next, I create a form that's bound to Order Summary Totals Qry. In Design view, I set the Form's Default View property to Continuous Forms so that the detail band displays all the records from the underlying query. In the Form Footer of that form, I add a calculated control named GrandTotal that contains the expression =Sum([TotalSales]) to display the grand total of all the TotalSales values. I also create a button named ExportBttn. Figure 14-12 shows the form in Design (left) and Form (right) views.

As always, when using external applications, you must choose Excel's object library (Microsoft Excel xx.x Object Library) in the References dialog box before writing the code that follows.

Figure 14-11:

Sample query to total sales from orders in a database.

Figure 14-11:

Sample query to total sales from orders in a database.

Figure 14-12:

Sample form named Order Summary Form.

il OrderSijmmaiyFijrm : Form

# Form Header

OrderSurnmaiyForm : Form

¡Product Name ♦ Form Footer

□rand

Total

Total Sales

Product Name golden Whistle tBOCf'OQ

Lawn Fiamingb

Name: GrandTotal

Control Source: =Sum([TotalSales])

Writing the Excel code

With the query and form squared away, the next step is to write VBA code that can open the Excel sheet and copy the value in the control named GrandTotal to the cell named FromAccess in the My Sheet.xls worksheet. You can attach that to the ExportBttn On Click event. The entire procedure is shown here in Listing 14-3.

Listing 14-3: Procedure to Copy Access Form Data to an Excel Worksheet

Private Sub ExportBttn_Click()

'Declare a variable named MySheetPath as String. Dim MySheetPath As String

'Note: You must change the path and filename below 'to an actual Excel .xls file on your own computer. MySheetPath="C:\Documents and Settings\Alan\My Documents" MySheetPath = MySheetPath + "\My Sheet.xls"

'Set up object variables to refer to Excel and objects. Dim Xl As Excel.Application Dim XlBook As Excel.Workbook Dim XlSheet As Excel.Worksheet

'Open an instance of Excel, open the workbook. Set Xl = CreateObject("Excel.Application") Set XlBook = GetObject(MySheetPath)

'Make sure everything is visible on the screen. Xl.Visible = True XlBook.Windows(1).Visible = True

'Define the topmost sheet in the Workbook as XLSheet. Set XlSheet = XlBook.Worksheets(l)

'Copy GrandTotal to FromAccess cell in the sheet. XlSheet.Range("FromAccess").Locked = False XlSheet.Range("FromAccess") = Me!GrandTotal

'Boldface the new value (optional). XlSheet.Range("FromAccess").Font.Bold = True

'Save the sheet with the new value (optional). XlBook.Save

'Close the Access form (optional). DoCmd.Close acForm, "OrderSummaryForm", acSaveNo

'Clean up and end with worksheet visible on the screen. Set Xl = Nothing Set XlBook = Nothing Set XlSheet = Nothing End Sub

Even though the procedure is just an example, it illustrates many techniques for manipulating Excel and worksheets from Access VBA. Taking it one bit at a time, the first line, as always, names the procedure. In this case, the procedure is tied to the On Click event of ExportBttn, so the procedure name is

ExportBttn_Click():

Private Sub ExportBttn_Click()

In this example, the code will change the contents of an Excel workbook named My Sheet.xls, stored in the My Documents folder of a user named Alan on a Windows XP computer. The following statements create a string variable named MySheetPath and store the lengthy path name, C:\Documents and Settings\Alan\My Documents, in that variable. (The only reason I split it into multiple lines was to get the code to fit within the margins of this book):

'Declare a variable named MySheetPath as String Dim MySheetPath As String

'Note: You must change the path and filename below 'to an actual Excel .xls file on your own computer. MySheetPath="C:\Documents and Settings\Alan\My Documents" MySheetPath = MySheetPath + "\My Sheet.xls"

Opening Excel and a workbook

The next step in this sample procedure is to open Excel and the workbook. First, you need to declare some object variables so you'll have short names to use for these objects later in the code. An Excel workbook is actually two objects: The workbook as a whole is a Workbook object; each sheet (page) in the workbook is a Worksheet object. So you can actually set up three object variables:

I One for Excel (of the type Excel.Application) I One for the workbook (of the type Excel.WorkBook)

i One for a specific sheet within that workbook (of the type

Excel.Worksheet)

In the following lines of code, I assign each of these object types named Xl ,

XlBook, and XlSheet:

'Set up object variables to refer to Excel and objects. Dim Xl As Excel.Application Dim XlBook As Excel.Workbook Dim XlSheet As Excel.Worksheet

With the object variables declared, you can start assigning specific objects to them. The following statement opens an instance of Microsoft Excel and makes the object variable name Xl refer specifically to that open instance of Excel:

'Open an instance of Excel, open the workbook. Set Xl = CreateObject("Excel.Application")

After Excel is open, you can use the GetObjecti) function to open a specific file and assign it to the workbook object. The syntax is

Set objectVarName = GetObjectifilePathName)

where objectVarName is the object variable name declared as an Excel. Workbook (XlBook in this example), and filePathName is the complete path and filename of the worksheet to open (previously stored in the variable named MySheetPath in this example). The next statement in the procedure uses GetObjecti) to open the My Sheet.xls workbook:

Set XlBook = GetObject(MySheetPath)

One thing that you always need to be aware of is that when you use Automation (that is, VBA) to open an instance of a program, the program usually isn't visible onscreen. In the case of Excel, even an open workbook isn't necessarily visible. You have to specifically tell Excel to make its first document window (referred to as XlBook.Windows(l) in VBA) visible. The following lines of code ensure that Excel and the workbook are visible onscreen:

'Make sure everything is visible on the screen. Xl.Visible = True XlBook.Windows(1).Visible = True

The code still needs to set a reference to the first sheet on the open workbook. You can use the workbook's Worksheets collection with a subscript to refer to a specific sheet by number. For example, .Worksheets(l) refers to the first (topmost) page of a workbook — the one that's automatically visible when you first open the workbook. In the following statement, I assign that topmost sheet to the object variable XlSheet:

'Define the topmost sheet in the Workbook as XLSheet. Set XlSheet = XlBook.Worksheets(l)

Referring to worksheet cells from VBA

After you set a reference to the worksheet, you can use its .Range property to refer to any cell in the worksheet. There are several ways to use the property. You can refer to a single cell by its address in the worksheet. For example, assuming that the following object variable name XlSheet refers to an open worksheet, the following expression refers to cell A1 in that sheet:

XlSheet.Range("A1")

You can also specify a range by using the syntax objectVarName. Range(startCell -.endCell) where startcell and endCell are both cell addresses. For example, the following expression refers to the range of cells extending from cell B3 to cell F20:

XlSheet.Range("B3:F20")

If you've previously named a cell or range in the worksheet, you can use that name in place of a cell address. For example, this statement refers to a cell or range named FromAccess:

XlSheet.Range("FromAccess")

To change the contents of a cell in a worksheet, follow the cell reference by an = sign and the value that you want to store in that cell. For example, this statement stores the words Howdy World in cell C2:

XlSheet.Range("C2") = "Howdy World"

This statement stores the number 100 in cell C3:

XlSheet.Range("C3") = 100

To put a literal date in a cell, enclose the date in # symbols. For example, this expression stores the date 12/31/05 in cell C4:

To put the current date into a cell, use the built-in Date() function without the parentheses, as shown here, where cell C5 will receive the current date as its value:

XlSheet.Range("C5") = Date

To place a formula in a cell, use the standard Excel syntax but place the whole formula inside quotation marks. For example, the following statement places the formula =Sum(D4:D10) in cell D11 of the worksheet:

XlSheet.Range("D11") = "=Sum(D4:D10)"

Note that you must still precede the formula with an = sign, inside the quotation marks, to ensure that the new cell content is treated as a formula rather than as a string of text.

Getting back to the sample procedure, the worksheet is open and visible at this point in the procedure. The next step is to copy the value displayed in the

GrandTotal control on OrderSummaryForm into the cell named FromAccess in the worksheet. To play it safe, the following statements first make sure that the cell isn't locked (XlSheet.Range("FromAccess").Locked = False).

Then the next statement makes the content of the cell named FromAccess equal to the values stored in the form's GrandTotal control:

'Copy GrandTotal to FromAccess cell in the sheet. XlSheet.Range("FromAccess").Locked = False XlSheet.Range("FromAccess") = Me!GrandTotal

At this point, the job is actually complete. The procedure could end right there with an End Sub statement. Just to illustrate a technique for formatting cells from VBA, I added the following statement to boldface the FromAccess cell in the worksheet:

'Boldface the new value (optional). XlSheet.Range("FromAccess").Font.Bold = True

You'll see other techniques for formatting spreadsheet cells in a moment. For now, continue on with the sample procedure. The next statement simply saves the worksheet with the new data in place. Again, this step is entirely optional.

'Save the sheet with the new value (optional). XlBook.Save

Now that the spreadsheet is open and the FromAccess cell has its new value, it's really not necessary to keep OrderSummaryForm open. This statement closes that form:

'Close the Access form (optional). DoCmd.Close acForm, "OrderSummaryForm", acSaveNo

At this point, the procedure has finished its job, and there's really nothing left to do. Just to keep things tidy, the following statements break the bonds between the object variables and Excel objects. Think of this as the programming equivalent of tying up loose ends. Then the procedure ends.

'Clean up and end with worksheet visible on the screen. Set Xl = Nothing Set XlBook = Nothing Set XlSheet = Nothing End Sub

It's worth noting that only one statement in the whole procedure, XlSheet. Range("FromAccess") = Me!GrandTotal, actually copies the value from the form control to the Excel worksheet. All the code preceding that statement is just getting things open and onscreen so that the statement can execute. All that code is boilerplate for opening an instance of Excel and a workbook. As you'll see in the next example, you can use most of that code, as-is, to do something different — copy an entire table, or the results of any query, to a worksheet.

To copy a table or query results to an Excel worksheet, you first need to create a recordset. You can use the general techniques described in Chapter 7 to create a Select query that produces the records you want to export. Then you can copy and paste its SQL statement into code to create a recordset in code. Then, thanks to Excel's CopyFromRecordset method, the code can copy the whole recordset to any place in the worksheet with a single command.

For example, the hefty-looking chunk of code in Listing 14-4 copies all records produced by OrderSummaryQry (refer to Figure 14-11) to a worksheet named RecordsetSheet.xls (for lack of a better name). As intimidating as it all looks, it's mostly a bunch of copy-and-paste code that I just lifted from other procedures:

Listing 14-4: Example of Copying a Recordset to an Excel Worksheet

'We'll start by creating a recordset named MyRecordset.

Dim cnn As ADODB.Connection

Set cnn = CurrentProject.Connection

Dim MyRecordset As New ADODB.Recordset

MyRecordset.ActiveConnection = cnn

'Build the SQL statement (swiped from a query). Dim MySQL As String

MySQL = "SELECT [Product Name], Sum([Qty]*[Unit Price])"

MySQL = MySQL + " AS TotalSales FROM [Order Details]"

MySQL = MySQL + " INNER JOIN Products ON"

MySQL = MySQL + " [Order Details].ProductID ="

MySQL = MySQL + " Products.ProductID"

MySQL = MySQL + " GROUP BY Products.[Product Name]"

MySQL = MySQL + " ORDER BY Products.[Product Name]"

MyRecordset.Open MySQL

'Now MyRecordset contains records to be exported. 'Now for the Excel rigmarole.

'Define the path to the workbook, save it as MySheetPath. Dim MySheetPath As String

'Note: You must change the path and filename below 'to an actual Excel .xls file on your own computer. MySheetPath = "C:\Documents and Settings\All Users\Documents" MySheetPath = MySheetPath + "\Worksheets\RecordsetSheet.xls"

'Set up object variables to refer to Excel and objects. Dim Xl As Excel.Application Dim XlBook As Excel.Workbook Dim XlSheet As Excel.Worksheet

'Open an instance of Excel, open the workbook. Set Xl = CreateObject("Excel.Application")

Set XlBook = GetObject(MySheetPath)

'Make sure everything is visible on the screen. Xl.Visible = True XlBook.Windows(1).Visible = True

'Define the topmost sheet in the Workbook as XLSheet, Set XlSheet = XlBook.Worksheets(l)

'Copy the recordset to worksheet starting at cell B3. XlSheet.Range("B3").CopyFromRecordset MyRecordset

'Clean up and end with worksheet visible on the screen.

MyRecordset.Close

Set cnn = Nothing

Set Xl = Nothing

Set XlBook = Nothing

Set XlSheet = Nothing

I didn't put the code above between Sub...End Sub statements. You could just attach the code to any command button's On Click event to run it when you want it run. To show how the code isn't as intimidating as it looks, let me tell you how I wrote it.

First, before I even wrote any code, I created an Excel worksheet, added a heading in cell A1, did a little formatting, and saved it in my Shared Documents folder as RecordsetSheet.xls (for lack of a better name). So I'll want my VBA code to open that workbook.

I also created a query in Access that defines the records I want to copy to the worksheet. I made sure that query was working and also that its Datasheet view showed the exact data I want to copy to Excel. Then I closed and saved that query.

With the worksheet and query in place, I started writing the code. I knew that I'd need two major chunks of code here: one chunk to create the recordset, and another to open the worksheet. I already have boilerplate code (from Chapter 7) for creating an ADODB (ActiveX Data Objects Database) recordset, so I just did a quick copy and paste of that code into a new procedure.

Most of the copy-and-paste code was fine. I just had to delete all the MySQL = statements so I could build a new SQL statement. To get that new statement, I opened my previously defined and test query in SQL view and copied its SQL statement (minus the semicolon at the end) into the Code window.

In the Code window, I set about breaking that lengthy SQL statement into smaller chunks. (Note: I did that only to make it all fit within the margins of this book.) In the following code fragment, italics indicate the lines that I had to change. All other lines are straight from a copy and paste:

'We'll start by creating a recordset named MyRecordset.

Dim cnn As ADODB.Connection

Set cnn = CurrentProject.Connection

Dim MyRecordset As New ADODB.Recordset

MyRecordset.ActiveConnection = cnn

'Build the SQL statement (swiped from a query). Dim MySQL As String

MySQL = "SELECT [Product Name], Sum([Qty]*[Unit Price])"

MySQL = MySQL + " AS TotalSales FROM [Order Details]"

MySQL = MySQL + " INNER JOIN Products ON"

MySQL = MySQL + " [Order Details].ProductID ="

MySQL = MySQL + " Products.ProductID"

MySQL = MySQL + " GROUP BY Products.[Product Name]"

MySQL = MySQL + " ORDER BY Products.[Product Name]"

MyRecordset.Open MySQL

'Now MyRecordset contains records to be exported.

That takes care of the recordset problem. Now onto opening Excel and my workbook. This was another copy and paste job, this time from the procedure shown in the preceding example. The path and filename to the workbook will be different in this procedure, so I had to change those lines of code (again shown in italics here), but the rest is exactly what I pasted into the procedure:

'Now for the Excel rigmarole. 'Define the path to the workbook, save it as MySheetPath. Dim MySheetPath As String

'Note: You must change the path and filename below 'to an actual Excel .xls file on your own computer. MySheetPath = "C:\Documents and Settings\All Users\Documents" MySheetPath = MySheetPath + "\Worksheets\RecordsetSheet.xls"

'Set up object variables to refer to Excel and objects. Dim Xl As Excel.Application Dim XlBook As Excel.Workbook Dim XlSheet As Excel.Worksheet

'Open an instance of Excel, open the workbook. Set Xl = CreateObject("Excel.Application") Set XlBook = GetObject(MySheetPath)

'Make sure everything is visible on the screen. Xl.Visible = True XlBook.Windows(1).Visible = True

'Define the topmost sheet in the Workbook as XLSheet, Set XlSheet = XlBook.Worksheets(l)

At this point in the code, I have my recordset and I have my open worksheet. Because this procedure copies a recordset, the next statement is brand-new, but it's not too terribly difficult to figure out what it's doing even if you just read it and take a wild guess:

'Copy the recordset to worksheet starting at cell B3. XlSheet.Range("B3").CopyFromRecordset MyRecordset

The preceding statement is all that you need to copy a recordset to an open Excel worksheet. The B3 in the statement just moves the cursor to cell B3. Then CopyFromRecordset MyRecordset copies the recordset, starting at cell B3, into the worksheet. Nothing to it!

The rest of the code is just clean up, but that code is just a straight copy and paste from the other two procedures as well. So you see, as big and intimidating as the procedure looks, it really required very little typing or programming on my part. I used boilerplate code — that I know already works — to write at least 90 percent of the procedure! And that's the way you write code. Use what already works, when you can. Create new stuff only when you have to.

Running Excel macros from Access

Speaking of writing code by using what works and creating only what you have to, consider formatting an Excel worksheet. When you write a procedure that copies data from Access to Excel, you might be tempted to write some code to format the worksheet as well, but that would be tedious and unnecessary. That's because in Excel, you could just record a macro while you're formatting the sheet. Then save that macro, and run it from your Access VBA program.

For example, rather than add a bunch of code to either of the procedures above to format the worksheet, you could just open the worksheet and move the cursor to some known starting point — say cell A1. Then do the following:

1. Choose ToolsOMacroORecord New Macro.

2. Give the macro an easily remembered name (like Format Sheet) and then click OK.

3. Format your worksheet using whatever techniques you wish.

For example, you might

• Click a column heading (like B) and choose FormatOColumnO AutoFit Selection to size the column to its contents.

• Click a column heading, choose FormatOCells, and select some format like Currency.

• Move the cursor to a cell and type a formula.

While recording a macro, try to use the Name box as much as possible to move the cursor to a specific cell. That way, if you move the cell or range later, the macro will go to its new location rather than the old location.

4. Keep doing whatever you have to do to make the sheet look right, and then click the Stop Recording button.

To test the macro, choose ToolsOMacroOMacros from the Excel menu bar. Click the macro name and choose Run. The macro will execute. If all is well, you're done. You can close and save the worksheet in the usual manner and close Excel as well.

The recorded macro is also VBA code. If you choose ToolsOMacroOMacros, click a macro name, and then click Edit, the macro opens in the VBA editor. Each step in the procedure that you see was recorded while you were recording the macro.

Back in your Access VBA procedure, you'll most likely want to run the macro after your code copies new content to the worksheet — for example, just under the XlSheet.Range("FromAccess") = Me!GrandTotal statement in the Listing 14-3, or under XlSheet.Range("B3").CopyFromRecordset MyRecordset in Listing 14-4. The syntax for running a macro in the currently open workbook, from Access, is obj/ar.Run ("macroName ")

where objVar is the object variable to which you've assigned the Excel application (Xl in previous examples), and macroName is the name of the macro in the worksheet that you want to run. For example, from any Access VBA procedure, the following statement runs the macro named FormatSheet in the currently open worksheet (assuming the open worksheet contains a macro named FormatSheet):

Xl.Run ("FormatSheet")

If you want to put the cursor in a specific field before the macro executes, use the syntax objVar .Range("Address").Select before running the macro. For example, the following code positions the cursor to cell A1 and then executes the macro named FormSheet in the currently open workbook:

'Go to cell A1. XlSheet.Range("A1").Select

'Run macro named FormatSheet. Xl.Run ("FormatSheet")

Part VI

The Part of Tens

Life Goals Cartoons

M My hat For Dummies book would be complete without ▼ ▼ a Part of Tens? Ten is such a nice number to work with, given our ten fingers and all. The Ten Commandments of Writing VBA Code cover the main strategies that you want to adopt to avoid going crazy trying to get VBA to do anything. Then there are the top ten nerdy programming things you're most likely to want to do almost from Day 1 of using VBA. And check out the way-more-than ten shortcut keys listed here. I figured that if I were gonna stick some shortcut keys here, why limit it to ten? I'll splurge a little on those because they're easy.

Was this article helpful?

+1 0

Responses

  • Helena
    How to building player with excel vba?
    5 months ago
  • arto
    Can vba code written in excel be used in access?
    1 month ago

Post a comment