.getfile is failing...sortof

[roscoe]

I have code that imports the contents of a text file. On rare occasions it fails. Here's the code in question (please ignore typos as they are not relevant to the problem but merely reflect my poor typing skills):

Set urlObj = CreateObject("Scripting.FileSystemObject")
Set cHTML = urlobj.getfile(TextFileName)
Set cinfo = cHTML.OpenAstextStream(1,0)

Note: I stole this code from someone else who was reading in html files and until now has worked fine for non-HTML text files (if that mattered at all). In fact I use it in multiple locations (i.e. opening other text files) and it has NEVER failed (assuming the file actually exists).

The issue: On rare occasions (randomly and unable to reproduce "on command") the code just stops at the second line above relative to a specific file. I've found that if I select "debug" to open the code I can then just hit F5 (without doing anything else) and it will continue normally. More often than not it doesn't fail and continues through this step normally.

What I have observed is that the the specific file being opened (i.e the one that is failing) is very large (>750,000 lines). I suspect that because of the size it takes a smidge longer than normal for the operating system (OS) to process the line and therefore is subject to failing. I'm wondering (seeking your wise guidance here) if there's some code I can add that will maybe add a few "beats" to the code around this line to allow the OS to keep up and prevent the random fails.

Thanks in advance!

 

[Domenic]

I just tried a number of times to open a file that contains 1,000,000 lines of text using your code. I encountered no problems, and it was fairly quick. So there must be something else going on.

When the error occurs, what type of error are you getting?

Also, when the error occurs, what has TextFileName been assigned?

 

[roscoe]

The error is vague so I don't recall. Since I can't reproduce on command I can't just pull it up. It's an exception of some kind, but since simply restarting the code once the debugger window opens works, the code syntax must be OK. And the textFileName is always the correct name. This is the whole part that bugs me...it works almost all of the time, and when it doesn't a simple F5 fixes it. My speculation about filesize and and speed is exactly that...a guess.

 

[Domenic]

Did you try adding DoEvents as Fazza suggested in your other thread?

 

[roscoe]

I haven't had the time yet, but can someone explain what DoEvents actually does? And if it's halting on the second line, and DoEvents is supposed to go AFTER the second line, how will it be invoked? I'm fuzzy on this (as if you couldn't tell).

 

[roscoe]

OK, so I inserted DoEvents after the second line. As I suspected, it failed before ever reaching that line and continued to do so. I then moved it to before the line and it didn't fail...but then I removed it and it still didn't fail, so I could not prove it made a difference (due to the random nature of the failure).

Could I insert a "On error Goto Repeat" just before the sometimes-failing line and put a line "Repeat:" just before that line so if it fails it goes backwards and tries again until it works? Does VBA even allow that? Of course if it did I'd want to insert something in there to prevent an infinte loop scenario.

 

[roscoe]

Anyone?

 

[Jetx227]

I've never used a code like this before, but does just adding a wait time work? Give the file a chance to open, something like:

Application.Wait Now+TimeValue("00:00:05")

This just makes excel wait 5 seconds before continuing

 

[Domenic]

Simply waiting a few seconds, as Jetx227 suggests, might just do the trick. Another way, though, might be to loop until the file is opened successfully. In addition, a timer could be used as well so that it would bail once a certain amount of time has elapsed.

I haven't been able to test it, but here's an example that loops until the file is opened successfully or until 10 seconds has elapsed. You can change the time as you desire.

Does this help?

'force the explicit declaration of variables
Option Explicit

Sub test()

    Const SECS_TO_WAIT As Long = 10 'change as desired
   
    'declare the variables
    Dim objFSO As Object
    Dim objFile As Object
    Dim objTextStream As Object
    Dim textFilename As String
    Dim startTime As Single
   
    'get the name of the text file
    textFilename = "c:\users\domenic\desktop\sample.txt" 'change accordingly
   
    'create an instance of the FileSystemObject
    Set objFSO = CreateObject("Scripting.FileSystemObject")
   
    'check to make sure that the file exists
    If Not objFSO.FileExists(textFilename) Then
        MsgBox "File not found!", vbExclamation
        Set objFSO = Nothing
        Exit Sub
    End If
   
    'get the file
    Set objFile = objFSO.GetFile(textFilename)

    'loop until file is opened successfully or until time limit is reached
    startTime = Timer
    On Error Resume Next
    Do
        Set objTextStream = objFile.OpenAsTextStream(1, 0)
        DoEvents
    Loop Until (Not objTextStream Is Nothing) Or (Timer - startTime > SECS_TO_WAIT)
    On Error GoTo 0
   
    'if file is opened successfully, print each line to the Immediate Window
    If Not objTextStream Is Nothing Then
        Do Until objTextStream.AtEndOfStream
            Debug.Print objTextStream.readline
        Loop
        objTextStream.Close
    Else
        MsgBox "Unable to open file!", vbExclamation
    End If

    'clear from memory
    Set objFSO = Nothing
    Set objFile = Nothing
    Set objTextStream = Nothing
   
End Sub

 

[roscoe]

Thanks, I like that code, it appears to be doing what I requested...but the code is halting on the .getfile line that precedes the timer loop which in my pea brain tells me that's the line I need loop around. Not sure what the test case would be (in place of "Loop Until (Not objTextStream Is Nothing)")