Using the Excel OM to Create a New Workbook

To create a new worksheet in Excel, you will build the code in a few steps. To start you create an Excel application object. Then, you create a new worksheet object, as shown in the following code:

'Define variables

Dim xlApp As Excel.Application

Dim xlWorkbook As Excel.Workbook

'Create the Excel Application object

Set xlApp = CreateObject("Excel.Application")

xlApp.Visible = True

'Create a new workbook

Set xlWorkbook = xlApp.Workbooks.Add

After creating the new worksheet, you'll want fill that worksheet with data. The next example uses a Recordset object (from DAO, see Chapter 6) to gather the data from an Access query in the database. The Recordset object enables you to get the data from any table or row returning a query for the desired records to be exported to Excel. The following code creates a new Recordset based on the selected query in the list box and uses that query's name as the name for the Excel worksheet.

'Define Variables Dim objRST As Recordset Dim strQueryName As String Dim strSheetName as String

'Create the Recordset strQueryName = Me.lstExport

Set objRST = Application.CurrentDb.OpenRecordset(strQueryName)

'Create a Sheet Name - Must be 31 chars or less strSheetName = Trim(Left(strQueryName, 31))

Once the Recordset object has been created, Excel's CopyFromRecordset method can be used to copy data from the Recordset to the Cells object of the new worksheet. The following code shows how to accomplish that task (it can be concatenated to the previous two code examples):

Dim xlSheet As Excel.Worksheet

'Use code to fill the Excel Sheet Set xlSheet = xlWorkbook.Sheets(l) With xlSheet

.Cells.CopyFromRecordset objRST .Name = strSheetName End With

'Clean up all Variables Set objRST = Nothing Set xlSheet = Nothing Set xlWorkbook = Nothing Set xlApp = Nothing

The preceding code creates a plain Excel worksheet, without any special formatting. However, the spreadsheet might look better to the user if it had column headers. It is easy to add a few lines of code to create column headings and to shade those column headings. Because this code uses a DAO Recordset object, you have the properties and methods of the Recordset object at your disposal. To add column headings, simply loop through the Fields collection of the Recordset and add a heading for each field (column) in the data set. This task is completed by adding the following lines of code to the previous example:

'Add headings to each of the columns Set xlSheet = xlWorkbook.Sheets(l) For lvlColumn = 0 To objRST.Fields.Count - 1 xlSheet.Cells(1, lvlColumn + 1).Value = _ objRST.Fields(lvlColumn).Name

Next

The preceding code loops through every column in the worksheet and places the appropriate field name in that column. Just placing field names isn't very exciting, however. Why not add some color? For some added pizzazz, you can also add a cell border and a bold font. The following code does the job:

'Change the font to bold for the header row

xlSheet.Range(xlSheet.Cells(1, 1), _

xlSheet.Cells(1, objRST.Fields.Count))

.Font.Bold = True

'Add a border to header row cells

With xlSheet.Range(xlSheet.Cells(1, 1),

xlSheet.Cells(1, objRST.Fields.Count))

.Borders(xlEdgeLeft)

.LineStyle = xlContinuous

.Weight = xlThin

.Colorlndex = xlAutomatic

End With

With xlSheet.Range(xlSheet.Cells(1, 1),

xlSheet.Cells(1, objRST.Fields.Count))

.Borders(xlEdgeTop)

.LineStyle = xlContinuous

.Weight = xlThin

.Colorlndex = xlAutomatic

End With

With xlSheet.Range(xlSheet.Cells(1, 1),

xlSheet.Cells(1, objRST.Fields.Count))

.Borders(xlEdgeBottom)

.LineStyle = xlContinuous

.Weight = xlThin

.Colorlndex = xlAutomatic

End With

With xlSheet.Range(xlSheet.Cells(1, 1),

xlSheet.Cells(1, objRST.Fields.Count))

.Borders(xlEdgeRight)

.LineStyle = xlContinuous

.Weight = xlThin

.Colorlndex = xlAutomatic

End With

This code sets each border (top, bottom, left, and right) to a thin line by calling the Borders object with the appropriate edge option and setting the LineStyle, Weight, and Colorlndex properties with the desired values. Now, you're ready to return to the code to fill the sheet with data. In this case, you need to make one minor alteration to the previously listed code. If you add the code to fill and format the column headings, and then try to execute the previously listed code as is, you'll end up with no header row and the first row of data formatted with bold font and borders. To start the data in the second row of the spreadsheet, change this code from the previous example:

With xlSheet

.Cells.CopyFromRecordset objRST .Name = strSheetName End With to the code:

With xlSheet

.Range("A2").CopyFromRecordset objRST .Name = strSheetName End With

Once this code has been added, the field names for each of the columns will be in the first row of the Excel spreadsheet and the data from the Recordset will start on the second row. This provides not just data, but information about the data to the user who wants to utilize the new worksheet. As the example shows, building spreadsheets in Excel based on Recordsets from an Access database solution is simple and requires only a small amount of code.

In some circumstances the previous code may fail. If the query referenced by the Recordset object is an action query (Update, Insert, Delete, and so on), this code will fail. For the OpenRecordset method to succeed, the query must return a set of records.

0 0

Post a comment