Visual Basic Code of the Week (COTW)
Issue #82
Online Version at (paid subscribers only)
All content and source code is Copyright (c) 1999 by C&D Programming Corp. No part of this issue can be reprinted or distributed in any manner without express written permission of C&D Programming Corp. Word, Excel and Visual Basic are trademarks of Microsoft Corp.

Get your Free Year of Code of the Week!!!

For the next month we are offering a free annual subscription to anyone that submits source code for an issue which gets published. If you have some useful source code that would be valuable for our members, fill out the form at

Please make sure all source code is tested and is in working condition. We are looking for all types of source from database access routines to API calls to graphics routines. We are not looking for one or two lines tricks at this time (this will be a future offering) . The source code should be at least 10 or 15 lines of long, preferably longer. It should be commented or at least readable by the average programmer.

If you are currently a member of Code of the Week we will extend your subscription an additional year.

Cool Software

Download a free copy of NetMon - Your Internet Performance Monitor at

In this Issue

In this issue we show how to integrate Microsoft Excel with Visual Basic. This issue shows how to open Excel, close Excel, open workbooks and save them in the same or different file formats.

This source code is designed for VB 5.0 and up. It also requires Excel 97 or higher. Questions? Email us at


The cExcelIntegration class module contains a couple of properties and several methods for dealing with Excel from Visual Basic. Using the source code supplied you can easily work with Excel files in your application. An especially nice feature of this particular class is the ability to do batch conversion of files from one format to another using the file conversion features of Excel. Some of the file formats supported are many versions of Lotus 1-2-3, dBASE and Excel. It also supports several text file formats such as comma-delimited.

To use this class you must add a reference to the Excel library. To do this, click on the Project menu and the Reference option. Scroll down to the line that says "Microsoft Excel 8.0 Object Library" and select it. To use this, you must have previously installed Excel 97 or higher.

We have only touched on the capabilities that can be programmed with Excel and Visual Basic. Be sure to check out the help file that comes with Microsoft Excel called VBAXL8.hlp. It contains a large amount of useful information about the object model that is exposed for your use.

If you have any questions about using this class, let us know at


Public Sub AppOpen(bVisible As Boolean)

Opens an instance of Excel. If bVisible is True it will become the active window. If it is False Excel would be hidden from view and your application would be able to stay in focus.

Public Sub AppClose()

Force the instance of Excel started by this class to close. The user will be prompted to save changes if your program does not save them before closing.

Public Sub FileOpen()
Public Sub FileClose(bSave As Boolean)
Public Sub FileSave()
Public Sub FileSaveAs()

These methods all do exactly what you think they would do: FileOpen opens the workbook specified by Filename, FileClose closes the workbook specified by Filename, and FileSave saves the workbook specified by Filename. The FileSaveAs method has a parameter called bSave that should be True to force saving the file first or False to not save the changes made to the workbook. FileSaveAs is the only method that allows you to save the file using a different format (see FileFormat in the Properties section).


Public Property Let FileFormat(vFileFormat As Excel.XlFileFormat)
Public Property Get FileFormat() As Excel.XlFileFormat

The FileFormat property allows you to change the format that the Excel sheet is saved as. Using this feature you can use Excel as a simple file conversion program to convert dBASE files to comma-delimited files or any other combinations of files that Excel support. This feature will only work using the FileSaveAs method since the FileSave method ignores the FileFormat property.

Refer to the FileFormat property in the help file Vbaxl8.hlp (which is usually located in the "C:\Program Files\Microsoft Office\Office folder. Some of the more common choices are:

Public Property Get AppIsOpened() As Boolean

Returns True if this instance of the class opened a copy of Excel.

Public Filename As String

Stores the filename of the currently opened workbook.


See each function/property for details.

Sample Usage

The below sample describes how to use the xExcelIntegration class. It assumes the following: A form has been created with four Command buttons called cmdClose, cmdOpen, cmdOpenFile, and cmdSaveAs. There are two text boxes called txtFilename and txtSaveFilename.

Option Explicit

Dim ExcelObj As New cExcelIntegration

Private Sub cmdClose_Click()
End Sub

Private Sub cmdOpen_Click()
    On Error GoTo Handler
    ExcelObj.AppOpen True
    Exit Sub

    MsgBox Err.Description
End Sub

Private Sub cmdOpenFile_Click()
    On Error GoTo Handler
    ExcelObj.Filename = txtFilename
    Exit Sub

    MsgBox Err.Description
End Sub

Private Sub cmdSaveAs_Click()
    On Error GoTo Handler
    ExcelObj.Filename = txtSaveFilename
    ExcelObj.FileFormat = xlCSV
    Exit Sub

    MsgBox Err.Description
End Sub

Source Code

To see the source code for this issue you must be a subscriber to Code of the Week. If you are a subscriber the source code is available at the following address:

This document is available on the web

Paid subscribers can view this issue in HTML format. There is no additional source or information in the HTML formatted document. It just looks a little better since we have included some HTML formatting. Just point your browser to link at the top of this document.

Other links

Contact Information

C&D Programming Corp.
PO Box 20128
Floral Park, NY 11002-0128
Phone or Fax: (212) 504-7945