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 Library:

   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.