[RESOLVED] Searching in text file

[mcsmba1]

Hi all,
I have a text file which stores 6 digit numbers with tab separator. I have an excel file which stores 6 digit numbers also in only one column of a sheet. I want to search data of excel sheet in text file, If numbers match then the find data will show in a list box. I want to do this through Visual Basic 2013.
Kindly help in this regard with code.........

[Gruff]

personally i would write the entire thing in Excel VBA.
You have all the tools to do the job in VBA and it would be a lot easier than trying to automate Excel from VB.NET 2013.

Create a form in VBA add a search button, a listbox, and a copy to clipboard button.

On the search button click open the text file and read the contents into an array of string splitting the data by newline and tab characters.
Loop through this string.
Use an excel column search for the text file value.
if found copy the row data you desire to the listbox.

Show a message box when the loop completes.

Allow the user to copy the listbox contents to the windows clipboard if desired.

If the name or path of the text file changes you will have to add a call to Excel's common file open dialog to select it.

To get started open your excel file and record a macro that does a column search for one number.

Examine the macro (VBA Code) This will be the heart of your search routine inside the loop.

[TnTinMN]

...Kindly help in this regard with code.........

I get right on it. By the way do, you want fries with that order?

[mcsmba1]

Thanks for reply and suggesttion by @Gruff......
Actually i receive excel file from my client after every 10 working days and our government announces a lucky draw after 15 days and i will check whether the numbers given in excel are present in text file or not. I want to give only path of both files in Visual Basic 2013 and my user just click a button to check whether their numbers are present or not. If numbers match then show in a list box

[Gruff]

Right. You can do it much simpler in Excel VBA than in VB 2013.
You would write your program as a global excel program that works on any excel workbook you load. You would use Excels VBA forms to make a form that contained your listbox, buttons and such. You could even assign a hot key or icon in the Excel toolbar to run your program.

If you use 2013 you will have to learn how to run Excel automation from 2013 and how do dispose of the com link after the fact. Those things can be difficult for a beginner. VB.NET 2013 does not have any native way to read an Excel workbook directly.

[Gruff]

The following is rough, but shows the Excel solution thought process.

It assumes Column A contains the Excel number data and that the first row is 1 and that row data is in column B.

Option Explicit

Private Sub UserForm_Initialize()
  txtTextFile.Text = GetSetting("NumberCompazer", "FilesAndPaths", "TextFile", "")
End Sub

Private Sub cmdBrowse_Click()
  With Application.FileDialog(msoFileDialogOpen)
    .Filters.Clear
    .Filters.Add "Text File", "*.txt"
    .FilterIndex = 1
    .AllowMultiSelect = False
    .InitialFileName = txtTextFile.Text
    
    Dim Ret As Integer
    Ret = .Show
    
    If Ret <> 0 Then
      ' Display and Save selected file path and name for next program load.
      txtTextFile.Text = .SelectedItems(1)
      Call SaveSetting("NumberCompazer", "FilesAndPaths", "TextFile", txtTextFile.Text)
    End If
  End With
End Sub

Private Sub cmdSearch_Click()
  Dim oSheet As Excel.Worksheet
  Dim oSearchRange As Excel.Range
  Dim oStartCell As Range
  Dim oLastCell As Range
  Dim oFoundCell As Range
  Dim SearchColumn As Integer
  Dim StartRow As Integer
  Dim LastRow As Integer
  
  Set oSheet = Application.ActiveSheet
  SearchColumn = 1 'Column 'A'
  StartRow = 1
  LastRow = Cells(Rows.Count, SearchColumn).End(xlUp).Row
  
  Set oStartCell = Cells(StartRow, SearchColumn)
  Set oLastCell = Cells(LastRow, SearchColumn)
  Set oSearchRange = Range(oStartCell, oLastCell)

  Dim TextLine As String
  Open txtTextFile.Text For Input As #1
    Do While Not EOF(1)
      ' Read each line of text
      Line Input #1, TextLine
      Dim Numbers() As String
      Dim Number As Variant
      ' Divide by tab characters
      Numbers = Split(TextLine, vbTab)
      For Each Number In Numbers
        Number = Trim(Number)
        Set oFoundCell = oSearchRange.Find(What:=Number, After:=oStartCell, LookIn:=xlValues, _
        LookAt:=xlWhole, SearchOrder:=xlByColumns, SearchDirection:=xlNext, _
        MatchCase:=False, SearchFormat:=False)
        
        If Not oFoundCell Is Nothing Then
          ' Add found items to listbox
          lstMatches.AddItem oFoundCell.Text & "   " & oFoundCell.Offset(0, 1).Text
        End If
      Next Number
    Loop
  Close #1
  
  Set oLastCell = Nothing
  Set oStartCell = Nothing
  Set oFoundCell = Nothing
  Set oSearchRange = Nothing
  Set oSheet = Nothing
End Sub

Private Sub cmdCopyToClipboard_Click()
  Dim oData As msforms.DataObject
  Set oData = New msforms.DataObject
  Dim sOut As String
  Dim i As Integer
  
  sOut = ""
  With lstMatches
    For i = 0 To .ListCount - 1
      sOut = sOut & .List(i) & vbCrLf
    Next i
    oData.Clear
    oData.SetText sOut
    oData.PutInClipboard
    Set oData = Nothing
  End With
  'Set oData = Nothing
  MsgBox "Windows Clipboard contains Found List."
End Sub

[mcsmba1]

Thanks for reply master Gruff , let me check
But, if i will do it in visual basic 2013 then what can i do for it????
I have done some part of it
On Button Click event

Dim filepath as string="D"\ALPHA\Draw.txt"
Dim txtfiledata as string
txtfiledata=My.computer.FileSystem.ReadAllText(filepath)
Dim Lookfor as String="762404"
If txtfiledata.contains(Lookfor) then
msgbox ("Found: " & Lookfar)
End if

It show me that 726404 is in the text file or not but i want to give it excel sheet1 column1 through loop to check it whether data of column1 is exist in text file or otherwise. If data match then show in the list box. Kindly guide
Thanks for your cooperation in this regard..........

[joe31623]

I would think that a solution using Visual Basic would use the FileStream Class's methods. See: https://msdn.microsoft.com/en-us/lib...v=vs.110).aspx. I would solve either a VBA or Visual Basic 2013 solution with some kind of collection, dictionary object, or dynamic array to add the strings that you've found.

As Gruff points out, this would be much easier in VBA. I do not understand your most recent question. Please clarify.

Gruff pretty much wrote your code for you and if you want anything more than that, I would provide specific files and more details very clearly. Preferably, upload a sample .txt file and sample .xls/xlsm file that you're starting with and then upload a sample .xls/xlsm file that shows the results you want. It only needs to have a partial solution and partial file if the file is large.

Also, Why do you need to do this in Visual Basic 2013 instead of VBA?

[Gruff]

mcsmba1,

All you have done in VB.NET 2013 so far is read a text file. That is easy.

As I said before VB.NET does not have any built in tools to read an Excel workbook file.

As far as I know there are only two ways to do what your asking in VB.NET.
1) Use Microsoft Automation ( Google this term. )
__a) Open an instance of Excel within VB.NET and manipulate the data in Excel
____ in much the same manner as my example.
__b) Open an instance of Excel within VB.NET and read the entire content of the columns
____ and manipulate the data in VB.NET.

2) Use ADO.NET to open the Excel workbook as a database source and manipulate the result in VB.NET as a datatable.

Both are intermediate programming tasks at best.
If you want to explore them Google the topics.
Read about them then ask questions here. We will be happy to help.

[joe31623]

mscmba1,

Elaborating on what Gruff mentioned above, the following thread may help get you started.

http://www.vbforums.com/showthread.p...eet-From-Array

[mcsmba1]

Thanks a lot Gruff and Joe,
Its really helpful for me as advice by Gruff and links provided by Joe.
Let me try and then put my question here.
Thanks for cooperation.........

[mcsmba1]

Thanks to both of you, especially Gruff, you have understand and help me to solve my issue. Thanks a lot..........