'Excel VBA Error Handling with Permission Denied when accessing a folder / file
I'm writing a script that looks at a certain file directory then makes a note of all folders in that location as well as the date it was last edited. It all works as expected however if it comes across a file that I don't have permissions for it errors (Run-time error '70': Permission denied).
I am no good at error handling and after trying a bunch of things I could think of and doing some research I am still stumped, what I would like is if there is a file it cannot access due to permissions it either just skips it or in a perfect world colours it red or something in the output then moves onto the next on the list.
Sub ListFoldersInDirectory()
'Application.ScreenUpdating = False
Dim objFSO, objFolders, objFolder As Object
Dim strDirectory, arrFolders(), test, a, LocationPath As String
Dim FolderCount, FolderIndex, getdirorfilesize, oFO As Long
Dim b, c As Integer
On Error Resume Next
If Err.number <> 0 Then
If Err.number = 70 Then
MsgBox "Permission Denied"
'Else
'MsgBox "An error occurred..."
End If
Err.Clear
End If
On Error GoTo 0
Sheets(2).Activate
'First time run to paste the first data in the correct place
Do While Sheets(2).Range("N1").Value = 0
Sheets(2).Range("N1").Value = 1
'Opens file directory and lets you select inital folder
With Application.FileDialog(msoFileDialogFolderPicker)
.InitialFileName = Application.DefaultFilePath & "\"
.Title = "Select Folder"
.Show
If .SelectedItems.Count = 0 Then
Exit Sub
End If
strDirectory = .SelectedItems(1)
End With
Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objFolders = objFSO.GetFolder(strDirectory).Subfolders
FolderCount = objFolders.Count
'Checks what folders are in the specified folder then pastes them onto asheet
If FolderCount > 0 Then
ReDim arrFolders(1 To FolderCount)
FolderIndex = 0
For Each objFolder In objFolders
FolderIndex = FolderIndex + 1
arrFolders(FolderIndex) = objFolder.Name
Next objFolder
Sheets(1).Range("A1").Resize(FolderCount).Value = Application.Transpose(arrFolders)
'Changes pasted data into a file address
Sheets(1).Activate
Sheets(1).Range("A1").Select
Selection.End(xlDown).Select
a = ActiveCell.Address
Sheets(1).Range("A1:" & a).Select
For Each x In Selection
x.Activate
ActiveCell.FormulaR1C1 = strDirectory & "\" & ActiveCell.Formula
Next x
'Moves Data to the main sheet
Sheets(1).Activate
Sheets(1).Range("A1").Select
Selection.End(xlDown).Select
b = ActiveCell.Row
Range("A1:A" & b).Select
Selection.Cut
Sheets(2).Select
Range("a1").Select
'Selection.End(xlDown).Select
ActiveCell.Offset(1, 0).Select
ActiveSheet.Paste
'Retrieves time and date when file was last accessed
Sheets(2).Activate
Sheets(2).Range("A1").Select
Selection.End(xlDown).Select
a = ActiveCell.Address
Sheets(2).Range("A2:" & a).Select
For Each x In Selection
x.Activate
LocationPath = ActiveCell.Value
ActiveCell.Offset(0, 1).FormulaR1C1 = _
FileDateTime(LocationPath)
Next x
Else
MsgBox "No folders found!", vbExclamation
End If
Loop
Set objFSO = Nothing
Set objFolders = Nothing
Set objFolder = Nothing
'Application.ScreenUpdating = True
'GetSubFolders
End Sub
As you can see at the top is an attempt at making something happen when it errors though with no luck, if it helps the portion that is highlighted on the debugger is 'FolderCount = objFolders.Count'
Apologies for the messy code, once it's all working I was planning on cleaning things up!
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow
| Solution | Source |
|---|
