# VBScript, DO Loops & Variable help please!



## NokiaGuy (Feb 17, 2010)

Hi,

I'm trying to write what should be a simple enough vbscript but am having real trouble. Please note that I am self-taught and my script maybe extremely scrappy to the untrained eye.

I have two files. File 1 contains reference numbers in this format:

```
123456
654321
234567
765432
etc..
```
File 2 is a csv file that has hundreds of records in, column 1 of that file is the reference number. I have been asked to use File 1 as a kind of lookup table, take the first reference number and search through File 2 to see if there is a matching record. If there is, write that record to a separate file.

Here's the script:

```
Option Explicit
Dim objFSO, strReference, newfile, objTextFile, objTextFile2, strRecord

Const ForReading = 1

Set objFSO = CreateObject("Scripting.FileSystemObject")

Set objTextFile = objFSO.OpenTextFile _
    ("c:\cltndr\lookup.txt", ForReading)
	
Do Until objTextFile.AtEndOfStream
strReference = objTextFile.ReadLine	
Set newfile = objFSO.OpenTextFile("C:\CLTNDR\" & strReference & ".txt", 2, True)

	Set objTextFile2 = objFSO.OpenTextFile _
    ("c:\cltndr\RRV112F3_2156.lst", ForReading)
	
	Do Until objTextFile2.AtEndOfStream
	strRecord = objTextFile2.ReadLine
	
	If Instr(strRecord,strReference) Then
	newfile.WriteLine strRecord
	newfile.Close
	End If
	Loop 

Loop 

objTextFile.Close
objTextFile2.Close

msgbox "Script Ends."
```
My problem: I'm getting an error within the second Do Loop stating that the, "Object variable not set" on Line 21:

```
If Instr(strRecord,strReference) Then
```
I'm clearly doing something wrong - do variables not carry between Do..Loops?

As stated at the top, I'm self taught and the code above may look horrific to some - but either way, I value any help.

Thanks for reading,
Neil.


----------



## TheOutcaste (Mar 19, 2009)

Welcome to TSF!

I copied your code, changed the file paths to match my test folder setup, and it runs without a problem.
My test data file was just "reference number, plain text", only one comma but that shouldn't make a difference.

There may be something in the csv file that it doesn't like.
I'd add these lines right before line 21, see if you can see which variable it's complaining about.

WScript.Echo "strRecord is " & strRecord
WScript.Echo "strReference is " & strReference

Comment out the If statement if needed, see if it will list all the lines in the csv file.

I'd run it using cscript in a command prompt though, or you'll have to click OK for every line in the csv file, for every reference number.


----------



## NokiaGuy (Feb 17, 2010)

Hi,

thanks very much for replying. I ended up spending some more time on things my end (prior to your answer) and just kept getting the same kind of errors. In the end, I too thought of the csv file and so I recreated it...When I ran it again, it worked first time :sigh: Taught me a valuable lesson for the future to check _everything_.

I'm now working on a new script that goes reopens the same files and this time, if it finds a matching record, it removes it from the original file completely. My only trouble is the Do Loop again - here's what I've got:

```
Const ForReading = 1
Const ForWriting = 2

Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objTextFile = objFSO.OpenTextFile("c:\cltndr\lookup.txt", ForReading)
Set newfile = objFSO.OpenTextFile("C:\CLTNDR\FileWithRemovedRecords.lst", 2, True)

Do Until objTextFile.AtEndOfStream
strReference = objTextFile.ReadLine	

	Set objTextFile2 = objFSO.OpenTextFile _
    ("c:\cltndr\RRV112F3_2156.lst", ForReading)
	
	             Do Until objTextFile2.AtEndOfStream
		strRecord = objTextFile2.ReadLine
		If Instr(strRecord,strReference) = 0 Then
		strNewContents = strNewContents & strRecord & vbCRLF
		End If
		Loop 
Loop 
NewFile.Write strNewContents
objTextFile.Close
newfile.Close

msgbox "Script Ends."
```
It _kind of _ works but (you may have already spotted this) due to the nature of the Do Loops, I end up with a file containing duplicate records. This is how I think it's working:

Loop 1 Starts
Read first line of lookup.txt
Open RRV112F3_2156.lst and read first line
If record from first line of lookup.txt not found then store in strNewContents
Loop to top and read second line of RRV112F3_2156.lst 
etc...To end of RRV112F3_2156.lst
******

At this point, my file would be ok, but the problem lies in the repeated loop of Loop 1. Due to my file being written everytime a record isn't found, it fills up the file with repeated records. Example, if I have just one number in my lookup.txt (123456) and in my example file, I have three records:

123456
654321
234561

On the first loop, I will end up with:

654321
234561

in my File 'WithRemovedRecords.lst' file. On the second run of that loop, I will get those records again - and again on the third run. So I will end up with a file looking like this:

654321
234561
654321
234561
654321
234561

Any idea how I can prevent this?

Thanks for reading! (I'm well known at work for writing an essay where 20 words probably would have sufficed!)

Neil.


----------



## gtk29 (Oct 3, 2008)

You will have to do reverse lookup this time.

Open CSV and go line by line, check if record is in lookup.txt. If no, then write it to new records file.


----------



## NokiaGuy (Feb 17, 2010)

Thanks for the reply gtk29...Spent pretty much all day on it but getting various (but not the correct!) results...

I'll keep working on it over the weekend :smile:


----------



## TheOutcaste (Mar 19, 2009)

One way would be to read the lookup data into an array, then check each line against the array.

```
Option Explicit

Const ForReading = 1
Const ForWriting = 2

Dim strRecord, strDataFile, strLookFile, strNewfile, strNewContents, strFlag
Dim objFSO, objDataFile, objFile, objNewfile, i, arrLines()

strDataFile = "c:\cltndr\RRV112F3_2156.lst"
strLookFile = "c:\cltndr\lookup.txt"
strNewFile = "C:\CLTNDR\FileWithRemovedRecords.lst"

Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objFile = objFSO.OpenTextFile(strLookFile, ForReading)
Set objNewfile = objFSO.OpenTextFile(strNewFile, ForWriting, True)

' Read in the lookup text
i = 0

Do Until objFile.AtEndOfStream
    Redim Preserve arrLines(i)
    arrLines(i) = objFile.ReadLine
    i = i + 1
Loop

objFile.Close

Set objDataFile = objFSO.OpenTextFile (strDataFile, ForReading)

' Read each line, then loop through array to see
' if it contains the data we are looking for.
' Set a flag if it's found

Do Until objDataFile.AtEndOfStream
  strRecord = objDataFile.ReadLine
  strFlag = "notfound"
  For i = Lbound(arrLines) to UBound(arrLines) Step 1
    If Instr(strRecord,arrLines(i)) > 0 Then strFlag = "found"
  Next
  If strFlag = "notfound" Then objNewfile.WriteLine strRecord
Loop

objDataFile.Close
objNewfile.Close

msgbox "Script Ends."
```
You could easily use an If-Then-Elseif to save the Deleted lines into a different file.

HTH

Jerry


----------



## NokiaGuy (Feb 17, 2010)

TheOutcaste,

I bow down to your genius ray: It works perfectly...Thank-you so much for your help. Ever had one of those, "5 minutes before I leave on a Friday afternoon and this is going to bug me all weekend" days? Well 6 minutes ago, I had that feeling! Now, the outlook is a lot brighter!

Thanks again,
Neil.


----------

