Instead of providing the file path in my code, I prompt the user to select the folder that contains data files in xlsx format, over which is looped via: For Each wbFile In fldr.Files.

I am getting error 424.

See the commented out lines in the code:

Sub getDataFromWbs()

Dim wb As Workbook, ws As Worksheet
Set fso = CreateObject("Scripting.FileSystemObject")
'Set fldr = fso.GetFolder("\\name_of_folder_to_get_files_from\")  'old code

'Dim FolderName As String      'fldr was previously, original code: FolderName
With Application.FileDialog(msoFileDialogFolderPicker)
    .AllowMultiSelect = False
    .Show
    On Error Resume Next
    fldr = .SelectedItems(1)     'fldr was previously, original code: FolderName
    Err.Clear
    On Error GoTo 0
End With

i = "1"                        'set integer for loop over sheets
For Each wbFile In fldr.Files  'loop over data in prompted folder

1 Answers

0
Yane On Best Solutions

Here's a quick fix of your code:

Sub getDataFromWbs()

Dim wb As Workbook, ws As Worksheet
Set fso = CreateObject("Scripting.FileSystemObject")
'Set fldr = fso.GetFolder("\\name_of_folder_to_get_files_from\")  'old code

'Dim FolderName As String      'fldr was previously, original code: FolderName
With Application.FileDialog(msoFileDialogFolderPicker)
  .AllowMultiSelect = False
  .Show
  On Error Resume Next
  FolderName = .SelectedItems(1)     'fldr was previously, original code: FolderName
  Err.Clear
  On Error GoTo 0
End With

i = "1"                        'set integer for loop over sheets
Set fldr = fso.GetFolder(FolderName)
For Each wbFile In fldr.Files  'loop over data in prompted folder