Figure

Vertex42 The Excel Nexus

Professional Excel Templates

Get Instant Access

Sample query to total sales from orders in a database.

Field 'Table Total pu

■ShoWv Criteria

ProducEWa.me Prosta^. ' Group By Ascending 0

iTotSiSafes: [QtyHUnitFrtce]

Order SLimrrraryTotals Qry

— X

p-Ti rluctN i rnr j

Trjnli'" - 1

50pk Audio tfi-R

$959.76

50-pk Floppy rji^k'7

SÖSÜI

Budget MP3Player

£10. OP

Golden Whistle

Sioo.oo

Kozmik. Video-Camera

S 1,000.00

Lawn Kf'amingo

$¿9.99''

Lucky Rabbits Foot

$79.90 I

NLJ clear Pencil.sHarpen

■SteCgfs' 1

OldTime StockTicker

5500.00

Scanner cable

59.98 1

WayCecrl Scanner

589.99

H lof Ii F M -

Next, we create a form that's bound to Order Summary Totals Qry. In Design view, we 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, we add a calculated control named GrandTotal that contains the expression =Sum([TotalSales]) to display the grand total of all the TotalSales values. We also create a button named ExportBttn (the Export To Excel button, as shown in Figure 14-12). Figure 14-12 shows the form in Design (left) and Form (right) views.

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

Figure 14-12:

Sample form named

Order-Summary-Form.

Figure 14-12:

Sample form named

Order-Summary-Form.

Vba Opening Forms Excel

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 MySheet.xlsx worksheet. You can attach that to the ExportBttn On Click event. The entire procedure is shown in Listing 14-3.

Listing 14-3: Copying 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 .xlsx file on your own computer. MySheetPath="C:\MySheet.xlsx"

'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.

(continued)

Listing 14-3 (continued)

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 property of ExportBttn, so the procedure name is ExportBttn_Click():

Private Sub ExportBttn_Click()

In this example, the code changes the contents of an Excel workbook named MySheet.xlsx, stored in the computer's C:\ folder. The following statements create a string variable named MySheetPath and store the pathname, C:\ MySheet.xlsx, in that variable. (The only reason we 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 .xlsx file on your own computer. MySheetPath = "C:\MySheet.xlsx"

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 that you 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 set up three object variables:

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

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

Excel.Worksheet)

In the following lines of code, we 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 GetObject() function to open a specific file and assign it to the workbook object. The syntax is

Set objectVarName = GetObject(filePathName)

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 GetObject() to open the MySheet.xlsx 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 on-screen. 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 on-screen:

'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, that topmost sheet is assigned 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 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/07 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 receives 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 complete. The procedure could end right there with an End Sub statement. Just to illustrate a technique for formatting cells from VBA, we added the following statement to boldface the FromAccess cell in the worksheet:

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

You see other techniques for formatting spreadsheet cells in a moment. For now, continue 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, copies the value from the form control to the Excel worksheet. All the code preceding that statement is just getting things open and on-screen so that the statement can execute. All that code is boilerplate code for opening an instance of Excel and a workbook. As you 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.

Copying a table or 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 the Excel 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 Order Summary Totals Qry (refer to Figure 14-11) to a worksheet named RecordsetSheet.xlsx (for lack of a better name). As intimidating as it all looks, it's mostly a bunch of copy-and-paste code that we just lifted from other procedures.

Listing 14-4: 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 [ProductName], Sum([Qty]*[UnitPrice])

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.[ProductName]"

MySQL = MySQL & " ORDER BY Products.[ProductName]"

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 .xlsx file on your own computer. MySheetPath = "C:\RecordsetSheet.xlsx"

'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

We didn't put the preceding code 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 us tell you how we wrote it.

344 Part V: Reachin9 0ut with VBA

First, before we even wrote any code, we created an Excel worksheet, added a heading in cell A1, did a little formatting, and saved it in our C:\ drive as RecordsetSheet.xlsx (for lack of a better name). So we want our VBA code to open that workbook.

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

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

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

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

'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 [ProductName], Sum([Qty]*[UnitPrice])"

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.[ProductName]"

MySQL = MySQL & " ORDER BY Products.[ProductName]"

MyRecordset.Open MySQL

'Now MyRecordset contains records to be exported.

That takes care of the recordset problem. Now move on to opening Excel and the workbook. This was another copy-and-paste job, this time from the procedure shown in Listing 14-3. The path and filename to the workbook are different in this procedure, so we had to change those lines of code (again shown in italics here), but the rest is exactly what we 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 .xlsx file on your own computer. MySheetPath = "C:\RecordsetSheet.xlsx"

'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, we have our recordset and we have our 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 cleanup, but that code is just a straight copy-and-paste job 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 our part. We used boilerplate code — that we know already works — to write at least 90 percent of the procedure! 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 can 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 in the preceding section to format the worksheet, you could just open the worksheet and move the cursor to some known starting point — cell A1, for example. Then do the following:

Was this article helpful?

0 0

Post a comment