Open EXCEL file with VB6 and read the content

I was planning to write an application today which simply opens an Excel file and read the Cell values into an array so that I could manipulate it accordingly. Since I don't have VS.NET installed on my home PC, I decided to do it with VB6 :o)

Here is the code...

Private Sub cmdOpenExcel_Click()
On Error GoTo ErrHandler
    Dim xlsApp As Object
    Dim xlsWB1 As Object
    'Late binding to open an XLS file which is present on my local harddisk
    Set xlsApp = CreateObject("Excel.Application")
    xlsApp.Visible = True
    Set xlsWB1 = xlsApp.Workbooks.Open(strFileName)
    Exit Sub
    MsgBox "There is a problem while opening the xls document. " & _
    " Please ensure it is present!", vbCritical, "Error"
End Sub

Now, since I know that my Excel file (which I want to work with) has 15 columns and 200 rows, here is what I did to read all the content to an Array for further manipulation.

Private Sub cmdParse_Click()
On Error GoTo ErrHandler:
    Dim xlsApp As Object
    Dim xlsWB1 As Object
    Dim xlsWS1 As Object
    'Opening the file to parse now
    Set xlsApp = CreateObject("Excel.Application")
    xlsApp.Visible = False
    Set xlsWB1 = xlsApp.Workbooks.Open(strFileName)
    Set xlsWS1 = xlsWB1.Worksheets("Sheet1")
    Dim col As Integer
    Dim row As Integer
    Dim str As String
    str = ""
    MaxRow = 200
    MaxCol = 15
    'Declaring an array so that we don't have to depend on the excel file anymore
    ReDim CaseArray(MaxRow, MaxCol)
    'Reading the Excel file and putting everything in Memory for faster manipulation
    For row = 1 To MaxRow
        For col = 1 To MaxCol
            CaseArray(row, col) = xlsWS1.cells(row, col).Value
    Set xlsApp = Nothing
    Set xlsWB1 = Nothing
    Set xlsWS1 = Nothing
    Exit Sub   
    MsgBox "An unknown error occurred while Parsing the Excel. Sorry about that!!" , vbCritical, "Error"
End Sub

In my case, CaseArray was a 2 dimensional Array using which I used in the other modules to manipulate the data as per my requirements!

Hope that helps!


Comments (18)

  1. Kailash says:

    How could I appreciate you Rahul?


  2. imRahulSoni says:

    You just did KP 🙂


  3. Anita Mallick says:

    Thanks Rahul.

    Your code really helped me.


  4. Sagar says:

    Hey Rahul,

    Thanks for this code buddy.

    But I have an issue here – I want to save contents of a RecordSet into an excel file. I want to display a File Open / Save As dialog box, the user will then select existing / specify name for new excel file. Once this is done, I want to rename the "Sheet1" to "Search Results" and then copy each record from the recordset into the excel sheet.

    Can you please help? I am using VB6 and ADODB.

  5. imRahulSoni says:

    Hi Sagar,

    That is indeed a good requirement, but unfortunately at this point I am pretty much pressed for time. If time allows I will try to write the code for it.

    Regarding the File open/Save As dialog, I have created another post at

    Hope that helps,


  6. Hemant says:

    i want to read excel cell comments and write them into database

  7. Hemant says:

    Hi Sagar Just check following code,

    excel file is opened as a adodb connection, then u can manipulate as u want

    Dim cn As ADODB.Connection

    Dim rsT As New ADODB.Recordset

    Dim tblList As ADODB.Recordset

    Dim I As Integer

    Private Sub cmdImport_Click()


       rsT.Open "select * from  [" & cmbSheetName.Text & "]", cn, adOpenDynamic, adLockReadOnly

       Do While Not rsT.EOF

       If IsNull(rsT.Fields(5)) = False Then

          List1.AddItem rsT.Fields(5) & ""

       End If




    End Sub

    Private Sub cmdOpenFile_Click()

       cDiag.Filter = "*.xls"

       cDiag.Action = 1

       txtFileName.Text = cDiag.FileName

       With cn

           .Provider = "Microsoft.Jet.OLEDB.4.0"

           .ConnectionString = "Data Source=" & txtFileName.Text & _

            ";Extended Properties=Excel 8.0;"

           .CursorLocation = adUseClient


       End With

       Set tblList = cn.OpenSchema(adSchemaTables)

       For I = 1 To tblList.RecordCount

           cmbSheetName.AddItem tblList.Fields("TABLE_NAME").Value & ""


       Next I

       cmbSheetName.ListIndex = 0

    End Sub

    Private Sub Form_Load()

       Set cn = New ADODB.Connection

    End Sub

    Private Sub Form_QueryUnload(Cancel As Integer, UnloadMode As Integer)

    ‘    cn.Close

    End Sub

  8. imRahulSoni says:

    Thanks for sharing that Hemant. I haven’t tested it, but looks quite a promising way of doing it!!!

  9. stanford gibson says:

    Thanks, This was helpful

  10. Slim Jim says:

    Thanks much for the Sample Rahul, it helped save some time.

    Take care.

  11. Kala says:


    my requirement is to populate data into excel sheet using vb6. i tried in this way,

    Dim oXL As Excel.Application

    Dim oWBK As Excel.Workbook

    Dim oWS As Excel.Worksheet

    Set oXL = New Excel.Application

    Set oWBK = New Excel.Workbooks

    Set oWS = New Excel.Worksheet

    oWBK = oXL.Workbooks.Open("D:Copy of July_Jobtimigs_Production_New.xls")

    oWS.Cells(5, 5) = "File Validation"

    but, i am getting error as ‘Activex component can’t create object’ for ‘Set oWBK = New Excel.Workbooks’

    could you help me in this?

  12. Inayat says:

    Imports Excel = Microsoft.Office.Interop.Excel

    Public Class Form1

       Private Sub Button1_Click(ByVal sender As System.Object, _

     ByVal e As System.EventArgs) Handles Button1.Click

           Dim xlApp As Excel.Application

           Dim xlWorkBook As Excel.Workbook

           Dim xlWorkSheet As Excel.Worksheet

           Dim range As Excel.Range

           Dim rCnt As Integer

           Dim cCnt As Integer

           Dim Obj As Object

           xlApp = New Excel.ApplicationClass

           xlWorkBook = xlApp.Workbooks.Open("c:vbexcel.xlsx")

           xlWorkSheet = xlWorkBook.Worksheets("sheet1")

           range = xlWorkSheet.UsedRange

           For rCnt = 1 To range.Rows.Count

               For cCnt = 1 To range.Columns.Count

                   Obj = CType(range.Cells(rCnt, cCnt), Excel.Range)









       End Sub

       Private Sub releaseObject(ByVal obj As Object)



               obj = Nothing

           Catch ex As Exception

               obj = Nothing



           End Try

       End Sub

    End Class

  13. Sameer Kelkar says:

    Thanks Rahul

    That helped a ton

  14. ravi says:


    my requirement:

    populate a vb combobox with values from one single excel column(say column D of an excel file stored in My Documents folder). Also, this column has some values that repeat themselves. I need to filter these and only populate distinct values in the combobox.

    i have been able to open the excel worksheet via vb. but, how do i pass the values from this columb onto the combobox on the form?

    help is greatly appreicated.

    i am using vb6.0 and excel 2003 – this helps.

  15. says:

    I’m reading an excel file to compare to the current Access DB records. If there is a change I write the change – so much for that.

    It seems like it takes FOREVER to read thru each of the cells and do whatever. Is there a FASTER way to get this done?

  16. Pradeep Bhojak says:

    Yes i have the same code as rahul mentioned at the top but with XP Professional Service Pack 3 (OS) and Excel-2007 (MS Office) i am getting an error "ActiveX Component Couldn’t create object" as i run

    Dim excellApp as Object

    Set excelApp = CreateObject("Excel.Application")

    Please help me


  17. kuldeep says:

    I want to create a Keyword Research Tool and I want to use Excel for various kinds of Calculations applied on various cells for calculating Keyword Relevancy Factor, KEI, R/S Ratio, Keyword Competition, Search Per Month, Last Month’s Search. Now the problem is that I do not know how I can do this with VB and Excel. Is there Anyone to help me?????????

    From: <a href=""><b>Google Adsense Alternative</b></a>

  18. sam says:

    thanks Rahul to share this code i will try it for my programs  

Skip to main content