Illogical import failure

[StaffsLebowski]

Hi,

For the last 6 months on a daily basis I've read a text file into a Worksheet using "Open strFileName For Input Access Read As #1" etc, with values similar to the following:

AAA;BBB;CCC
DDD;EEE;FFF
GGG;HHH;III

Today, Excel crashes when:

1: Line 2 is moved to the start
2: Line 2 is moved to the end
3: Line 2 contains includes the final ;FFF value
4: Line 2's last value (ie: FFF) is changed to something else, ie: XXX
5: Line 2 is removed completely

The only way I can get the file to import, is keeping it on line 2 and removing the last value

A: Re-started computer
B: Used another computer
C: Opened the Excel file with the repair option
D: Deleted the Worksheet and created another

How on earth is this possible???

Thanks for any ideas what may be causing this.

Staffs Lebowski..

 

[Norie]

Where does the import fail?

Is it on this line?

Open strFileName For Input Access Read As #1

PS Have you tried using FreeFile instead of hard-coding #1 ?

FF = FreeFile()

Open strFileName For Input Access Read As #FF

 

[StaffsLebowski]

Hi Norie,

This is the line it fails on:

ThisWorkbook.Worksheets(strWorksheetName).Cells(intRowIndex, intColumnIndex).Value = Replace(varTempVal, strDelimiter, "")

Without a lot of blah, blah, blah to explain why, the same values are imported each time the WB is opened. So, how is it possible that code, that has worked flawlessly for 6 months, without being changed, even confirmed yesterday, doesn't work today, with the exact same data?

If you look at the values entered, it does not make any sense. How can substituting 'FFF' to 'XXX' (or any other value), instantly causes Excel to crash? Not complain, but crash! Note that, I have already read the value from the file as the values are already stored in variables as they passed through the loop - it's when I'm trying to assign that value to the WS.

It makes absolutely no sense..

 

[StaffsLebowski]

OK, I have the issue resolved. But, why?

I haven't quite gotten to the bottom of this yet, but, it appears that another piece of code could not find a value on another sheet. Big deal. I can understand that the process would fail because of it, but, why crash Excel? Especially as there is robust error handling in place and, 'Break on Unhandled Errors' is selected so that, if no value exists, a message would appear. So why now, all of a sudden has it decided to crash??

And it still doesn't explain why, changing a value from 'FFF' to 'XXX' should also cause it to crash. Absolutely bizarre..

God, with a passion do I hate Excel. Excel is so unpredictable at what should be predictable. Can't wait to get back to Access..

Thanks for the input Norie..

 

[Kyle123]

Without seeing your code we aren’t going to be able to help you. I can guarantee that there’ll be a logical explanation though, maybe not obvious though!!