none
Creating a script to copy the files from a list into a specific folder causes run-time 70 permission denied error. RRS feed

  • Question

  • I've confirmed with my IT department that I have all of the proper privileges and am an Admin. Files on the file list are in this format:

    h:\Folder\sub folder\sub sub folder\file name.xls
    h:\Folder 1\sub folder\sub sub folder\that file name.xls
    h:\Folder 2\sub folder\sub sub folder\this file name.xls

    Sub Copy_Certain_Files_In_Folder()    Dim FSO As Object
        Dim sourceFile As String
        Dim ToPath As String
        Dim FileExt As String
        Dim X As Integer
     X = 1
    
        ToPath = "H:\redo"
       sourceFile = Cells(X, 1).Value
    
    
    
    18 Do While sourceFile <> ""
    
    sourceFile = Cells(X, 1).Value
        Set FSO = CreateObject("scripting.filesystemobject")
    
    
    
    
    
        If FSO.FolderExists(ToPath) = False Then
            MsgBox ToPath & " doesn't exist"
            Exit Sub
    End If
    
    FSO.CopyFile sourceFile, Destination:=ToPath
    X = X + 1
    Loop

    End Sub

    Tuesday, January 16, 2018 9:17 PM

All replies

  • b,
    re: error 70

    The are some folders that are forbidden, best word I know to describe them.
    On my system it is the System Volume Folder.
    You will have to use On Error Resume Next or my preferred method of checking the folder name and then skipping problem folders...
        If VBA.Inst(1, objFolder.name, "System Volume", vbBinaryCompare) < 1 Then  (its safe)
    '---
    Jim Cone
    Portland, Oregon USA
    https://goo.gl/IUQUN2 (Dropbox)
    (free & commercial excel add-ins & workbooks)


    • Edited by James Cone Wednesday, January 17, 2018 5:29 AM
    Wednesday, January 17, 2018 5:26 AM
  • Hello brokenshoes,

    Please add a "\" after target path string and you need update the loop code like below.

    Sub Copy_Certain_Files_In_Folder()
        Dim FSO As Object
        Dim sourceFile As String
        Dim ToPath As String
        Dim FileExt As String
        Dim X As Integer
        X = 1
        ToPath = "C:\Users\Admin\Desktop\TestFolder"
        sourceFile = Cells(X, 1).Value
        Set FSO = CreateObject("scripting.filesystemobject")
        Do While sourceFile <> ""
        If FSO.FolderExists(ToPath) = False Then
            MsgBox ToPath & " doesn't exist"
            Exit Sub
        End If
        FSO.CopyFile sourceFile, Destination:=ToPath & "\"
        X = X + 1
        sourceFile = Cells(X, 1).Value
    Loop
    End Sub

    Best Regards,

    Terry


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Wednesday, January 17, 2018 5:43 AM
  • Hi,

    I am wondering why your code will not work and you see "70 permission denied error", because you are using CopyFile of FileSystemObject.

    But I suppose your code leaves some space for improvement, so I tried to modify your code.
      

      

    [code in "Copy Files" button]
    Private Sub btn_CopyFiles_Click()
        Dim FSO As Object
        Dim sourceFile As String
        Dim targetPath As String
        ' ---
        Dim myRow As Integer
        Dim lastRow As Integer
        ' ---
        Set FSO = CreateObject("scripting.filesystemobject")
        Range("B1:B100").Value = ""
        ' ---
        targetPath = "H:\xx_redo"
        lastRow = Cells(Rows.Count, 1).End(xlUp).Row
        ' ---
        For myRow = 1 To lastRow
            ' ---
            If (FSO.FolderExists(targetPath) = False) Then
                MsgBox targetPath & " doesn't exist"
                Exit Sub
            End If
            ' ---
            sourceFile = Trim(Cells(myRow, 1).Value)
            If (Dir(sourceFile) = "") Then
                MsgBox "[input]" & Chr(13) & sourceFile & Chr(13) & "doesn't exist"
                Cells(myRow, 2).Value = "not exist"
            Else
                Dim fileName As String: fileName = Dir(sourceFile)
                Dim targetfile As String: targetfile = targetPath & "\" & fileName
                FSO.CopyFile sourceFile, Destination:=targetfile
            End If
        Next
        ' ---
        MsgBox "operation completed """
    End Sub

    Regards,

    Ashidacchi

    Wednesday, January 17, 2018 6:29 AM