Rahul Soni's blog

Never assume the obvious is true!

Open EXCEL file with VB6 and read the content

Open EXCEL file with VB6 and read the content

  • Comments 18

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

  • How could I appreciate you Rahul?

    KP

  • You just did KP :)

    Thanks!

  • Thanks Rahul.

    Your code really helped me.

    Anita

  • 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.

  • 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

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

  • 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

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

  • Thanks, This was helpful

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

    Take care.

  • Hi,

    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?

  • 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

  • Thanks Rahul

    That helped a ton

  • 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.

  • 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?

Page 1 of 2 (18 items) 12
Leave a Comment
  • Please add 5 and 2 and type the answer here:
  • Post