VBA/VB6 - Using Excel from another application

December 2016

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)       
     Example:       
     Sheet.Name = "The Sheet"       
     With Sheet       
         . [A1] = "This is the cell A1"       
         . [A2] = "This is the A2"       
         . Columns ("A:A"). ColumnWidth = 23.14       
     End With       
     'All the functions are available with Excel sheet.       
End Sub

Related :

This document entitled « VBA/VB6 - Using Excel from another application » from CCM (ccm.net) is made available under the Creative Commons license. You can copy, modify copies of this page, under the conditions stipulated by the license, as this note appears clearly.