Solved excel VBA code to produce permutation list to text file

September 4, 2014 at 09:46:29
Specs: Windows 7, i3
I want an excel VBA code that will read from a text file and check each item in text file against all the items in sheet1 to see if it finds any matches. Each match it finds, the rows in the excel sheet1 are highlighted. i hope the experts on this site will be able to help

message edited by Knowledge_is_key


See More: excel VBA code to produce permutation list to text file

Report •


✔ Best Answer
September 4, 2014 at 13:26:09
The code below will do the following:

1 - Read the data in a text file, line by line.
2 - Replace the hyphens with spaces so the strings from the Text file match the strings in the Excel file. The replacement is done in memory...it does not actually change the text file.
3 - Attempt to Find the new string in the Excel file
4 - Highlight the cell if found

Sub CompareAndColorText()

Dim myFile, text_line, fix_string As String

'*** Choose one of the two following lines to access the text file ***'

'This choice hardcodes the full path and filename
    'myFile = "c:\path\filename.txt"
    
'This choice opens a Dialog box so you can Browse to the file
     myFile = Application.GetOpenFilename()

'Open the text file
    Open myFile For Input As #1

'Loop through each line of the text file
      Do Until EOF(1)
'Read a line
        Line Input #1, text_line
'Replace the hyphen with a space
          fix_string = Replace(text_line, "-", " ")
'Search for the matching string
            With Sheets(2).Columns(1)
              Set c = .Find(fix_string)
'If found, color the cell Red
              If Not c Is Nothing Then c.Interior.ColorIndex = 3
            End With
      Loop

'Close the file
    Close #1
    
End Sub

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.



#1
September 4, 2014 at 11:24:57
Wouldn't it be easier to just copy the text file into another Excel sheet and then compare the values? It's possible that you can use Conditional Formatting to highlight the rows.

In any case, without knowing the layout of the text file or the Excel sheet, it would hard for us to offer a solution that will fit your exact needs. We need more detail.

For example, you said "check each item in text file against all the items in sheet1"

What's an "item"? Every word/value in both files? If the text file contains "Hello" and an Excel cell contains "Hello Tom!" is that a match? That's the kind of detail we need before we can help.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#2
September 4, 2014 at 11:58:46
My Utmost apologies for not being detailed,

1. excel has a row limitation of 1048576 while the text file has faaar more lines of data so copying text file to excel would not be practical.

2. Text file has data lines, each line has info like 1-2-3-4-5 going down vertically. (note: each number in the line is delimited by "-" like 1-4-5-6 etc) Each line of data is considered an "item".

The excel sheet1 data is in one column, only columnA like 1 2 3 4 5, the data are placed in rows going down (note: each number in the line is delimited by a space like 1 4 5 6 etc)

hope this clears it up :)

message edited by Knowledge_is_key


Report •

#3
September 4, 2014 at 12:53:06
If the text file contains e.g. 1-4-5-6 and the Excel file contains e.g. 1 4 5 6, then there are no matches, correct? 1-4-5-6 will not match 1 4 5 6 in any application that I know of.

In reality you are not looking for matches, but for the same sequence of numbers, correct?

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

message edited by DerbyDad03


Report •

Related Solutions

#4
September 4, 2014 at 13:00:19
Presumably related: http://www.computing.net/answers/pr...

As he's asking for batch, expect any sequences to be vertical, not horizontal.

How To Ask Questions The Smart Way


Report •

#5
September 4, 2014 at 13:26:09
✔ Best Answer
The code below will do the following:

1 - Read the data in a text file, line by line.
2 - Replace the hyphens with spaces so the strings from the Text file match the strings in the Excel file. The replacement is done in memory...it does not actually change the text file.
3 - Attempt to Find the new string in the Excel file
4 - Highlight the cell if found

Sub CompareAndColorText()

Dim myFile, text_line, fix_string As String

'*** Choose one of the two following lines to access the text file ***'

'This choice hardcodes the full path and filename
    'myFile = "c:\path\filename.txt"
    
'This choice opens a Dialog box so you can Browse to the file
     myFile = Application.GetOpenFilename()

'Open the text file
    Open myFile For Input As #1

'Loop through each line of the text file
      Do Until EOF(1)
'Read a line
        Line Input #1, text_line
'Replace the hyphen with a space
          fix_string = Replace(text_line, "-", " ")
'Search for the matching string
            With Sheets(2).Columns(1)
              Set c = .Find(fix_string)
'If found, color the cell Red
              If Not c Is Nothing Then c.Interior.ColorIndex = 3
            End With
      Loop

'Close the file
    Close #1
    
End Sub

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#6
September 4, 2014 at 14:59:59
WOW, this is brilliant work, i'm a newby programmer and hope to be as great as you guys one day. The problem is how do i get to be as great as u guys? and what language i'm to focus on? i'm thinking VBA and batch coding but my friend seems to think C# is the best thing since slice bread....can u guys give me advice please

Report •

#7
September 5, 2014 at 04:59:18
While your most recent question should be in it's own thread since it has nothing to do with comparing a text file to an Excel file, I'll give you a quick answer, although it's not really an answer.

Asking us whether you should focus on VBA, Batch or C is like asking us if you should buy a car, a van or a pickup truck. What is the purpose of the vehicle?

At the highest level...

...VBA is specific to an application
...Batch is related to Operating Systems
...C is more of a cross platform language, but that doesn't mean you can use C for everything and eliminate VBA or Batch

I don't think anyone can tell you which language to focus on unless they know what your ultimate goal is.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#8
September 5, 2014 at 06:29:27
Don't go with batch. Batch is a pseudo language that no one likes, is technically depreciated, and has more gotcha than actual rules.

C#'s fine. Python's good and multi-platformed. If you want to just focus on scripting, download the v3 of PowerShell and play with that.

How To Ask Questions The Smart Way


Report •

#9
September 5, 2014 at 11:23:29
this is the second day of trying without success, i cant seem to get it to work. After the i select the text file location nothing happens. Is it possible to send the actual working copy through this medium?

Report •

#10
September 5, 2014 at 14:00:15
Why did you say that my response was "brilliant" if you didn't get it to work? Typically, compliments are not due until the solution works. In any case...

You will note that the code is trying to find matches on Sheet(2). If you are looking for matches on a different sheet, change the number to correspond to the correct sheet or put the actual name of the sheet inside the parenthesis, with quotes around the sheet name.

e.g.

With Sheets(”My Sheet Name")

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#11
September 5, 2014 at 15:20:21
i saw the code at first and assumed it worked, sorry. i have put data in the text file exactly--->3-3-3-3 while i placed data in sheet(2)---> 3 3 3 3 in cell(1,1). i expect cell(1,1) to be highlighted but nothing still seems to work..can u send me an actual working copy u tested.

Report •

#12
September 6, 2014 at 08:34:20
Unfortunately, I am traveling for the next few days and don't have easy access to Excel. I'm working from an iPad.

I basically did the same thing as you described, except that I used hundreds of randomly generated strings as my test and it worked fine.

1 - I put this formula in A1 and dragged it down hundreds of rows:

=RANDBETWEEN(0,9)&"-"&RANDBETWEEN(0,9)&"-"&RANDBETWEEN(0,9)&"-"&RANDBETWEEN(0,9)

This created strings like 3-6-6-7

2 - I then copied the strings into Notepad and saved the txt file.

3 - Back in Excel, I used the same formula as above but used spaces instead of hyphens to create random strings with spaces, e.g. 3 6 2 9

4 - I selected Column A and did a Copy...PasteSpecial...Values to lock in the strings.

5 - I ran the macro and the code highlighted the "matching" strings.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#13
September 6, 2014 at 10:53:25
For what it's worth,
I followed DerbyDad03's instructions and it worked for me,
using Excel 2007 & a text filed created with JEdit.

In the Macro I specify the file using both the:

'This choice hardcodes the full path and filename
myFile = "C:\Users\Mike\Documents\Untitled-1.txt"

and the Browse option:

'This choice opens a Dialog box so you can Browse to the file
myFile = Application.GetOpenFilename()

and both choices produced the desired result.

I used 1000 random numbers.

MIKE

http://www.skeptic.com/


Report •

#14
September 6, 2014 at 11:15:10
Played around with this for a while and just as I thought,
I found the ubiquitous <Space> character is probably your problem.

In your Text file,
if your string number is either preceded or followed by a <Space> character,
then it will NOT MATCH the string number in the Excel file.
Excel treats a <Space> as a valid character.

You will have to insure that the numbers in your text file DO NOT have a <Space>
or multiple spaces either in front of, or, following your numbers.

What does a line in your Text file actually look like?


MIKE

http://www.skeptic.com/

message edited by mmcconaghy


Report •

#15
September 6, 2014 at 11:58:57
Personally, I'd rather change this line:
fix_string = Replace(text_line, "-", " ")
To:
fix_string = Replace(Trim(text_line), "-", " ")

mmcconaghy: What does a line in your Text file actually look like?
Hint: I linked to it in my first post.

How To Ask Questions The Smart Way

message edited by Razor2.3


Report •

#16
September 6, 2014 at 12:52:03
Nice call Razor2.3,
that solved the <Space> problem.

The reason for "what does a line look like":
First the OP asked for this:

each line has info like 1-2-3-4-5 going down vertically
Which is Five numbers, then he follows it with:

each number in the line is delimited by "-" like 1-4-5-6 etc)
Which is Four numbers.

Just curious, if it is Five Numbers, or Four Numbers, or a Mix of both?


MIKE

http://www.skeptic.com/

message edited by mmcconaghy


Report •

#17
September 6, 2014 at 15:12:24
Gentlemen i feel really silly right now, because i decided to go over the instructions by DerbyDad03 step by step and some how its now working perfectly. Problem solved.

i was right the first time brilliant work, this is truly the most awesome site in the world....thanks again DerbyDad03, Razor2.3, mmcconaghy for your help.

message edited by Knowledge_is_key


Report •

#18
September 6, 2014 at 16:50:14
I'm not surprised that using my instructions worked, but the real question is "Does it work the actual data that you need it to work with?"

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

message edited by DerbyDad03


Report •

#19
September 7, 2014 at 06:34:52
After testing a small sized text file it worked perfectly, but when i tested using a text file over 5Gb in size with the exact same data the entire program freezes up, and when i try to open the text file a message pops up stating "the file is too large for notepad to open". Note: the numbers in the text file were generated for permutations of 6 numbers, each range is 1-39, thats why the huge file size.

I was thinking maybe everything should have been done in excel initially instead of using an external text file, and use combinations instead of permutation.

I will post my other excel question in another thread.


Report •

Ask Question