Run-time error '75' when using working vba-script on different computer
Attend Excelapalooza
Thanks Thanks:  0
Likes Likes:  0
Page 1 of 2 12 LastLast
Results 1 to 10 of 17

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

  1. #1
    New Member
    Join Date
    Mar 2018
    Posts
    16
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

     
    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

  2. #2
    Board Regular
    Join Date
    Mar 2010
    Location
    Bradford, West Yorkshire, UK
    Posts
    2,391
    Post Thanks / Like
    Mentioned
    12 Post(s)
    Tagged
    1 Thread(s)

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

    try changing

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

    to

    myfolder = Environ("UserProfile") & "\Desktop\Temp measurements\Test"
    I visit this site mainly to remember how little I know

  3. #3
    New Member
    Join Date
    Mar 2018
    Posts
    16
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    Now instead I get run-time error 76. Path not found. The debuger highlights:
    Filecopy myFolder & fileName, myFolder & "StandardFileToImport.txt"

  4. #4
    Board Regular
    Join Date
    Mar 2010
    Location
    Bradford, West Yorkshire, UK
    Posts
    2,391
    Post Thanks / Like
    Mentioned
    12 Post(s)
    Tagged
    1 Thread(s)

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

    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
    I visit this site mainly to remember how little I know

  5. #5
    New Member
    Join Date
    Mar 2018
    Posts
    16
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

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

  6. #6
    New Member
    Join Date
    Mar 2018
    Posts
    16
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    Adding \ did not do anything

  7. #7
    Board Regular
    Join Date
    Mar 2010
    Location
    Bradford, West Yorkshire, UK
    Posts
    2,391
    Post Thanks / Like
    Mentioned
    12 Post(s)
    Tagged
    1 Thread(s)

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

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

    try debug.print Environ ("Userprofile")


    Last edited by Dryver14; Mar 16th, 2018 at 12:01 PM.
    I visit this site mainly to remember how little I know

  8. #8
    Board Regular
    Join Date
    Mar 2010
    Location
    Bradford, West Yorkshire, UK
    Posts
    2,391
    Post Thanks / Like
    Mentioned
    12 Post(s)
    Tagged
    1 Thread(s)

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

    It may be worth checking a few things in the immediate window, to see the file path before you run that line of code.
    I visit this site mainly to remember how little I know

  9. #9
    New Member
    Join Date
    Mar 2018
    Posts
    16
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    Where in the script do I put it? Tried several places, but still just get the error message when I run it

  10. #10
    New Member
    Join Date
    Mar 2018
    Posts
    16
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

      
    Now I got a error 52: bad file name or number.

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

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

 

 
DMCA.com