Automatically importing most recent file in folder with VBA every x seconds

[DrZegt]

This is my first time using VBA, so I am not familiar with how it really works. I need a code that continously imports the most recent measurement scan from a certain folder and updates the cells in a certain location with the contents of the txt-file. So far I am using the code below in order to automatically import the file "Scan00.txt" every 10 seconds. Unfortunately the software storing the scans uses padding digits and so the next scan is called "Scan01.txt". I want to modify my code to instead of importing the contents of Scan00, it imports the most recent txt-file in the folder instead.

In the Microsoft Excel Objects folder I have this code:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    Application.OnTime dTime, "Scanimport", , False
End Sub
 
Private Sub Workbook_Open()
    Application.OnTime Now + TimeValue("00:00:10"), "Scanimport"
End Sub

I then have this module:

Sub Scanimport()


Set shFirstQtr = Workbooks(1).Worksheets(1)
Set qtQtrResults = shFirstQtr.QueryTables _
 .Add(Connection:="TEXT;M:\pc\Desktop\Innovasjonsprosjekt\Test\Scan00", _
 Destination:=Range( _
    "K2"))
With qtQtrResults
    Columns("K:L").Select
    Selection.ClearContents
    .Name = "test"
    .FieldNames = True
    .RowNumbers = False
    .FillAdjacentFormulas = False
    .PreserveFormatting = True
    .RefreshOnFileOpen = False
    .RefreshStyle = xlInsertDeleteCells
    .SavePassword = False
    .SaveData = True
    .AdjustColumnWidth = True
    .TextFilePromptOnRefresh = False
    .TextFilePlatform = 737
    .TextFileStartRow = 18
    .TextFileParseType = xlDelimited
    .TextFileTextQualifier = xlTextQualifierDoubleQuote
    .TextFileConsecutiveDelimiter = False
    .TextFileTabDelimiter = True
    .TextFileSemicolonDelimiter = False
    .TextFileCommaDelimiter = False
    .TextFileSpaceDelimiter = False
    .TextFileColumnDataTypes = Array(1)
    .TextFileTrailingMinusNumbers = True
    .Refresh BackgroundQuery = False
    Columns("K:L").Select
    Selection.Replace What:=",", Replacement:=".", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
End With
dTime = Now + TimeValue("00:00:10")
    Application.OnTime dTime, "Scanimport"


End Sub

I have found a lot of similar examples on these forums, but they don't use this convenient .Add(Connection:= part, and I don't understand how I can incorporate this in the various codes used to determine the most recent file.

 

[DrZegt]

So when trying this out on several different computers it runs into runtime error 75: File/path access error. I have seen others using FileCopy also seem to get this error quite frequently. Apparently a solution is to copy to a new folder by using something like this:

[COLOR=#333333]Dim fs As Object[/COLOR]
[COLOR=#333333]Dim oldPath As String, newPath As String[/COLOR]
[COLOR=#333333]oldPath = "C:\Documents and Settings\user\My Documents" 'Folder file is located in[/COLOR]
[COLOR=#333333]newPath = "C:\Documents and Settings\user\My Documents\Misc" 'Folder to copy file to[/COLOR]
[COLOR=#333333]Set fs = CreateObject("Scripting.FileSystemObject")[/COLOR]
[COLOR=#333333]fs.CopyFile oldPath & "\" & "Query1.xls", newPath & "\" & "Query1.xls" 'This file was an .xls file[/COLOR]
[COLOR=#333333]Set fs = Nothing[/COLOR]

I figured I could replace the first oldPath with myFolder and Query1.xls with fileName, which I figured should point to the latest saved file and then make a new folder to copy to and replace the second Query1.xls with StandardFileToImport.txt. Unfortunately it did not find the file, and I don't manage to fix it as I don't fully understand what this does in my original code: fileName = GetLatestFile(myFolder, "Scan")

GetLatestFile seems to be defined as: GetLatestFile = latestFile = fileName = Dir(folderName & "*" & MatchThis & "*")
latestFile I assume is defined as the most recently saved file. folderName I assume is myFolder, which is just the path to the folder where I save my scans. MatchThis I assume means that it looks for a file called "Scan" and then followed by *, i.e. whatever. When I try to implement the above part in my code and I add a watch on Scan, the value is just """". I'm not sure what it would say if it was working, but hopefully something along the lines of "Scan01.txt" or whatever the most recent file is. Anyone can point out what I'm doing wrong?

I don't even know whether this approach will work for me should I be able to actually do it correctly, but I figured I should at least give it a try, but if there are better solutions I'm all ears