?
Solved

VB.NET query Excel Files

Posted on 2005-04-26
7
Medium Priority
?
272 Views
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.
0
Comment
Question by:kmalhotra
  • 2
  • 2
4 Comments
 
LVL 28

Expert Comment

by:iboutchkine
ID: 13868548
'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
            'MsgBox(.Range("A1").Value)
            '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.Save()
        wbk = Nothing
      'if we need to show Excel  
      'oExcel.Visible = True
        MsgBox("Done")
    End Sub

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

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

Author Comment

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

Thanks
KM
0
 
LVL 2

Author Comment

by:kmalhotra
ID: 13868927
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)

Thanks
KM
0
 
LVL 28

Accepted Solution

by:
iboutchkine earned 2000 total points
ID: 13875170
if you need the value of cell B1 , it will be
wbk.ActiveSheet.Range("B1").Value

Also you can refere to the cell not by name but by it's index
the 40th column will be
wbk.ActiveSheet.Range(40).Value
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Since .Net 2.0, Visual Basic has made it easy to create a splash screen and set it via the "Splash Screen" drop down in the Project Properties.  A splash screen set in this manner is automatically created, displayed and closed by the framework itsel…
Calculating holidays and working days is a function that is often needed yet it is not one found within the Framework. This article presents one approach to building a working-day calculator for use in .NET.
This video shows how to quickly and easily deploy an email signature for all users in Office 365 and prevent it from being added to replies and forwards. (the resulting signature is applied on the server level in Exchange Online) The email signat…
Despite its rising prevalence in the business world, "the cloud" is still misunderstood. Some companies still believe common misconceptions about lack of security in cloud solutions and many misuses of cloud storage options still occur every day. …
Suggested Courses

807 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