VBA Run time error '5'

12Rev79

New Member
Joined
Mar 2, 2021
Messages
40
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
Dear Experts,

I have the code below that would list all the files in selected directory.

It work so far the error happens when I click Cancel.

Can anyone is kind to help me fix the error?

1618233396688.png


VBA Code:
Private Sub OldName_Click()

Dim fldr As FileDialog
Dim sItem As String
Dim strPath As String
Set fldr = Application.FileDialog(msoFileDialogFolderPicker)
With fldr
    .Title = "Select a Folder"
    .AllowMultiSelect = False
    .InitialFileName = strPath
    If .Show <> -1 Then GoTo NextCode
    sItem = .SelectedItems(1)
End With
NextCode:
Set fldr = Nothing
Dim objFSO As Object
Dim objFolder As Object
Dim objFile As Object
Dim mysub As Object
Dim i As Integer


Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objFolder = objFSO.GetFolder(sItem)
i = 1
For Each objFile In objFolder.Files
Cells(i + 2, 2) = objFile.Name
Cells(i + 2, 4) = objFile.Path
i = i + 1
Next objFile

For Each mysub In objFolder.SubFolders
Cells(i + 2, 2) = mysub.Name
Cells(i + 2, 4) = mysub.Path
i = i + 1

Next mysub
End Sub

Thank you in advance.
12Rev79
 
Last edited by a moderator:

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.
Try changing this
VBA Code:
If .Show <> -1 Then GoTo NextCode
to
VBA Code:
If .Show <> -1 Then Exit Sub
 
Upvote 0
Solution
Hi,​
as a reminder to just list files from a folder better use the VBA Dir function rather than the external FSO, that's easier & faster !​
Another point : at 99% a good VBA procedure does not need any Goto statement …​
 
Upvote 0
Never ever ever never use GoTo. When you press Cancel, the code keeps going anyway and sItem is null when you reach the statement causing the error.

I see that @Fluff has provided one viable solution. I was going to rewrite the logic to avoid an Exit but that is the quickest fix.
 
Upvote 0
Hi,​
as a reminder to just list files from a folder better use the VBA Dir function rather than the external FSO, that's easier & faster !​
Another point : at 99% a good VBA procedure does not need any Goto statement …​
Thank you Marc L, I will take that kind information
 
Upvote 0
Never ever ever never use GoTo. When you press Cancel, the code keeps going anyway and sItem is null when you reach the statement causing the error.

I see that @Fluff has provided one viable solution. I was going to rewrite the logic to avoid an Exit but that is the quickest fix.
Thank you 6StringJazzer
 
Upvote 0
Glad we could help & thanks for the feedback.
 
Upvote 0

Forum statistics

Threads
1,213,489
Messages
6,113,954
Members
448,535
Latest member
alrossman

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
Back
Top