Excel is one of the most popular tools for analysing data across the world. One can initialise Excel by utilising VB6, VBA or other applications from the office software. For this the user has to write a custom code in VBA. One of the most important points to remember in this case is to add a reference to the Windows Excel library. Here, Excel needs to be declared as the new application and then the workbook needs to be added. One can also specify the names of the worksheets along with specifying values for specific columns.
Here is a little routine to call Excel from VB6 or another Office application.
Paste in a general module (eg Module1)
- Under VBA>> Insert>> Module and paste in the window ...
- Under VB6>> Project>> Add a module, and paste into this window ...
Dim EX As New Application
Public Book As Workbook
Public Sheet As Worksheet
'Don't forget to add the reference...
'Microsoft Excel X,X object library
Sub AddExcel ()
Set EX = CreateObject ( "Excel.Application")
EX.Visible = True
Set Book = EX.Workbooks.Add
'All the functions are available with Application Book
Set Sheet = Book.Sheets (1)
Sheet.Name = "The Sheet"
. [A1] = "This is the cell A1"
. [A2] = "This is the A2"
. Columns ("A:A"). ColumnWidth = 23.14
'All the functions are available with Excel sheet.
This document, titled « VBA/VB6 - Using Excel from another application », is available under the Creative Commons
license. Any copy, reuse, or modification of the content should be sufficiently credited to CCM