Log To External File VBA

[Gtrtim112]

I have a file that pings some data to an external file. Everything works great. I'd like to expand on it, if possible, though. This file will be given to 6 different groups of people. Each person will have to select their group from a drop down box. Depending on which group they are in, I need a different external file to be chosen. I don't know if a variable can be added to the current code to add in those different group choices or not. I'm curious if someone might can take a look at it and see if it can be altered to accommodate what I'm trying to achieve?

Sub LogInformation(LogMessage As String)
Const LogFileName As String = "P:\Engineering\Engineering Tool Logs\Group1.txt"
//Const LogFileName As String = "P:\Engineering\Engineering Tool Logs\Group2.txt"
//Const LogFileName As String = "P:\Engineering\Engineering Tool Logs\Group3.txt"

//Can I do something like "P:\Engineering\Engineering Tool Logs\" & " Sheets = (Data Handling) Range = (F2) & ".txt"

Dim FileNum As Integer
FileNum = FreeFile ' next file number
Open LogFileName For Append As #FileNum ' creates the file if it doesn't exist
Print #FileNum, LogMessage ' write information at the end of the text file
Close #FileNum ' close the file
End Sub
Public Sub DisplayLastLogInformation()
Const LogFileName As String = "P:\Engineering\Engineering Tool Logs\Group1.txt"
//Const LogFileName As String = "P:\Engineering\Engineering Tool Logs\Group2.txt"
//Const LogFileName As String = "P:\Engineering\Engineering Tool Logs\Group3.txt"
// Can I do someting like "P:\Engineering\Engineering Tool Logs\" & " Sheets = (Data Handling) Range = (F2) & ".txt"
Dim FileNum As Integer, tLine As String
FileNum = FreeFile ' next file number
Open LogFileName For Input Access Read Shared As #f ' open the file for reading
Do While Not EOF(FileNum)
Line Input #FileNum, tLine ' read a line from the text file
Loop ' until the last line is read
Close #FileNum ' close the file
MsgBox tLine, vbInformation, "Last log information:"
End Sub
Sub DeleteLogFile(FullFileName As String)
On Error Resume Next ' ignore possible errors
Kill FullFileName ' delete the file if it exists and it is possible
On Error GoTo 0 ' break on errors
End Sub

 

[Fluff]

Cross posted VBA To Log To External File - Cell Value Dependent

While we do allow Cross-Posting on this site, we do ask that you please mention you are doing so and provide links in each of the threads pointing to the other thread (see rule 13 here along with the explanation: Forum Rules). This way, other members can see what has already been done in regards to a question, and do not waste time working on a question that may already be answered.

 

[Fluff]

It is possible, but you would not be able to declare them as Const.

 

[Gtrtim112]

Right.... so I'm curious how to write the code correctly.

 

[CephasOz]

How about

Sub LogInformation(ByVal LogMessage As String)
    Dim FileNum As Integer
    FileNum = FreeFile ' next file number
    Open LogFileName() For Append As #FileNum ' creates the file if it doesn't exist
    Print #FileNum, LogMessage ' write information at the end of the text file
    Close #FileNum ' close the file
End Sub

Public Sub DisplayLastLogInformation()
    Dim FileNum As Integer
    Dim tLine As String
    FileNum = FreeFile ' next file number
    Open LogFileName() For Input Access Read Shared As #FileNum ' open the file for reading
    Do While Not EOF(FileNum)
        Line Input #FileNum, tLine ' read a line from the text file
    Loop ' until the last line is read
    Close #FileNum ' close the file
    MsgBox tLine, vbInformation, "Last log information:"
End Sub

Sub DeleteLogFile(ByVal FullFileName As String)
    On Error Resume Next ' ignore possible errors
    Kill FullFileName ' delete the file if it exists and it is possible
    On Error GoTo 0 ' break on errors
End Sub

Function LogFileName() As String
    Const strToken As String = "<num>"
    Const strBase As String = "P:\Engineering\Engineering Tool Logs\Group<num>.txt"
    Dim strNum As String
    strNum = Format(ThisWorkbook.Worksheets("Data Handling").Range("F2").Value)
    LogFileName = Replace(strBase, strToken, strNum)
End Function

 

[Gtrtim112]

How about

Sub LogInformation(ByVal LogMessage As String)
    Dim FileNum As Integer
    FileNum = FreeFile ' next file number
    Open LogFileName() For Append As #FileNum ' creates the file if it doesn't exist
    Print #FileNum, LogMessage ' ......
[/QUOTE]

Very much appreciated! Worked like a charm. You're awesome! Thanks SO much!

 

[CephasOz]

Glad to help.