.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 have just re-read your original post and, yes, you're quite right. 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

    'loop until able to get the file or until time limit is reached
    startTime = Timer
    On Error Resume Next
    Do
        Set objFile = objFSO.GetFile(textFilename)
        DoEvents
    Loop Until (Not objFile Is Nothing) Or (Timer - startTime > SECS_TO_WAIT)
    On Error GoTo 0
    
    Set objTextStream = objFile.OpenAsTextStream(1, 0)
  
    'print each line to the Immediate Window
    Do Until objTextStream.AtEndOfStream
        Debug.Print objTextStream.readline
    Loop
    
    objTextStream.Close

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

 

[roscoe]

I'll have to try that, thanks. Note though that integer time in excel is in days, so 10 secounds would have to be 10/24/60/60

 

[Domenic]

Actually, the Timer function returns the elapsed time since midnight in seconds, so there's no need to make any adjustments.

 

[roscoe]

Did not know that...thanks

 

[roscoe]

So I tried the code...it appeared to work except now it crashes on the objFile.OpenAsTextStream line because objFile had not yet been "gotten". I need a step that if it exits the loop based on time rather success it exits the subroutine.

 

[Domenic]

My apologies, somehow I overlooked it. So I've amended the code accordingly. Also, let me know if it's necessary to create a loop for the textstream as well. In any case, please let me know whether the macro works as desired.

'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

    'loop until able to get the file or until time limit is reached
    startTime = Timer
    On Error Resume Next
    Do
        Set objFile = objFSO.GetFile(textFilename)
        DoEvents
    Loop Until (Not objFile Is Nothing) Or (Timer - startTime > SECS_TO_WAIT)
    On Error GoTo 0
   
    'check whether getting the file was successful
    If objFile Is Nothing Then
        MsgBox "Timed out!", vbExclamation
        Set objFSO = Nothing
        Exit Sub
    End If
   
    Set objTextStream = objFile.OpenAsTextStream(1, 0)

    'print each line to the Immediate Window
    Do Until objTextStream.AtEndOfStream
        Debug.Print objTextStream.readline
    Loop
   
    objTextStream.Close

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

End Sub

 

[roscoe]

That was more elegant than what I was thinking...let me give that a whirl. Thanks

 

[Domenic]

Sounds good

 

[roscoe]

#sigh
First, these failures rarely occur on my machine. Our organization works with thin clients and virtual machines, menaingw eare heavily dependant on out network. Not every thin client is identical, nor is everyone's individual profile (i.e. amount of assigned memory, CPUs, etc...). This is why I suspect hardware config is the issue.

That said, for a particluarly gentleman, the code is halting on the following line (as in highlighted yellow in the debugger)

If objFile Is Nothing Then

I can think of no reason why the code should fail on this line

 

[Domenic]

Hmmm... yeah, I don't see how an error can occur on that line. I would suggest trying it again, and if the error in fact occurs on that line, I would add the following code just before If objFile Is Nothing Then, simply for testing purposes. The message box should display File, if successful. Otherwise it should display Nothing.

    MsgBox TypeName(objFile)