search within an Excel database

[v_shriram]

Hi
I am a vb.NET hobbyist from India. I am trying to design a data entry application for my company to deal with customer information. I am able to read and write data from and to a excel database using my vb.NET application. Every row in the database file contains details about a customer. I would like to know how I can search the database for information about a particular customer and display the same in various fields in my application.

 

[cjard]

There is no such thing as an Excel database. Excel is a spreadsheet application

Spreadsheets are not databases

If you intend to create a database driven app, use a database

 

[AzzKickah]

I don't think that's the answer to his question.

I'm also looking for a way to lookup specific text in an Excel file.

 

[MattP]

You can certainly connect to a data file using OleDb or ODBC. ConnectionStrings.com - Forgot that connection string? Get it here!

cjard is makign a strong suggestion that if you have any say whatsoever in the matter that you use a database.

 

[mH_p0rtal]

hi
i think i'm abit late here ,
by the way excel isn't for being used as database but sometimes we don't have any choices in some different circumstances

use of these namespaces :

Imports Microsoft.Office.Core
Imports System.io
Imports Excel
Imports System.Data

then take a look at this sub that i used some years ago for a specific goal
reading excel file & put the data in a TxT file

   Public Sub ExportExcelIntoText(ByVal FullFileName As String)
        Dim TextFilename As String
        TextFilename = "C:\persia.txt"
        If File.Exists(TextFilename) Then
            File.Delete(TextFilename)
        End If
        Dim fs As FileStream
        Dim sWrtier As StreamWriter
        Dim da As OleDb.OleDbDataAdapter
        Dim ds As DataSet
        ds = Nothing
        da = Nothing
        sWrtier = Nothing
        Try
            fs = New FileStream(TextFilename, FileMode.Create, FileAccess.Write)
            sWrtier = New StreamWriter(fs)
            Dim cnn As New OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + FullFileName + ";Extended Properties=""Excel 8.0;HDR=YES;""")
            da = New OleDb.OleDbDataAdapter("Select * from [Sheet1$]", cnn)
            ds = New DataSet("ExcelFile")
            da.Fill(ds)
            Dim rowIndex As DataRow
            Dim colIndex As DataColumn
            For Each rowIndex In ds.Tables(0).Rows

                If rowIndex(0) Is Nothing Then
                    sWrtier.Write("" + vbTab)
                Else
                    sWrtier.Write(rowIndex(1).ToString + "," + rowIndex(0).ToString)
                End If
           
                sWrtier.WriteLine()
            Next
 
        Catch ex As Exception
            Console.Write(ex.Message)
        Finally
            ds.Dispose()
            ds = Nothing
            da.Dispose()
            da = Nothing
            sWrtier.Close()
            sWrtier.Dispose()
            sWrtier = Nothing
        End Try
    End Sub

 

[MattP]

You wouldn't need Microsoft.Office.Core or Excel in your Imports w/ that code.

 

[mH_p0rtal]

sorry its true , it for some years ago & mixed up w another project that using this code , its another way but i offer you to use the first way

        Dim xlApp As Excel.Application
        Dim xlBook As Excel.Workbook
        Dim xlSheet As Excel.Worksheet
        xlApp = CType(CreateObject("Excel.Application"), Excel.Application)
        xlBook = CType(xlApp.Workbooks.Open("C:\test.xls"), Excel.Workbook)
        xlSheet = CType(xlBook.Worksheets(1), Excel.Worksheet)