VBA - Loop through folder with blank spaces address

Question

I have a function that asks the user to select a folder and then another one that should output all the file names in that folder. I have been trying the following but it does not work because the folder address contains blank spaces. Could you please help?

'gets folder address
recsFolder = Functions.GetFolder("C:\")

'Loop through files in folder
Dim StrFile As String
StrFile = Dir(recsFolder)
Do While Len(StrFile) > 0
    Debug.Print StrFile
    StrFile = Dir
Loop

Thanks!

Edit: Code for GetFolder

Function GetFolder(strPath As String) As String
    Dim fldr As FileDialog
    Dim sItem 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:
    GetFolder = sItem
    Set fldr = Nothing
End Function

By blank spaces I mean spaces that are present in the address (i.e. between Daily and Summary) C:\Users\User1\Desktop\Daily Summary

What's the code for the `GetFolder` function? and what are "blank spaces" ? — SierraOscar, Mar 31 '16 at 14:50

score 1 · Answer 1 · answered Mar 31 '16 at 14:58

1

You need to provide Dir() with a file pattern in order to list files.

Change to:

StrFile = Dir$(recsFolder & "\*.*")

answered Mar 31 '16 at 14:58

Alex K.

171,639
30
264
288

cheers, it did work as well. What is the purpose of the $ after the Dir? – peetman Mar 31 '16 at 15:01
See [vba: what does $ do?](http://stackoverflow.com/questions/3389444/vba-what-does-do) – Alex K. Mar 31 '16 at 15:04
@peetman as a follow-on, the accepted answer on that link is useful - but isn't actually the reason why the `$` is used. A couple of answers down explains why: http://stackoverflow.com/a/3389455/4240221 – SierraOscar Mar 31 '16 at 15:08
@MacroMan thank you for the explanations. it returns a string. – peetman Mar 31 '16 at 15:11
Guys, I am having another issue. While the loop is running at some point the Dir gets the value . How can I avoid this? – peetman Apr 01 '16 at 10:30
Probably best to ask a new question with the offending code & details. – Alex K. Apr 01 '16 at 10:34

score 0 · Accepted Answer · answered Mar 31 '16 at 14:59

0

Change

StrFile = Dir(recsFolder)

To

StrFile = Dir(recsFolder & "\*.*")

answered Mar 31 '16 at 14:59

SierraOscar

17,507
6
40
68

VBA - Loop through folder with blank spaces address

2 Answers2