Run-time error '75' when using working vba-script on different computer

[DrZegt]

I have a code that changes the name of the most recently saved file in the folder to StandardFileToImport.txt, and then imports the contents it into excel every 10 seconds. This works fine on the computer where I originally made the code, but when I try it on several other different computers (after changing the folder location), I keep getting Run-time error '75': Path/File access error.

Some probably irrelevant info: I am an admin-user on both the computer where it works and one of the computers where it doesn't work. The computer where the script works is on a network, but the computers where it doesn't work are not, the file location has been set as trusted in the trust center, the files in the folder are not read-only. I have tried using a folder on an USB-stick instead of C:\, but doesn't change anything. I have tested on excel 2007, 2010 and 2016.

When I look up similar problems on these forums, it appears that almost everyone has an error in their code somewhere. As this code is working fine on one computer, I'm not really sure whether this is the case here, but it might be possible to do some changes to it so that it works on other computers regardless. I am very inexperienced with VBA, so might be I'm just doing a stupid mistake when changing computers. The only modification I do to the script when I change computers is to change the myFolder locations at the 2 positions they appear in the script. Everything else I leave exactly as is. In the specified folder on the computer there is a "Scan0.txt" file (which is supposed to get imported when the script works).

Here is the code I use:

Option Explicit


Sub RunOnceToCreateStandardQuery()
    Dim fileName As String
    Dim myFolder As String
    
    myFolder = "C:\Users\Yvonne\Desktop\Temp measurements\Test\"
    fileName = myFolder & "StandardFileToImport.txt"
            
    FileCopy myFolder & GetLatestFile(myFolder, "Scan"), myFolder & "StandardFileToImport.txt"
    
    With ActiveSheet.QueryTables.Add(Connection:="TEXT;" & fileName, _
        Destination:=Range("$K$2"))
        .Name = "StandardScanImport"
        .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
          
    End With
End Sub


Function GetLatestFile(folderName As String, MatchThis As String) As String
    Dim fname As String
    Dim latestFile As String
    fname = Dir(folderName & "*" & MatchThis & "*")
    latestFile = fname
    Do While fname <> ""
        If FileDateTime(folderName & fname) > FileDateTime(folderName & latestFile) Then
            latestFile = fname
        End If
        fname = Dir
    Loop
    GetLatestFile = latestFile
    'MsgBox latestFile & vbCrLf & FileDateTime(folderName & latestFile)
End Function


Sub ScanImport()
    Dim dTime As Date
    Dim myFolder As String
    Dim fileName As String
    
    myFolder = "C:\Users\Yvonne\Desktop\Temp measurements\Test\"
   
    Columns("K:L").ClearContents
    fileName = GetLatestFile(myFolder, "Scan")
    FileCopy myFolder & fileName, myFolder & "StandardFileToImport.txt"
    ActiveWorkbook.RefreshAll  ' refreshes all queries (should only be one)
    dTime = Now + TimeValue("00:00:10")
    If Range("A1") = "" Then   ' so you can stop the repeat if there is anything in A1
        Application.OnTime dTime, "ScanImport"
    End If
    Range("b1") = "File imported: " & fileName
End Sub

When I run the code and click on debugg after I get the error, it highlights the line:

FileCopy myFolder & GetLatestFile(myFolder, "Scan"), myFolder & "StandardFileToImport.txt"

I'm running out of things to try, so any help would be greatly appreciated

 

[Dryver14]

as you step over the code using the F8 key

I am not sure what this does FileCopy myFolder & GetLatestFile(myFolder, "Scan"), myFolder & "StandardFileToImport.txt", it doesn't mean it's a bad thing but someone else may know

in the immediate window you could put ? myFolder ? fileName

Essentially as it is giving the path not found we need to make sure the path is correct

 

[DrZegt]

What do you mean with immediate window?

If I use F8 nothing happens when I step ovet debug.print Environ ("UserProfile")

 

[DrZegt]

I assume that FileCopy part copies the most recent file in the specified folder to the same folder and changes the name to StandardFileToImport.txt?

 

[Dryver14]

That's in the function.

STOP.

Whatever I am doing here it certainly isn't helping. go back to your original script and I am sure one of the more experienced operators may step in to help

 

[DrZegt]

Anyone else have any ideas?

 

[Dryver14]

I hate leaving you like this.

on the pc where this code is not working get the person to go to the folder on their pc and select it without opening it then in the filepath at the top click on it and it will show the file path for them,
see if it matches the myfolderpath you have listed. If not then get them either to copy and paste that in place of what you have as folder path.

I have noticed you have this in 2 different sub routines so it will need changing in both.

if it is just the beginning of the path that is different ie: the C:Users\kevin.wilson element then my earlier point is valid and will make the code more robust.

If it is just one user then typing or copying the correct path should resolve your issue. Remember to change it in both Sub Routines

 

[DrZegt]

The new path on the new PC is obtained by doing just that, and then copied into the two places where it is required. Apparently this error is quite common with FileCopy command. If the file is somehow open it cant be copied and renamed. I just dont see why it works on my computer on not on the others. Someone has suggested to copy to a new folder and not the same one, but as I don't really understand the code I struggle with actually changing it. Not sure why this would work though.