Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

fixing code getting names of subfolders and files from specific main folder

0

hello

i got  this  code  the  original works  well  but  i  try amending  by  add some message box to avoid the error    the message shows  but   it  doesn't  happen any thing  in col c  as was working  and  i  would   show  in col b  the  name's file for  each  subfolder  if is possible

Sub fldr()
   Dim Fldr As String
   Dim SubFldr As Object
   Dim i As Long

   i = 2
   With Application.FileDialog(4)
      .AllowMultiSelect = False
      If .Show = -1 Then Fldr = .SelectedItems(1)
   End With

   With CreateObject("scripting.filesystemobject")
   If Fldr = "" Then
    MsgBox "You didn't select a folder."
Else
    MsgBox "You selected this folder: " & Fldr

      For Each SubFldr In .GetFolder(Fldr).SubFolders




         ActiveSheet.Hyperlinks.Add Anchor:=Cells(i, 3), _
                Address:=Fldr & Application.PathSeparator & SubFldr.Name, _
                TextToDisplay:=SubFldr.Name
         i = i + 1
      Next

   End If
   End With
End Sub

i truly appreciate if anybody can update  the  code 

Answer
Discuss

Answers

0
Selected Answer

I didn't change any of your code. However, you didn't put the code lines in a logical order. That's why your code didn't work. Here is the corrected syntax.

Sub fldr()

    Dim fldr         As String
    Dim SubFldr      As Object
    Dim R            As Long

    R = 2
    With Application.FileDialog(4)
        .AllowMultiSelect = False
        If .Show = -1 Then fldr = .SelectedItems(1)
    End With

    If fldr = "" Then
        MsgBox "You didn't select a folder."
    Else
        MsgBox "You selected this folder: " & vbCr & fldr

        With CreateObject("Scripting.FileSystemObject")
            For Each SubFldr In .GetFolder(fldr).SubFolders
                ActiveSheet.Hyperlinks.Add Anchor:=Cells(R, 3), _
                       Address:=fldr & Application.PathSeparator & SubFldr.Name, _
                       TextToDisplay:=SubFldr.Name
                R = R + 1
            Next
        End With
    End If
End Sub

In fact, the first thing I did was to review your indenting. Indents establish correlations because items that belong together - like If ... End If, For ... Next, With ...End With - are on the same indent level. Once that is done you can see what's in between and spot the items that don't belong.

Discuss

Discussion

thanks variatus  for make clear  problem which cuses it   
now  works  very good 
best regards,
leopard
leopard (rep: 88) Sep 30, '20 at 7:42 am
Add to Discussion


Answer the Question

You must create an account to use the forum. Create an Account or Login