Permission denied for particular user

jardenp

Active Member
Joined
May 12, 2009
Messages
367
I have a script that works fine on my machine (Win 7, Office 2010) but not on another machine (Win 10, Office 2010). I get a runtime error 70 permission denied when it tries to copy the file at the end of the script portion below. The other user can read/write/delete in all folders in use. From another post (here) I tried to switch
VBA Code:
Set oFSO = CreateObject("Scripting.FileSystemObject")
to
VBA Code:
Set oFSO = CreateObject("VBScript.RegExp")
but that threw an error on the next line:
VBA Code:
    Set oFolder = oFSO.GetFolder(FolderPath)

This is a script being used in a userform. "Entry" is an global integer variable defined in another sub.

My first thought is that it must be a reference issue between Win 7 and Win 10. Is there a reference I should check or uncheck in my VBE?

Thanks!

VBA Code:
Private Sub TripScanClick()
    
    Dim FolderPath As String, path As String, count As Integer
    FolderPath = "S:\XXX\Destination Folder"

    path = FolderPath & "\*"

    Filename = Dir(path)

    Do While Filename <> ""
       count = count + 1
        Filename = Dir()
    Loop

    If count = 0 Then
        MsgBox "No files were found in the target folder " & path
        Exit Sub
    ElseIf count > 1 Then
        MsgBox "More than one file was found in the target folder " & path & ". Please make sure the only file in the folder is the scan for this trip."
        Exit Sub
    End If
    
    'Rename File
    Dim oFSO As Object
    Dim oFolder As Object
    Dim oFile As Object
    Dim oFileName As String
    Dim NewUnitFileName As String
        NewUnitFileName = "\" & Me.Controls("TractorNum_TB" & Entry) & "- Odom " & Me.Controls("StartOdom_TB" & Entry) & "-" & Me.Controls("EndOdom_TB" & Entry) & " Trip Start Date " & Format(Me.Controls("TripStartDate_TB" & Entry), "yyyy-mm-dd") & ".pdf"
    Dim NewPayrollFileName As String
        NewPayrollFileName = "\" & Me.Controls("DivEntry_CB" & Entry) & "-" & Me.Controls("BillingGroup_CB" & Entry) & "-" & Left(Me.Controls("DriverName_CB" & Entry), 3) & "," & Mid(Me.Controls("DriverName_CB" & Entry), InStr(1, Me.Controls("DriverName_CB" & Entry), ",") + 2, 1) & "-" & Me.Controls("TractorNum_TB" & Entry) & "-" & Format(Me.Controls("TripStartDate_TB" & Entry), "yyyy-mm-dd") & ".pdf"
    Dim UnitFilesDir As String
        UnitFilesDir = "S:\XXX\Unit Trip Scans"
    Dim TripArchiveDir As String
        TripArchiveDir = "S:\XXX\Trip Sheet Archive"
    Dim TripWeeklyFolderDir As String
        TripWeeklyFolderDir = "S:\XXX\Trip Sheet Weekly Folders"
    Dim PayrollByDivisionDir As String
        PayrollByDivisionDir = "S:\XXX\Payroll by Division"
    
    Set oFSO = CreateObject("Scripting.FileSystemObject")

    Set oFolder = oFSO.GetFolder(FolderPath)
    
    'kill thumbs.db if it's there
    If Dir(FolderPath & "\thumbs.db") <> "" Then
        SetAttr FolderPath & "\thumbs.db", vbNormal
        Kill FolderPath & "\thumbs.db"
    End If
    
        

    For Each oFile In oFolder.Files

        oFileName = oFile.Name

    Next oFile
    
    'Copy file to Archive
    oFSO.CopyFile FolderPath & "\" & oFileName, _
        TripArchiveDir & NewPayrollFileName
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
57,023
Office Version
  1. 365
Platform
  1. Windows
It's a mystery why it works on the Win 7 machine and not the win 10. I'll investigate that too,
That could be a factor, or could just be coincidental, so try not to get too hung up on that.
Other things, like user permissions, could factor to (make sure that the other user can actually write to that drive!).
 

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.

jardenp

Active Member
Joined
May 12, 2009
Messages
367
I went through the possible reasons in the stack overflow post and it wasn't any of those. I solved it (at least for now) by taking the leading "\" off the second string variable in the CopyFile destination and joining the two variables with & "\" &. So NewPayrollFileName, which was "\string of stuff.pdf" became "string of stuff.pdf" and my destination went from "TripArchiveDir & NewPayrollFileName" to "TripArchiveDir & "\" & NewPayrollFileName" and it worked. I have no idea why...

Joe4, thank you for your help!
 
Solution

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
57,023
Office Version
  1. 365
Platform
  1. Windows
Glad you got it figured out.
 

Watch MrExcel Video

Forum statistics

Threads
1,130,205
Messages
5,640,837
Members
417,173
Latest member
Tuanphun

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