VBEXCEL is a Visual Basic library that provides an easy method of setting up and retrieving a handle to a Microsoft Excel 5.0 pivot table created from the results of a SQL statement. It also includes all Microsoft Excel Visual Basic for Applications constant declarations.
It requires the use of a VBODBC data source. To use VBEXCEL with a different data source, see the in-line comments on what changes need to be made.
1. VBEXCEL.BAS A Visual Basic module
2. VBEXCEL.XLS A Microsoft Excel template file that is used for pivot tables.
1. VBEXCEL_OpenAppObj() - Initializes Microsoft Excel for OLE Automation.
2. VBEXCEL_OpenPivotWorkbook() - Executes a SQL statement, places the results in a Microsoft Excel pivot table, and returns an OLE handle to the pivot table in the form of a global variable (since Visual Basic can't SET a passed object variable). Multiple workbooks can be opened within the application.
3. VBEXCEL_WorkBookShow() - Should be used before any OLE automation calls to a workbook. Makes it visible and active, which some properties and methods require. Also performs check to make sure Microsoft Excel is ready to be automated.
4. VBEXCEL_GetRangeStr() - Creates a valid range string (e.g. "A5:F12") from row and column integer values. Helpful because of a bug in Visual Basic when the Excel Cells() method is used as a parameter to another Microsoft Excel method.
5. VBEXCEL_WorkBookHide() - Should be used after OLE Automation calls. Hides the workbook from the GUI so the user can not interact with it from within Microsoft Excel.
6. VBEXCEL_ClosePivotWorkbook() - Closes the given workbook and deletes the temporary file. You still need to set all your workbook object variables to Nothing.
7. VBEXCEL_CloseAppObj() - Shuts down Microsoft Excel if no workbooks are open. You still need to set all your application object variables to nothing.
VBEXCEL Function Flowchart
1. Add VBEXCEL.BAS to your project.
2. Copy VBEXCEL.XLS into the application directory.
3. Follow the instructions later in this presentation and add the VBODBC files to your project.
4. Have the Visual Basic application connect to a data source.
5. Call VBEXCEL_OpenAppObj to initialize Microsoft Excel and OLE.
6. Call VBEXCEL_OpenPivotWorkbook to create a new pivot table from a SQL statement.
7. Assign the global variables (VBEXCEL_NextPivotTable et. al.) to your own, since Visual Basic can not pass object handles as parameters to functions.
8. Manipulate the workbook with VBEXCEL_WorkBookShow, VBEXCEL_GetRangeStr, VBEXCEL_WorkBookHide, and your one OLE Automation calls.
9. Close the workbook with VBEXCEL_ClosePivotWorkbook. You still need to set all your own workbook object variables to "Nothing".
10. Close the application with VBEXCEL_CloseAppObj. You still need to set all your own application object variables to "Nothing".