Tutorial/FAQ: How to Automate Excel from VB6 (or VB5/VBA)
Do you want to create or read an Excel file from your program?
Perhaps you want to create a report with some data, a few formulas, and some graphs, or maybe get some particular data out of a spreadsheet?
The good news is that Excel has been designed to let you do all of this and more; you can treat it like other objects that you would use from VB, and you can perform programmatically all of the tasks that you can manually. In the rest of this tutorial you will find out how.
Please note that you must have Excel installed to do any of this, and so must your users!
Note that as an alternative to automation you can use an Excel file as a database, which does not require Excel to be installed - however it is not as easy to use as other database systems, due to unusual SQL syntax (eg: "SELECT * from `Sheet1$A1:`"). This method is not discussed in this document, please search the Database Development forum for previously asked questions which cover this.
2 - In the beginning - Adding a Reference to Excel
3 - Opening Excel
4 - Creating/opening a Workbook
5 - Preparing to work with data (setting and closing App/Book/Sheet references)
6 - Reading and writing data
7 - Basic formatting
8 - Finding "special" cells (bottom row etc)
9 - Installation Issues
10 - Using a Late-Bound connection to Excel
11 - "Excel doesn't close"
12 - How do I write code to … ? [a.k.a. recording macro's]
13 - Useful functions and downloads
14 - Frequently Asked Questions