File System Object

[sassriverrat]

Good Morning,

I'm working on an error handler and stumbled upon this wonderful section of code, available from https://bettersolutions.com/vba/error-handling/log-file.htm

Anyway, I don't want to enable File System Object in my reference library so I tried modifying some script to this

Dim g_objFSO As ObjectDim g_scrText As Object
Set g_objFSO = CreateObject("Scripting.FileSystemObject")
Set g_scrText = CreateObject("Scripting.TextStream")

and it hasn't worked. I was hoping someone might have an appropriate way to fix this. Below is the total compilation of the code. All of it has been placed in it's own module in VBA and then the "Call Error_Handle" piece in the code of my stuff (see below as well). Maybe I've done something else wrong here. Thanks!

Total Error Coding

'added to the top of a section of my code
Const sProcName As String = "Instructional"
On Error GoTo Helper

'taken from mine
'Error Clearing Code
Exit Sub
Helper:
    resp = MsgBox("We're sorry to see you've encountered an error." & vbCrLf & vbCrLf & "To proceed, we recommend you contact the Developer " & _
    "with error codes [1126] and " & "[" & Err.Number & "-" & Err.Description & "]." & vbCrLf & vbCrLf & "To attempt to patch your problem at least " & _
    "temporarily, we recommend you click [Yes] to see help directions. Would you like to continue?", vbYesNoCancel, name)
        If resp = vbYes Then
            UserForm18.Show
            Call Error_Handle(sProcName, Err.Number, Err.Description)
            'Dim ermsg As String
            'ermsg = Environ("Userprofile") & "" & Sheets("Developer").Range("E44") & "" & "Error Msg " & Format(Date, mm - dd - yyyy) & ".txt"
            'Open ermsg For Output As #1 
            'Write #1 , "error codes [1126] and " & "[" & Err.Number & "-" & Err.Description
            'Close #1 
            
        ElseIf resp = vbNo Then
            Exit Sub
        ElseIf resp = vbCancel Then
            Exit Sub
        End If

'now here-on down goes in its own module I believe (see website)
Public Sub Error_Handle(ByVal sRoutineName As String, _ 
                         ByVal sErrorNo As String, _ 
                         ByVal sErrorDescription As String) 
Dim sMessage As String 
   sMessage = sErrorNo & " - " & sErrorDescription 
   Call MsgBox(sMessage, vbCritical, sRoutineName & " - Error") 
   Call LogFile_WriteError(sRoutineName, sMessage) 
End Sub 


Public Function LogFile_WriteError(ByVal sRoutineName As String, _
                             ByVal sMessage As String)


Dim g_objFSO As Object
Dim g_scrText As Object
Set g_objFSO = CreateObject("Scripting.FileSystemObject")
Set g_scrText = CreateObject("Scripting.TextStream")
Dim sText As String
   On Error GoTo ErrorHandler
   
   If (g_scrText Is Nothing) Then
      If (g_objFSO.FileExists("C:\temp\mylog.txt") = False) Then
         Set g_scrText = g_objFSO.OpenTextFile("C:\temp\mylog.txt", IOMode.ForWriting, True)
      Else
         Set g_scrText = g_objFSO.OpenTextFile("C:\temp\mylog.txt", IOMode.ForAppending)
      End If
   End If
   sText = sText & "" & vbCrLf
   sText = sText & Format(Date, "dd MMM yyyy") & "-" & Time() & vbCrLf
   sText = sText & " " & sRoutineName & vbCrLf
   sText = sText & " " & sMessage & vbCrLf
   g_scrText.WriteLine sText
   g_scrText.Close
   Set g_scrText = Nothing
   Exit Function
ErrorHandler:
   Set g_scrText = Nothing
   Call MsgBox("Unable to write to log file", vbCritical, "LogFile_WriteError")
End Function


'These were from the website but I tried tweaking the previous function to add these in...maybe that was the mistake

Public g_objFSO As Scripting.FileSystemObject 
Public g_scrText As Scripting.TextStream 

        
End Sub
[COLOR=[URL="https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=1"]#1[/URL] 24080]



[/COLOR]

 

[RoryA]

Remove this line:

Set g_scrText = CreateObject("Scripting.TextStream")

 

[sassriverrat]

Awesome. Ok, that's a start. However, now I'm getting a new error:

      If (g_scrText Is Nothing) Then
      If (g_objFSO.FileExists("C:\temp\mylog.txt") = False) Then
         Set g_scrText = g_objFSO.OpenTextFile("C:\temp\mylog.txt", IOMode.ForWriting, True)
     
         'This line fails: Set g_scrText = g_objFSO.OpenTextFile("C:\temp\mylog.txt", IOMode.ForWriting, True)


      End If

says "Object required" and in the msgbox from the previous function (1st) it's giving me for the error code it's giving me a "0-" when I know that's not the true error.

 

[RoryA]

Replace IOMode.Forwriting with 2 and IOMode.Forappending with 8?

 

[sassriverrat]

@RoryA

you, sir, are, yet again, AMAZING! Thank you.

Pure Learning Moment: Why did that solve the problem?

 

[RoryA]

IOMOde is an enumeration declared in the Scripting Runtime library. If you don't have a reference set to that library, you can't use the enumeration, you have to use the literal values.

 

[Norie]

Instead of replacing the constants with their values you could add this at the top of the module,

Const ForAppending = 8
Const ForWriting = 2

then instead of IOMode.ForAppending and IOMode.ForWriting use ForAppending and ForWriting respectively.

 

[sassriverrat]

Thank you for the explanations and alternative ideas gentlemen! This worked perfectly and is exactly what I've been looking for. Thank you!

 

[sassriverrat]

So one last question here: I'd like to save the module that error'd into that log file. Is this easily possible?

 

[Norie]

Do you mean you want to determine which module the error occurred in and record that in the log along with the other information?

If you do then you could try something like this:

1 Add something similar to this at the top of each module, but instead of identifying the procedure it identifies the module.

'added to the top of a section of my code
Const sProcName As String = "Instructional"

Const sModuleName As String = "ThisModule"

2 Change the call to the error logger so it includes the module name as well as the procedure, something like this perhaps.

 Call Error_Handle(sModuleName & "-" & sProcName, Err.Number, Err.Description)

That should get you started but there are probably other changes that could make things a bit more 'polished'.

PS You might want to declare sProcName and sModuleName as global variables rather than constants.

If you did that you would set their values at the start of each procedure.