Share via

Open EXCEL file with VB6 and read the content

  • Article

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
ErrHandler:
    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
        Next
    Next
    xlsWB1.Close
    xlsApp.Quit
    Set xlsApp = Nothing
    Set xlsWB1 = Nothing
    Set xlsWS1 = Nothing
    Exit Sub   
ErrHandler:
    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!

Cheers,
Rahul

Comments

  • Anonymous
    February 01, 2007
    How could I appreciate you Rahul? KP

  • Anonymous
    February 01, 2007
    You just did KP :) Thanks!

  • Anonymous
    February 07, 2007
    Thanks Rahul. Your code really helped me. Anita

  • Anonymous
    March 13, 2007
    The comment has been removed

  • Anonymous
    March 14, 2007
    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 http://blogs.msdn.com/rahulso/archive/2006/03/24/4-ways-to-send-a-pdf-file-to-the-ie-client-in-asp-net-2-0.aspx. Hope that helps, Rahul

  • Anonymous
    April 12, 2007
    i want to read excel cell comments and write them into database

  • Anonymous
    April 12, 2007
    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()    List1.Clear    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    rsT.MoveNext    Loop    rsT.Close 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        .Open    End With    Set tblList = cn.OpenSchema(adSchemaTables)    For I = 1 To tblList.RecordCount        cmbSheetName.AddItem tblList.Fields("TABLE_NAME").Value & ""        tblList.MoveNext    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

  • Anonymous
    April 12, 2007
    Thanks for sharing that Hemant. I haven't tested it, but looks quite a promising way of doing it!!!

  • Anonymous
    May 09, 2007
    Thanks, This was helpful

  • Anonymous
    June 22, 2007
    Thanks much for the Sample Rahul, it helped save some time. Take care.

  • Anonymous
    August 02, 2008
    The comment has been removed

  • Anonymous
    November 14, 2008
    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)                MsgBox(Obj.value)            Next        Next        xlWorkBook.Close()        xlApp.Quit()        releaseObject(xlApp)        releaseObject(xlWorkBook)        releaseObject(xlWorkSheet)    End Sub    Private Sub releaseObject(ByVal obj As Object)        Try            System.Runtime.InteropServices.Marshal.ReleaseComObject(obj)            obj = Nothing        Catch ex As Exception            obj = Nothing        Finally            GC.Collect()        End Try    End Sub End Class

  • Anonymous
    December 04, 2008
    Thanks Rahul That helped a ton

  • Anonymous
    February 04, 2009
    hi, 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.

  • Anonymous
    February 05, 2009
    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?

  • Anonymous
    March 19, 2009
    The comment has been removed

  • Anonymous
    April 11, 2009
    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="http://google-adsense-alternative.blogspot.com"><b>Google Adsense Alternative</b></a>

  • Anonymous
    July 28, 2009
    thanks Rahul to share this code i will try it for my programs