VBA/VB6 - Using Excel from another application

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"       
     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
Ask a question
CCM is a leading international tech website. Our content is written in collaboration with IT experts, under the direction of Jean-François Pillou, founder of CCM.net. CCM reaches more than 50 million unique visitors per month and is available in 11 languages.
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 (ccm.net).