Open File for Input, EOF Problem

[joel009]

Hi All, I need some help.
I inherited an Excel Macro system set up for payroll and financial evaluation and suddenly I have encountered a problem with an established routine no longer performing as expected.

We write to a file:
Public Function WriteExc(filename, sht, rng)
'Writes data to file
'variables for filename to write to, sheet to write from
'and starting range

Open filename For Output As #1
Sheets(sht).Select
Range(rng).Select
While ActiveCell.offset(0, 2) > 0
Write #1, RTrim(ActiveCell), RTrim(ActiveCell.offset(0, 1)), _
ActiveCell.offset(0, 2), ActiveCell.offset(0, 3), _
ActiveCell.offset(0, 4), ActiveCell.offset(0, 5), _
ActiveCell.offset(0, 6), ActiveCell.offset(0, 7), _
ActiveCell.offset(0, 8), ActiveCell.offset(0, 9), _
ActiveCell.offset(0, 10), RTrim(ActiveCell.offset(0, 11)), _
ActiveCell.offset(0, 12), ActiveCell.offset(0, 13), _
ActiveCell.offset(0, 14), ActiveCell.offset(0, 15), _
ActiveCell.offset(0, 16), ActiveCell.offset(0, 17), _
ActiveCell.offset(0, 18), ActiveCell.offset(0, 19), _
ActiveCell.offset(0, 20)
ActiveCell.offset(1, 0).Activate
Wend
Close #1

End Function

This is working as expected although I would like to trim every entry, but it has not been changed.

We then read the file back in later with this procedure:

Public Function ReadExc(filename, rpt)
'Reads data from file
'variable is passed for filename to read from
'and whether it's the "CLIENT" report or "HOURS" report

On Error GoTo ErrorHandler

Open filename For Input As #1

FileLength = LOF(1) 'I added this to trouble shoot

While Not EOF(1)
Input #1, fname, lname, we, Amt, regHrs, regRate, otHrs, _
OTRate, dblhrs, dblrate, miscAmt, MiscDesc, Invoice, Branch, _
OrdNum, Status, OrigWE, client, ssnum, extra1, extra2

ActiveCell = rpt
ActiveCell.offset(0, 1) = fname
ActiveCell.offset(0, 2) = lname
ActiveCell.offset(0, 3) = we
ActiveCell.offset(0, 4) = Amt
ActiveCell.offset(0, 5) = regHrs
ActiveCell.offset(0, 6) = regRate
ActiveCell.offset(0, 7) = otHrs
ActiveCell.offset(0, 8) = OTRate
ActiveCell.offset(0, 9) = dblhrs
ActiveCell.offset(0, 10) = dblrate
ActiveCell.offset(0, 11) = miscAmt
ActiveCell.offset(0, 12) = MiscDesc
ActiveCell.offset(0, 13) = Invoice
ActiveCell.offset(0, 14) = Branch
ActiveCell.offset(0, 15) = OrdNum
ActiveCell.offset(0, 16) = Status
ActiveCell.offset(0, 17) = OrigWE
ActiveCell.offset(0, 18) = client
ActiveCell.offset(0, 19) = ssnum
ActiveCell.offset(0, 20) = extra1
ActiveCell.offset(0, 21) = extra2
ActiveCell.offset(1, 0).Activate
Wend
Close #1

ErrorHandler:
Select Case Err.Number
Case 62
Close #1
Exit Function
End Select
End Function
When the problem presented I added the error handling and it will allow the processing to proceed to completion.

My question/problem is - What is going on. The FileLength returns 22000+ records but we are only writting in 157. I am trying to figure out why. There are no blank entries at the end of the file so why is the Open action returning such a wrong number. I lean towards the input used to create the file but can easily see it is not writting 22000+ line.

Newer to Excel VBA and need some advice.


Also, can someone explain to me if this is a valid technique (Possibly another thread is necessary).

While comparing string values, We sort by FN and LN, then compare rows. I am trying to understand the code as I see it and how it works.
We have many loops that are set up like

While activecell < activecell.offset (0,1)
'do something
activecell = activecewll.offset (1,0).activate
else
'do something else
activecell = activecell.offset (1,0).activate
wend

This seems to work very well and I am surprised. May be it is the sorting alphabetically that makes it work but why is Adams < Aezzzzzzzzzzzzzzzzzzz?

My preference would be if I chose to stay with a While statement:

while activecell <> activecell.offset(0,1)
'do something
activecell = activecewll.offset (1,0).activate
else
'do something else
activecell = activecell.offset (1,0).activate
wend

Is it just my personal preference or????


Thanks in Advance


Joel

 

[Andrzejek]

[tt]
FileLength = LOF(1) [green]'I added this to trouble shoot[/green]
[/tt]
"The FileLength returns 22000+ records " - no, it does not.

It returns 22000+ characters in the file, not the "records".

Have fun.

---- Andy

 

[joel009]

OK I hacked my way through this.
The spreadsheet contained extra "'s and affected the import.
Johnson, Mike "Mickey"
Replaced all " with nothing and everything is working as usual.

Thanks,
Joel

Joel