Use VBA to Compare Data in a Text File with the List of Excel Worksheet

In this article, I will explain about how to compare data in a list of information contained in a text file with the information about list of excel sheet. It is very useful while we will be working in File system object.

Work of Opening the Text File

When you are working with VBA, and want accessing the File System Object (FSO) . It should be a procedure; you have save the code library of File System Object. To making easily access the files. If you want to use this facility the simply copy the code which is given below, replace the file name according to your need.
The code which is given below. Read the data into a string called allTxt and we shall using this string allTxt to comparing the list of items with excel sheet..To do this work we need to compare this list each item in our list line by line of the file.

Dim fso As Scripting.FileSystemObject

Dim myFile As Object

Set fso = New Scripting.FileSystemObject

filePath = ActiveWorkbook.path & "myFile.txt"

Set myFile = fso.openTextFile(filePath)

allTxt = myFile.ReadAll


Set myFile = Nothing

Set FSO =nothing

Once when we have to save the data into the string, then we do not need the file. So we close the files and release the file from memory.
The next step to select the list of information or data which we want to compare. If you data is present in column one of worksheet1 the we can this code for one column of workseet 1. Write the codes like this:

dim rng as range


set rng=range("a1").currentRegion.columns(1)

Now next step to compare our list defined with the data and the text file in a string. we can use loop through the list and determine the items in the file:

For Each c in rng.rows

If InStr(allTxt, c) > 0 Then

txtFound = txtFound & c & ","


txtNotFound = txtNotFound & c & ","

End If


The given code has written with two separate strings of tiems . This the items may be found and those not found in the list . It is asimple task to convert our result into a readable format.
Now our next step ,First, we will remove the trailing comma from each string with the left function.

txtFound = left(txtFound, Len(txtFound) - 1)

txtNotFound = left(txtNotFound, Len(txtNotFound) - 1)

Finally, we shall create a message box in VBA to display the result.
Note The character vbcrlf which creates a new line between the two strings.

msg = "Found: " & txtFound & vbCrLf

msg = msg & "Not found: " & txtNotFound

MsgBox msg

Summary of the article
With the help of this article has demonstrated the power of VBA to compare data in different files in worksheets .Often we need to exchange information in our project in Excel worksheet


Author: Phagu Mahato01 Nov 2014 Member Level: Gold   Points : 4

I am thankful to all visitors and editors of DotNetSpider . It is too useful article for newbie who want to know about how to use file system object.I am mentioning few more code snippet for Use VBA to Compare Data in a Text File as example of my articles for separator character

Sub DoTheImport()
Dim FName As Variant
Dim Sep As String
FName = Application.GetOpenFName(FileFilter:="Text File (*.txt),*.txt")
If FName = False Then
// code snippet to cencellout
Exit Sub
End If
Sep = Application.InputBox("Enter a separator character.", Type:=2)
If Sep = vbNullString Then
// code snippet to cencellout
Exit Sub
End If
Debug.Print "FName: " & FName, "Separator: " & Sep
ImportTextFile FName:=CStr(FName), Sep:=CStr(Sep)
End Sub

  • Do not include your name, "with regards" etc in the comment. Write detailed comment, relevant to the topic.
  • No HTML formatting and links to other web sites are allowed.
  • This is a strictly moderated site. Absolutely no spam allowed.
  • Name: