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
myFile.Close
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
worksheets(1).activate
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 & ","
Else
txtNotFound = txtNotFound & c & ","
End If
Next
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
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