Checking a textfile name for changes and enable a label

Wicked_

Board Regular
Joined
Jun 5, 2018
Messages
81
Hi.

Is there a way for excel to check a certain .txt file at startup (in a spesfic folder(and file)) for changes made (like higher number(versions)) then make a label enabled in the program ? (label7.enable)

Example.

Versions.txt (inside the file it says v1.0, then if i change it to example v1.1) then it makes the label 7 enabled.

Thanks :)
 

Some videos you may like

Excel Facts

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.

pbornemeier

Well-known Member
Joined
May 24, 2005
Messages
3,884
If the version number was always in the same place (First line of file??) and the program tracked what the previous version was then it could open the file and search the text for the current version number and enable a textbox if it was changed.
Other options to track changes are:
1. Read the system modification date for the file and compare it to the previous (saved) modified date.
2. Include the version number in the filename (e.g., Certain Text File v1.4.txt).
Neither of these would require the file to be opened and so would be a bit quicker.
 

Wicked_

Board Regular
Joined
Jun 5, 2018
Messages
81
Hi.
Those options seems neat!

Could you give a example how i would make that sort of code ? (both for 1 and 2, if its no problem :) )

If the version number was always in the same place (First line of file??) and the program tracked what the previous version was then it could open the file and search the text for the current version number and enable a textbox if it was changed.
Other options to track changes are:
1. Read the system modification date for the file and compare it to the previous (saved) modified date.
2. Include the version number in the filename (e.g., Certain Text File v1.4.txt).
Neither of these would require the file to be opened and so would be a bit quicker.
 

pbornemeier

Well-known Member
Joined
May 24, 2005
Messages
3,884
This is the version-in-name version. The function at the bottom can be used to convert it to the file-mod-date version

Code:
Option Explicit

Sub ChedkForNewFile()
    
    'Assumes the filenames all end with versioning info something like": "_v1.77.txt"
    'Assumes there is a "File History" worksheet with
    '  Column A that contains the basic filename (name without versioning info)
    '  Column B that contains the versioning info
    
    Dim sFolder As String
    Dim sFileNameWithoutVersion As String
    Dim sFileNameExt As String
    Dim lUSPos As Long
    Dim lDotPos As Long
    Dim sFileVersionInfo As String
    Dim sSavedVersionInfo As String
    Dim lFileCount As Long
    Dim sFileNameWithoutVersioningInfo As String
    Dim sFoundFile As String
    Dim oFound As Object
    Dim lLastHistoryRow As Long
    Dim lUpdateRow As Long
    Dim wksHist As Worksheet
    
    'Update next line with name of your File History worksheet
    Const sFileHistoryWorksheet As String = "File History"
    
    'Update next line with path to file
    sFolder = Environ("userprofile") & "\Documents\"
    'Make sure it ends with a \
    If Right(sFolder, 1) <> "\" Then sFolder = sFolder & "\"
    
    'Update next line with the basic file name (e.g., "SomeFile" if the actual name was "SomeFile_v1.77.txt"
    sFileNameWithoutVersioningInfo = "SomeFile"
    
    Set wksHist = Worksheets(sFileHistoryWorksheet)
    
    'Search the folder for the basic name
    sFileNameExt = UCase(Dir(sFolder & sFileNameWithoutVersioningInfo & "*.txt"))
    Do While sFileNameExt <> vbNullString
        sFoundFile = sFileNameExt
        lFileCount = lFileCount + 1
        sFileNameExt = Dir
    Loop
    'More than one?
    If lFileCount > 1 Then
        MsgBox lFileCount & " files with a name like " & sFileNameWithoutVersioningInfo & _
            " were found in " & sFolder & vbLf & vbLf & _
            "Remove the older files and run code again.", , "Multiple Files Found"
        GoTo End_Sub
    End If
    
    If sFoundFile <> vbNullString Then
        'File Found, get version info
        lUSPos = InStrRev(sFoundFile, "_")
        lDotPos = InStrRev(sFoundFile, ".")
        sFileVersionInfo = Mid(sFoundFile, lUSPos, lDotPos - lUSPos)
    Else
        MsgBox "A file like " & sFoundFile & " was not found in " & sFolder, , "File Not Found"
        GoTo End_Sub
    End If
    
    With wksHist
        .AutoFilterMode = False
        lLastHistoryRow = .Cells(.Rows.Count, 1).End(xlUp).Row
    End With
    
    'Is there an entry in File History
    Set oFound = wksHist.Columns("A:A").Find( _
        What:=sFileNameWithoutVersioningInfo, LookIn:=xlFormulas, _
        LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False, SearchFormat:=False)
    If Not oFound Is Nothing Then
        'match found
        lUpdateRow = oFound.Row
        sSavedVersionInfo = oFound.Offset(0, 1).Value
        If sSavedVersionInfo <> sFileVersionInfo Then
            'Versions differ, Enable label
            MsgBox "New version for file found" & vbLf & vbLf & _
                "   " & sFileNameWithoutVersioningInfo & vbLf & vbLf & _
                "      Previous:" & vbTab & sSavedVersionInfo & vbLf & _
                "      Current:" & vbTab & sFileVersionInfo
            'label7.enable
        End If
        wksHist.Cells(lUpdateRow, 2).Value = sFileVersionInfo
    Else
        'Not Found, add to history
        lUpdateRow = lLastHistoryRow + 1
        wksHist.Cells(lUpdateRow, 1).Value = sFileNameWithoutVersioningInfo
        wksHist.Cells(lUpdateRow, 2).Value = sFileVersionInfo
    End If

End_Sub:
    
End Sub


Function GetModDate(sFilePathName As String) As Date
    'This function will return the date/time of the last file modification
    'Late binding requires no reference to MS Script Runtime !!
    
    Dim fso As Object, f As Object
    Set fso = CreateObject("Scripting.FileSystemObject")
    
    Set f = fso.GetFile(sFilePathName)

    GetModDate = f.DateLastModified
   
    Set f = Nothing
    Set fso = Nothing
End Function
 
Last edited:

Wicked_

Board Regular
Joined
Jun 5, 2018
Messages
81

ADVERTISEMENT

Nice! i´ll try it out tomorrow when i get back to work.
I´ll let you know :)
 

Watch MrExcel Video

Forum statistics

Threads
1,122,884
Messages
5,598,651
Members
414,251
Latest member
oExcel

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top