Read an already open Excel file in VB.NET

sunilr · January 5th, 2007, 09:28 AM

Hi Guys,

I want to read an excel file in VB.NET. This excel file is always open and is constantly updated.

The code I have used is given below.

Code:

Dim objExcelApp As New Excel.Application
        Dim objWorkBook As Excel.Workbook = objExcelApp.Workbooks.Open("c:\ReadExcel.xls")
        Dim objWorkSheet As Excel.Worksheet = objWorkBook.Worksheets.Item(1)
        Dim objRng As Excel.Range
        Dim strCol, strCell As String
        Dim maxCol, maxRow As Integer

        Dim iRow, iCol As Integer

        maxRow = 2

        maxCol = 2

        objWorkBook = objExcelApp.Workbooks.Open("c:\ReadExcel.xls")

        objWorkSheet = objWorkBook.Worksheets(1)

        objExcelApp.Visible = True


        For iCol = 1 To maxCol 
            For iRow = 1 To maxRow 
                strCol = Chr(Asc(iRow) + 16)
                strCell = strCol + iCol.ToString

                objRng = objWorkSheet.Range(strCell)
             msgbox 
            Next
        Next

I have a few issues with this code.

1. Since I want to read an already open excel sheet I don't want to open it again. Here the OPEN command opens another excel sheet which I don't want.

2. In this code the columns & rows are hard coded, but in my case the excel sheet can have any number of rows & columns.
I want to loop till the end of the file.

Can anybody Please help me on an urgent basis.

Thanks in advance

deepti_07 · March 28th, 2007, 05:45 AM

Hi Sunil,

Did u get any solution for giving dynamically the rows with data ?

Please let me know @ deepti.premkumar@gmail.com

Deepti

ashishkapoor2000 · April 4th, 2007, 06:41 AM

Hi Sunil

You can use connection string and connect to excel

ConnectionString: Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\MyExcel.xls;Extended Properties="Excel 8.0;HDR=Yes;IMEX=1";

SQL Query: SQL syntax "SELECT * FROM [sheet1$]". I.e. excel worksheet name followed by a "$" and wrapped in "[" "]" brackets.

If nothing going Right
Turn Left

August 27th, 2008, 12:28 AM

you can do it with OLEDB update

http://vb.net-informations.com/excel...data_oledb.htm

or ttry this

http://vb.net-informations.com/excel..._open_file.htm

PerttiOO · February 28th, 2014, 07:20 AM

Hi, I had a similar requirements and here is what I used:
- First I set up a FileSystemWatcher to monitor XLS file changes.
- Second read every sheet and every sheets cell in the targeted excel file.

Here is a code for first part:

Code:

    Private watcher As New FileSystemWatcher()

    Private Sub Form1_Load(sender As System.Object, e As System.EventArgs) Handles MyBase.Load
        watcher.Path = Environment.GetFolderPath(Environment.SpecialFolder.MyComputer)
        watcher.Filter = "*.xls"
        watcher.NotifyFilter = NotifyFilters.LastAccess Or NotifyFilters.LastWrite Or NotifyFilters.Size Or NotifyFilters.FileName

        AddHandler watcher.Changed, AddressOf OnChanged
        AddHandler watcher.Renamed, AddressOf OnChanged

        watcher.EnableRaisingEvents = True
    End Sub

And for the second part I used this VB.NET excel component which can greatly simplify reading of excel files in .NET. Here is a code:

Code:

    Private Sub OnChanged(source As Object, e As FileSystemEventArgs)
        Dim file As ExcelFile = ExcelFile.Load(e.FullPath)

        For Each sheet As ExcelWorksheet In file.Worksheets
            Dim range As CellRange = file.Worksheets.ActiveWorksheet.GetUsedCellRange(True)

            For i As Integer = range.FirstColumnIndex To range.LastColumnIndex
                For j As Integer = range.FirstRowIndex To range.LastRowIndex

                    Dim cell As ExcelCell = range(j - range.FirstRowIndex, i - range.FirstColumnIndex)
                    Dim cellName As String = CellRange.RowColumnToPosition(j, i)
                    Dim cellRow As String = ExcelRowCollection.RowIndexToName(j)
                    Dim cellColumn As String = ExcelColumnCollection.ColumnIndexToName(i)

                    ' Do something with cell's information.
                Next
            Next
        Next
    End Sub