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
 

Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
57,023
Office Version
  1. 365
Platform
  1. Windows
but that threw an error on the next line:
VBA Code:
Set oFolder = oFSO.GetFolder(FolderPath)
What is the exact error message?
Did you confirm that the user has permission to that folder and it is already mapped on his computer, exactly as the code shows (S:\XXX\Destination Folder)?
 

jardenp

Active Member
Joined
May 12, 2009
Messages
367
Joe - The error is "runtime error 70: permission denied" for the code as is when it tries to run oFSO.CopyFile. The error for the code you quoted (after I tested switching "Scripting.FileSystemObject" to "VBScript.RegExp" was runtime error 438: object doesn't support this property or method." However I switched it back to "scripting.filesystemobject."

I probably should have added what I'm trying to do: This script takes a PDF from a folder and copies it to other folders with new names based on data in the userform.

Also, it now seems to be broken in the same way on my machine (Win 7, Office 2010) as the newer one (Win 10, 2010). Hmmmm....

Thanks for the help!
 

jardenp

Active Member
Joined
May 12, 2009
Messages
367
Joe,

I forgot to add: yes, drive is mapped and user can read/write/delete in all folders.

Thanks,
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
57,023
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Did you compare the selected references on each machine?

Which references are selected on the machine where it does work?
Which references are selected on the machine where it does not work?
 

jardenp

Active Member
Joined
May 12, 2009
Messages
367
Same on new and old. These references in this order:
Visual Basic for Applications
Microsoft Excel 14.0 Object Library
OLE Automation
Office 14.0 Object Library
Microsoft Forms 2.0 Object Library
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
57,023
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

I believe you should have a Microsoft Scripting Runtime reference selected also, since you are trying to use that here:
VBA Code:
    Set oFSO = CreateObject("Scripting.FileSystemObject")
 

jardenp

Active Member
Joined
May 12, 2009
Messages
367
Joe,

I added that and still get runtime error 70: permission denied on the oFSO.CopyFile line.
 

jardenp

Active Member
Joined
May 12, 2009
Messages
367
Thanks for the link. I will work through those. It's a mystery why it works on the Win 7 machine and not the win 10. I'll investigate that too,
 

Watch MrExcel Video

Forum statistics

Threads
1,130,205
Messages
5,640,836
Members
417,172
Latest member
Matias_Luna

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