QTP: Function returns empty string from excel/text file

Question

The application converts Excel files to txt. I have to verify each line matches. Below is the function to verify that, but the problem is it sometimes returns empty string from txt file or excel file, while both files have text in those rows/lines.

I get file and folder names, as well as what excel sheet to use (as TabUse) from database

Function excelcomparison (ByRef ObjFolder, ByRef OrgFolder, ByRef originalfile, ByRef targetFile, ByRef TabUse)
print originalfile&":::"&TabUse&" -=VS=- "&targetFile
Dim fsox : Set fsox = CreateObject("Scripting.FileSystemObject")
Dim TargFileRead : Set TargFileRead = fsox.OpenTextFile(targetFile)

Dim OrgExcel : Set OrgExcel = CreateObject("Excel.Application")
'Application.DisplayAlerts = False
OrgExcel.Workbooks.Open(originalfile)
Set vSheet = OrgExcel.ActiveWorkbook.WorkSheets(TabUse)
For rc = 1 To vSheet.UsedRange.Rows.Count
        For cc = 1 To vSheet.UsedRange.Columns.Count
        vtext = (vSheet.cells(rc,cc))
            If vstring="" Then
                vstring=vtext
            Else
                vstring = vstring&vbTab&vtext
            End If
        Next

"Trim" any leading and trailing tabs:

Do 
  If Left(vstring , 1)=ChrW(9) Then
    vstring = MID(vstring, 2)
  Else
    Exit Do
  End If
Loop 
Do 
    If RIGHT(vstring, 1)=ChrW(9) Then
        vstring= REPLACE(RIGHT(vstring, 1),ChrW(9), ChrW(32))
        vstring=Trim(vstring)
    Else
        Exit Do
    End If
Loop

        vstring = Trim(vstring)

Some cells are united in Excel and have height of two or more row. So, skip those excel rows and txt lines:

    If len(vstring)>0 Then
        TargFileText = TargFileRead.ReadLine
        Do 
            If Left(TargFileText, 1)=ChrW(9) Then
                TargFileText = MID(TargFileText, 2)
            Else
                Exit Do
            End If
        Loop 
        Do 
            If RIGHT(TargFileText, 1)=ChrW(9) Then
                TargFileText = REPLACE(RIGHT(TargFileText, 1),ChrW(9), ChrW(32))
                TargFileText=Trim(TargFileText)
            Else
                Exit Do
            End If
        Loop
        TargFileStr = Trim(TargFileText)


        If trim(vstring) = trim(TargFileStr) Then
'           print "match"
            Else
            print "Not Match"
            print "+"&trim(TargFileStr)
            print "*"&trim(vstring)
        End If
    Else
    print "Lenth=0"
    End If
    vstring = ""
    vtext = ""
    TargFileStr=""
Next

OrgExcel.ActiveWorkbook.Close
TargFileRead.Close

fsox = Nothing
TargFileRead = Nothing
vSheet = Nothing
OrgExcel = Nothing
End Function

Problem 1: It does not read some text or excel files, randomly (returns empty string from excel/text file)

Problem 2: It does not close opened Excel and they take huge memory (up to 50 files to be verified)

Question: What needs to be fixed?

Answer 1

I think the problem is arising when you are trying to remove the vbtab from the Right end side of the string in both the excel and the text file.

For Excel, you have used:

If RIGHT(vstring, 1)=ChrW(9) Then
    vstring= REPLACE(RIGHT(vstring, 1),ChrW(9), ChrW(32))       'This may be the source of your problem
    vstring=Trim(vstring)
Else
    Exit Do
End If

Explanation:

You are replacing ChrW(9) with chrw(32) in a string[RIGHT(vstring, 1)] which contains nothing but chrw(9). After you have done this replacement, you are assigning the result[which is chrw(32) or a space] to the variable vstring. After this line, you trim this variable due to which vstring=""

For Text file you have used:

If RIGHT(TargFileText, 1)=ChrW(9) Then
    TargFileText = REPLACE(RIGHT(TargFileText, 1),ChrW(9), ChrW(32))   'This may be the source of the problem
    TargFileText=Trim(TargFileText)
Else
    Exit Do
End If

Explanation:

You are replacing ChrW(9) with chrw(32) in a string[RIGHT(TargFileText, 1)] which contains nothing but chrw(9). After you have done this replacement, you are assigning the result[which is chrw(32) or a space] to the variable TargFileText. After this line, you trim this variable due to which TargFileText=""

SOLUTION: In both the cases, you need to remove the tab from the right side just like the way you removed vbTab from the left end side of the string i.e, by using the MID function:

If RIGHT(vstring, 1)=ChrW(9) Then
    vstring= mid(vstring,1,len(vstring)-1)      'If there is a tab in the right side of the string, just capture till second last character of the string thus excluding the vbTab.
Else
    Exit Do
End If

Similarly, for the text file:

TargFileText= mid(TargFileText,1,len(TargFileText)-1) 

Also, you are reading the text file only when the vstring is not blank. So, if the vstring is blank, the text file "pointer" remains at the same line where as excel row increments by 1. Due to this, you may have incorrect comparisons. To avoid this, you can use Skipline method in the else part as shown below:

 If len(vstring)>0 Then
    '----your code----
    '...
    '...
else
    TargFileRead.Skipline          'so that it skips the line corresponding to the case when vstring is ""
    '--remaining code---
End If

For closing the excel, use the Quit method of the Excel application.

OrgExcel.Quit