Resources » Tools and Utilities » Langauge migration, conversion

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


Posted Date: 23-Dec-2012  Last Updated:   Category: Langauge migration, conversion    
Author: Member Level: Diamond    Points: 15


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


Did you like this resource? Share it with your friends and show your love!

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

No responses found. Be the first to respond...

Feedbacks      

Post Comment:




  • 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:   Sign In to fill automatically.
    Email: (Will not be published, but required to validate comment)



    Type the numbers and letters shown on the left.


    Submit Article     Return to Article Index

    Subscribe to Subscribers
    Active Members
    TodayLast 7 Daysmore...

    Awards & Gifts
    Talk to Webmaster Tony John

    Online Members

    More...
    Copyright © SpiderWorks Technologies Pvt Ltd., Kochi, India