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

DrZegt

New Member
Joined
Mar 7, 2018
Messages
16
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:

Code:
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:
Code:
FileCopy myFolder & GetLatestFile(myFolder, "Scan"), myFolder & "StandardFileToImport.txt"

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

Some videos you may like

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.

Dryver14

Well-known Member
Joined
Mar 22, 2010
Messages
2,396
try changing

myFolder = "C:\Users\Yvonne\Desktop\Temp measurements\Test"

to

myfolder = Environ("UserProfile") & "\Desktop\Temp measurements\Test"
 

DrZegt

New Member
Joined
Mar 7, 2018
Messages
16
Now instead I get run-time error 76. Path not found. The debuger highlights:
Filecopy myFolder & fileName, myFolder & "StandardFileToImport.txt"
 

Dryver14

Well-known Member
Joined
Mar 22, 2010
Messages
2,396
try myFolder & "\StandardFileToImport.txt"

if people are not sharing the same desktop then you may need to use Yvonne, I just did not think this path would be right for other users
 

DrZegt

New Member
Joined
Mar 7, 2018
Messages
16

ADVERTISEMENT

I don't think it understands "UserProfile", as I misspelled it without it mattering.
 

Dryver14

Well-known Member
Joined
Mar 22, 2010
Messages
2,396

ADVERTISEMENT

Environ("UserProfile") = C:\Users\KevinWilson in my instance

try debug.print Environ ("Userprofile")


 
Last edited:

Dryver14

Well-known Member
Joined
Mar 22, 2010
Messages
2,396
It may be worth checking a few things in the immediate window, to see the file path before you run that line of code.
 

DrZegt

New Member
Joined
Mar 7, 2018
Messages
16
Where in the script do I put it? Tried several places, but still just get the error message when I run it
 

DrZegt

New Member
Joined
Mar 7, 2018
Messages
16
Now I got a error 52: bad file name or number.

It highlights fname= Dir (folderName & MatchThis & "*")
 

Watch MrExcel Video

Forum statistics

Threads
1,127,918
Messages
5,627,620
Members
416,257
Latest member
salomon

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