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
In this example, the SQL statement in the variable strTemp would normally be indented,
but this causes display problems for this book.
This code works in a similar way to earlier examples in that it creates an Excel object
based on the template spreadsheet MyRecSet. Where it is different is that it then iterates
through a 20×20 range of cells, beginning at A1, and looks for any cell with a text string that
commences with an exclamation mark (!).
When it finds the exclamation mark, it removes it from the string. It then incorporates the
criteria into the SQL string using a WHERE clause. A recordset is created and the value is
overwritten into the same cell. If no value is found, then 0 is used. Notice that an “On Error”
statement is used in case the user has entered something in the spreadsheet that will cause an
error.
The spreadsheet is saved under a different name (so the original criteria are maintained)
and the Excel application is closed.
Run the code and the spreadsheet TestExcel will be created.
If you open the spreadsheet TestExcel, you will see there is now a number in cell C5.
Users can also use multiple criteria by employing an AND or OR statement in the
spreadsheet string. An example would be ![Product Name]= ‘Northwind Traders Syrup’ and
[Reorder Level]>10. So long as the criteria can be concatenated into the SQL statement
within the VBA code, this will still work.
Chapter 35: Use Excel For Output 335