VB.NET query Excel Files

Posted on 2005-04-26
Last Modified: 2010-04-23
Hi all,

This is pretty urgent for me. I will appreciate quick help on this. I have a VB.NET application in which I need to open excel files and then query bits and pieces of them. For example Row 5 - (Column 8 - Column 25). These coordinates change with other things I am doing in the program.

I need to be able to  
1) Read an excel file in VB.NET
2) Probablly load it into memory
3) Query to fetch different sections of the excel file.

Please help me with this.
Question by:kmalhotra
    LVL 28

    Expert Comment

    'no refernces  - late binding
    Public Class Form1
        Inherits System.Windows.Forms.Form

    #Region " Windows Form Designer generated code "
    #End Region
        Dim oExcel As Object

        Private Sub btnOpen_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnOpen.Click
            oExcel = CreateObject("Excel.Application")
            Dim wbk As Object

            wbk = oExcel.Workbooks.Open(Filename:="C:\temp\test.xls", UpdateLinks:=False, ReadOnly:=False)
            With wbk.ActiveSheet
                'read and write
                .Range("B1").Value = "Iouri" & .Range("A1").value
                .Range("B2").Value = "Iouri" & .Range("A2").value
                .Range("B3").Value = "Iouri" & .Range("A3").value
            End With ' wbk
            'print Workbook

            'save Excel
            oExcel.DisplayAlerts = False
            wbk = Nothing
          'if we need to show Excel  
          'oExcel.Visible = True
        End Sub

        Private Sub btnExit_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnExit.Click
        End Sub

        Private Sub Form1_Closing(ByVal sender As Object, ByVal e As System.ComponentModel.CancelEventArgs) Handles MyBase.Closing
            'release excel lib
            oExcel = Nothing
            'collect garbage
        End Sub
    End Class
    LVL 2

    Author Comment

    Can you also explain to me what is going on? I am not sure about the ranges and values.

    LVL 2

    Author Comment

    I need to go to a row and then read a range of columns in that row. How can I do that. Also how do i know what the name of the 40th column is (AL or whatever)

    LVL 28

    Accepted Solution

    if you need the value of cell B1 , it will be

    Also you can refere to the cell not by name but by it's index
    the 40th column will be

    Featured Post

    Better Security Awareness With Threat Intelligence

    See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

    Join & Write a Comment

    This article explains how to create and use a custom WaterMark textbox class.  The custom WaterMark textbox class allows you to set the WaterMark Background Color and WaterMark text at design time.   IMAGE OF WATERMARKS STEPS Create VB …
    It was really hard time for me to get the understanding of Delegates in C#. I went through many websites and articles but I found them very clumsy. After going through those sites, I noted down the points in a easy way so here I am sharing that unde…
    This video is in connection to the article "The case of a missing mobile phone (". It will help one to understand clearly the steps to track a lost android phone.
    Internet Business Fax to Email Made Easy - With eFax Corporate (, you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…

    729 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    19 Experts available now in Live!

    Get 1:1 Help Now