On Error Resume Next
Kill "TestExcel.xlsx"
On Error GoTo 0
MyBook.SaveAs ("TestExcel")
MyBook.Close
MyExcel.Quit
Set RecSet = Nothing
Set MyExcel = Nothing
Set MyBook = Nothing
Set MySheet = Nothing
End Sub
This code works similar to the first example in this chapter in that it creates an Excel
application and a new workbook in that application. A recordset is then created based on the
Products and Purchase Order Details tables to show the product name and the cost.
Notice that the query has been loaded into the variable strTemp in several stages. It could
have been done as one continuous string, but this makes the string very difficult to view in its
entirety and will create problems for another developer trying to learn your code.
The code iterates through the recordset and transfers the Product Name and Total Cost
into columns A and B of the spreadsheet. There is a counter that works out the row each
record is placed on.
A second counter increments the first counter by two every time five items are reached.
This has the effect of populating the spreadsheet in groups of five items, which could not be
done using theCopyFromRecordsetmethod.
Allowing Users to Design Their Excel Reports
You can also use VBA code to allow users to design their own Excel reports, provided that
they follow a few simple rules. They can use an Excel template, such as MyRecSet that you
used earlier in this chapter, and you can provide them with the flexibility to decide where the
numbers will appear.
This is particularly useful where the reporting criterion is a moving target each month.
Instead of having to rewrite your VBA code to encompass new criteria (and where it is to be
shown in the Excel spreadsheet), you can allow the user the freedom to decide.
Open up the original Excel template file that you created earlier, called MyRecSet, and
select a cell such as C5. Enter!Northwind Traders Syrupinto this cell.
The string “Northwind Traders Syrup” is a product within the Northwind database and the
exclamation mark (!) is a marker to tell our VBA code that it has to find a number for this
product. This would normally be completed by the user and they would use this notation to
select different products for reporting on the spreadsheet.
Chapter 35: Use Excel For Output 333