Share this Page URL

Chapter 17. Using Visual Basic to Manage... > Using Visual Basic to Work with Data... - Pg. 599

Using Visual Basic to Manage Data Set xlwrksht = Nothing Set xlwrkbk = Nothing End Sub 599 As in the procedure that added a contact to Outlook, the code first declares a variable for the Application object associated with the program the procedure is working with--Excel, in this case. The code then declares variables for an Excel Workbook object and a Worksheet ob- ject in addition to a String variable to which we assign the path and file name of the spreadsheet the procedure creates. In the line strFileName = FilePath & "expenseapproval.xls ", we use the constant FilePath that we created in .Chapter 5.This constant refers to the default path to which this book's sample files are copied when you install them from the companion CD. In this line, the code adds the text string expenseapproval.xls, which is the name we're using for the spreadsheet. The line Set xlobj = CreateObject ("Excel.Application") opens a new instance of Excel. The line that follows adds an item to the Workbooks collection, and the third line in that group assigns a reference to the active workbook (the workbook that's currently displayed) to the variable xlwrkbk. At this point, the code has created a blank, unsaved workbook in Excel. In the next group of lines, the procedure calls the Workbook object's SaveAs method, using the variable strFileName to provide the file name and the path where the file should be saved. The code then calls the Close method to close the new workbook, followed by the Excel Application object's Quit method, which closes Excel itself. The object variables for the work- book and the instance of Excel are then set to Nothing. The procedure continues by calling the DoCmd object's TransferSpreadsheet method (which we've used in other procedures in this book) to export the records selected by the query named qryExpenseApprovalRequired. The records are exported to the spreadsheet we created in the