Use the Excel Object from VB
Often, you may need to import data from a Microsoft Excel worksheet into your
Visual Basic application or export your data to an Excel worksheet.
Since the functionality of Excel is available through the Excel
Object Library, the hardest part is determining which Excel
properties to set and methods to call. The Excel Object Library is very
extensive. To see for yourself, just reference it in your project and
bring it up in the object browser.
What I demonstrate here are a few very basic concepts. I start with a
simple spreadsheet, fill it with data, change the font, color and format of a
few cells and create a simple formula to sum up the data. Then I let you
view the spreadsheet or print it out.
There are many books devoted to using the Excel library and all do a better job
than this sample. All I am attempting to do is show that it is possible
and actually very easy to do. I wrote this program in response to all the
questions I have received on how to export data to Excel. Hopefully this
will provide a little insight and act as a starting point.
To view the worksheet I use the ShellExecute API to
start Excel and have it display the worksheet. For a detailed description
of this function see my Open/Print Files,
View Web Sites, Connect to the Internet program.
Download Source Code
I use a sample worksheet that was created with Excel 97. If you have an
older version of Excel this program may not work. However, the concepts
that I illustrate will work with any version of Excel.
To use Excel within your application create an instance of the Excel Object
Dim oExcelApp As excel.Application
Dim oWs As excel.Worksheet
Dim oWb As excel.Workbook
Set oExcelApp = CreateObject("EXCEL.APPLICATION")
Set oWs = oExcelApp.ActiveSheet
Set oWb = oExcelApp.ActiveWorkbook
Since I used early binding you must set a reference to Excel in the Project |
References dialog. See the code for details.
Run the code and click Export to Excel to make a copy of the TheScarms.xls
template spreadsheet and populate it. Then click ViewWorksheet to start
Excel and have it display the new worksheet. The Print button instructs
Excel to send the sheet to your printer.