VBA/VB6 - Using Excel from another application

Ask a question
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