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 http://www.codeoftheweek.com/submissions.html
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.
Download a free copy of NetMon - Your Internet Performance Monitor at http://www.codeoftheweek.com/netmon/index.html
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 questions@codeoftheweek.com.
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 questions@codeoftheweek.com
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.
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()
ExcelObj.AppClose
End Sub
Private Sub cmdOpen_Click()
On Error GoTo Handler
ExcelObj.AppOpen True
Exit Sub
Handler:
MsgBox Err.Description
End Sub
Private Sub cmdOpenFile_Click()
On Error GoTo Handler
ExcelObj.Filename = txtFilename
ExcelObj.FileOpen
Exit Sub
Handler:
MsgBox Err.Description
End Sub
Private Sub cmdSaveAs_Click()
On Error GoTo Handler
ExcelObj.Filename = txtSaveFilename
ExcelObj.FileFormat = xlCSV
ExcelObj.FileSaveAs
Exit Sub
Handler:
MsgBox Err.Description
End Sub
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: http://www.codeoftheweek.com/membersonly/bi/0082.html