Results 1 to 8 of 8
  1. #1
    3 Star Lounger
    Join Date
    Nov 2011
    Location
    Australia
    Posts
    221
    Thanks
    80
    Thanked 3 Times in 2 Posts

    VBA txt log file maker for 3 separate .txt log files - needs "overhaul"

    I had a .txt log file maker written on another forum about 6 or 7 years ago,(maybe more) I can't find the author and that forum has since closed or is in limited service.

    The .txt file maker works exactly as needed, it is used throughout my projects to .txt logs and for error searches or checks at specific points.

    Recently, my project required 3rd party external .txt file pick-up for data within the 1st and 2nd txt files.

    I used the original .txt log file maker VBA, but changed the "name.txt" to suit the 2 required .txt files
    by the 3rd party .txt file pick-up.

    In the uploaded workbook there are 4 Modules.

    B_TextMessage_1

    C_TextMessage_2

    D_LOGMESSAGE' original VBA code


    B and C Modules are the required .txt file maker for the 3rd party pick-up, so those 2 are essential.


    I added the 3rd and the original .txt file maker in a separate Module for error checks and record keeping, as usual.

    ---------
    3rd, but first listed Module:
    A_test_messages Module is where the VBA
    Activates all 3 .txt file makers as such;

    Code:
    Sub test_3_messages()
    
    LOGMESSAGE1 Sheets("Sheet1").Range("C13").Value & "-" & Sheets("Sheet1").Range("C14").Value
    
    LOGMESSAGE2 Sheets("Sheet1").Range("C7").Value & "-" & Sheets("Sheet1").Range("C8").Value _
                & "-" & Sheets("Sheet1").Range("C9").Value & "-" & Sheets("Sheet1").Range("C10").Value _
                  & "-" & Sheets("Sheet1").Range("C11").Value & "-" & Sheets("Sheet1").Range("C12").Value
    
    '3rd and original .txt file maker.             
    LOGMESSAGE Sheets("Sheet1").Range("G8").Value & "-" & Sheets("Sheet1").Range("G9").Value
    
    End Sub

    For testing,
    the .txt file maker's directory Folder is set at;

    Important for testing to make it work
    C:\LOGMESSAGETEST' this is where the .txt log files will be when VBA is Activated.
    Sub test_3_messages()
    '-----------------------

    The Question is:
    Is there a way to update the original .txt file maker VBA code,(In Module D_) so it's all in one VBA coding and able to add or reduce extra .txt file makers but named otherwise if required ?

    Or do I have to use a separate a Module for each and every newly named .txt file in the same directory in
    C:\LOGMESSAGETEST ?

    Thanks.
    Attached Files Attached Files

  2. #2
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,434
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    XPD,

    This single module will cover any file you want to write to:
    Code:
    Public Sub LOGMESSAGE(zLogFile As String, strMessage As String)
    
       Dim iFileNumber  As Integer
       Dim zzLogFile As String
    
         '// change name to suit.
         
    
        zLogFile = "C:\LOGMESSAGETEST\" & zLogFile & ".txt"
    
        On Error GoTo ErrHandler
         '// Get the next unused file number
         '// Most likely will always be 1
        iFileNumber = FreeFile
    
        If iFileNumber > 0 Then
    
             '// 'Open' will create the file if it doesn't already exist
            Open zLogFile For Append As #iFileNumber
            Write #iFileNumber, Format$(Now, "yyyymmdd-hh:nn:ss") & ">" & strMessage
        Else
            MsgBox "Error getting open file handle...", vbExclamation, "Log Error"
        End If
    
    EndProc:
    
         '// If there's an open file handle,
         '// Close it
        If iFileNumber > 0 Then
            Close #iFileNumber
        End If
    
        Exit Sub
    
    ErrHandler:
    
         '// Just a general error message
        MsgBox "Error writing log... (" & Err.Number & ": " & Err.Description & ")", vbExclamation, "Log Error"
        Resume EndProc
    
    End Sub
    You just need to change your calling sequences to:
    Code:
    Sub test_3_messages()
    'works OK
    LOGMESSAGE "LogFile1", Sheets("Sheet1").Range("C13").Value & "-" & Sheets("Sheet1").Range("C14").Value
    
    '---------
    'works ok
    LOGMESSAGE "LogFile2", Sheets("Sheet1").Range("C7").Value & "-" & Sheets("Sheet1").Range("C8").Value _
                & "-" & Sheets("Sheet1").Range("C9").Value & "-" & Sheets("Sheet1").Range("C10").Value _
                  & "-" & Sheets("Sheet1").Range("C11").Value & "-" & Sheets("Sheet1").Range("C12").Value
                  
    '---------
    '3rd and original .txt file maker
    LOGMESSAGE "LogFile", Sheets("Sheet1").Range("G8").Value & "-" & Sheets("Sheet1").Range("G9").Value
    
    Sheets("Sheet1").Range("C2").Value = "log file success at Folder C:\LOGMESSAGETEST"
    Sheets("Sheet1").Range("G2").Value = Date
    Sheets("Sheet1").Range("H2").Value = Time
    
    ActiveWorkbook.Save
    
    End Sub
    Results:
    xpd1.JPG
    Code:
    Contents of: LogFile.txt:
    
    "20150723-21:06:34>GENERAL MESSAGE 1-GENERAL MESSAGE 2"
    
    Contents of: LogFile1.txt:
    
    "20150723-21:06:34>EMAIL@ADDRES.COM-DATA456"
    
    Contents of: LogFile2.txt:
    
    "20150723-21:06:34>AB-CD-1-2-3-4"
    HTH
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  3. The Following User Says Thank You to RetiredGeek For This Useful Post:

    XPDiHard (2015-07-27)

  4. #3
    3 Star Lounger
    Join Date
    Nov 2011
    Location
    Australia
    Posts
    221
    Thanks
    80
    Thanked 3 Times in 2 Posts
    RG, I tested your code changes, but, the point about 2 of the files the 3rd party program reads, has to be specific, which is out of my control and that's ok.

    The original LOGMESSAGE code has time and date stamps, OK, Good
    Code:
    '// 'Open' will create the file if it doesn't already exist
            Open zLogFile For Append As #iFileNumber
            Write #iFileNumber, Format$(Now, "yyyymmdd-hh:nn:ss") & ">" & strMessage
    Because the 3rd party program uses the minimal amount of required data, that section of the original LOGMESSAGER has been cut out, it looks like:
    Code:
     // 'Open' will create the file if it doesn't already exist
            Open strFileName For Output As #iFileNumber
             Print #iFileNumber, strMessage

    What I was hoping, if both choices can be implemented in the one single code, by way of adding a point of reference/match when the code is Run
    So if it is a time/date stamp logmessage, it's that, but if it is for the 3rd party it has time/date omitted.
    Can the VBA be written in such a way to omit the time/date stamp if not required ?

    And 2ndly,
    if the 3rd party program requires a 3rd, more or less .txt files, I simply add another of the same lines of code with reference pertaining to the txt files name.

    So it may be
    LOGMESSAGE_ORIGINAL' ( uses the time/date stamp)

    LOGMESSAGE_3RD PARTY_1, ( it's .txt file name would be accordingly so 3rd party "knows" the file.
    eg: 3rd_party_1TEXT.txt

    LOGMESSAGE_3RD PARTY_2, ( it's .txt file name would be accordingly so 3rd party "knows" the file.
    eg: 3rd_party_2TEXT.txt

    LOGMESSAGE_3RD PARTY_3, ( it's .txt file name would be accordingly so 3rd party "knows" the file.
    eg: 3rd_party_3TEXT.txt

    all in one Module.
    ---------,

    If not, then the above example would have to use 4 separate Modules, 1 Module; LOGMESSAGE / original and 3 more Modules as the requested requirements by the 3rd party software makers.

    ( 3rd party software makers don't give a kahoot on how to make the .txt files, they just want the data inside the .txt files a certain way so it's picked up and read.

    The current way I have it now works,
    just thought if there is a way so to lessen the WorkBook's excesses, or
    "if it ain't broken, don't fix it" ?


    Thanks

  5. #4
    WS Lounge VIP
    Join Date
    Dec 2009
    Location
    Earth
    Posts
    8,175
    Thanks
    47
    Thanked 982 Times in 912 Posts
    Public Sub LOGMESSAGE(zLogFile As String, strMessage As String, Optional bTimeStamp As Boolean = True)
    .
    .
    .
    If bTimeStamp Then
    Write #iFileNumber, Format$(Now, "yyyymmdd-hh:nn:ss") & ">" & strMessage
    Else
    Write #iFileNumber, strMessage
    EndIf


    cheers, Paul

  6. The Following User Says Thank You to Paul T For This Useful Post:

    XPDiHard (2015-07-27)

  7. #5
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,434
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    XPD,

    See if this works for you, i.e. did I understand your requirements?
    Code:
    Option Explicit
    
    Public Sub LOGMESSAGE(zLogFile As String, bTStamp As Boolean, strMessage As String)
    
       Dim iFileNumber   As Integer
       Dim zFullFileName As String
    
         '// change name to suit.
         
    
        zFullFileName = "C:\LOGMESSAGETEST\" & zLogFile & ".txt"
    
        On Error GoTo ErrHandler
         '// Get the next unused file number
         '// Most likely will always be 1
        iFileNumber = FreeFile
    
        If iFileNumber > 0 Then
    
             '// 'Open' will create the file if it doesn't already exist
            Open zFullFileName For Append As #iFileNumber
            
            If bTStamp Then
              Write #iFileNumber, Format$(Now, "yyyymmdd-hh:nn:ss") & ">" & strMessage
            Else
              Write #iFileNumber, strMessage
            End If
            
        Else
            MsgBox "Error getting open file handle...", vbExclamation, "Log Error"
        End If
    
    EndProc:
    
         '// If there's an open file handle,
         '// Close it
        If iFileNumber > 0 Then
            Close #iFileNumber
        End If
    
        Exit Sub
    
    ErrHandler:
    
         '// Just a general error message
        MsgBox "Error writing log... (" & Err.Number & ": " & Err.Description & ")", vbExclamation, "Log Error"
        Resume EndProc
    
    End Sub   'LOGMESSAGE
    Calling Sequence:
    Code:
    Option Explicit
    
    Sub test_3_messages()
    'works OK
    LOGMESSAGE "LogFile1", True, Sheets("Sheet1").Range("C13").Value & "-" & Sheets("Sheet1").Range("C14").Value
    
    '---------
    'works ok
    LOGMESSAGE "LogFile2", False, Sheets("Sheet1").Range("C7").Value & "-" & Sheets("Sheet1").Range("C8").Value _
                & "-" & Sheets("Sheet1").Range("C9").Value & "-" & Sheets("Sheet1").Range("C10").Value _
                  & "-" & Sheets("Sheet1").Range("C11").Value & "-" & Sheets("Sheet1").Range("C12").Value
                  
    '---------
    '3rd and original .txt file maker
    LOGMESSAGE "LogFile", True, Sheets("Sheet1").Range("G8").Value & "-" & Sheets("Sheet1").Range("G9").Value
    
    Sheets("Sheet1").Range("C2").Value = "log file success at Folder C:\LOGMESSAGETEST"
    Sheets("Sheet1").Range("G2").Value = Date
    Sheets("Sheet1").Range("H2").Value = Time
    
    ActiveWorkbook.Save
    
    End Sub
    HTH
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  8. The Following User Says Thank You to RetiredGeek For This Useful Post:

    XPDiHard (2015-07-27)

  9. #6
    3 Star Lounger
    Join Date
    Nov 2011
    Location
    Australia
    Posts
    221
    Thanks
    80
    Thanked 3 Times in 2 Posts
    Update,
    haven't tested the replied codes yet, will do in good time and reply back,

    Thanks

  10. #7
    3 Star Lounger
    Join Date
    Nov 2011
    Location
    Australia
    Posts
    221
    Thanks
    80
    Thanked 3 Times in 2 Posts
    I may have solved it, it works by putting all 3 logging codes in one module, one under the other.

    This is what I thought had to be avoided.

    The reason is this,

    The original logmessage maker, uses a time and date stamp and logs each line one after the other in the txt file as it was meant to do.

    The other 2 new log files, need to be a different file name endings, no date or times stamp and the text within to remain a single line each and every time, it's not really a "history" log, it's a point of pick-up
    for the 3rd party software/scripting.

    I thought there was a way to have all 3 under one Public Sub, but still make the same log files the same as if when separated and still end up with 3 separate txt files named accordingly.
    ----------------
    'Seperated in one Module.
    Public Sub LOGMESSAGE1(strMessage As String)
    'code
    End Sub

    Public Sub LOGMESSAGE2(strMessage As String)
    'code
    End Sub

    Public Sub LOGMESSAGE(strMessage As String)
    code
    End Sub
    ----------------

    Or ( the question )

    'Combined in one Module.
    ----------------------------------------
    Public Sub logfilemakerS(strMessage As String)
    'code

    LOGMESSAGE(strMessage As String)

    LOGMESSAGE1(strMessage As String)

    LOGMESSAGE2(strMessage As String)

    'code
    End Sub
    ----------------------------------------
    I see the problem now, how would it be called ?

    Sub WorkBook_Project ()

    "codes
    logfilemaker_LOGMESSAGE "test message-time and date stamps " ' general user log files

    logfilemaker_LOGMESSAGE1 "test message 1- no time and date stamps" ' required by 3rd party

    logfilemaker_LOGMESSAGE2 "test message 2- no time and date stamps" ' required by 3rd party

    'codes
    End Sub
    ----------------------------------------

    '??

    The fundamental differance in the coding between time & and date stamps and none
    is this part here

    dated and time logs that append
    '
    Code:
    // 'Open' will create the file if it doesn't already exist
            Open strFileName For Append As #iFileNumber
            Write #iFileNumber, Format$(Now, "yyyymmdd-hh:nn:ss") & ">" & strMessage
    ' no dated and time logs
    Code:
      '// 'Open' will create the file if it doesn't already exist
            Open strFileName For Output As #iFileNumber
             Print #iFileNumber, strMessage
    Note
    To test the uploaded "fixed" workbook for testing,
    it's set to work in a directory named:
    C:\LOGMESSAGETEST
    Attached Files Attached Files

  11. #8
    WS Lounge VIP
    Join Date
    Dec 2009
    Location
    Earth
    Posts
    8,175
    Thanks
    47
    Thanked 982 Times in 912 Posts
    Unlike the Print # statement, the Write # statement inserts commas between items and quotation marks around strings as they are written to the file. [1]

    I would change all "write" statements to "print" in one module and everything should be fine.

    cheers, Paul

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •