Import all files from folder instead of one at a time. Loop maybe?

[kansfan2001]

I have built a code to import specific areas from one sheet and placing that data into another. But for the life of me I can not figure out how to get it to repeat for all the excel files in the folder. I have to manually add each folder. I was wondering how to make the VBA below function for all file in the folder before it stops?

Thanks for the help its greatly appreciated

Sub ImportFromPickSheet()
'
' ImportFromPickSheet Macro
'

'
' Defines variables Workbook 1 is for the MNF Calculator - Workbook 2 is for the Player Pick Sheet
Dim wb1 As Workbook, wb2 As Workbook

' Disable screen updating to reduce screen flicker
Application.ScreenUpdating = False

' Define which workbook is which (Change "C:\" to pick import sheet location)
Set wb1 = ThisWorkbook
 ChDir "C:\"
  FName = Application.GetOpenFilename
  If FName <> False Then
    Set wb2 = Workbooks.Open(FName)
  End If

' Copy range B6:AM5 from ImportToCalc (Sheet2) of Workbook 2 and Import into ImportFromPickSheet (Sheet3) of Workbook 1
With wb2.Sheets("ImportToCalc").Range("B5:AM5")
wb1.Sheets("ImportFromPickSheet").Range("B" & Rows.Count).End(xlUp)(2).Resize(.Rows.Count, .Columns.Count).Value2 = .Value2
End With


' Close wb2
wb2.Close


' Re-enable screen updating
Application.ScreenUpdating = True


End Sub

 

[kansfan2001]

Here's the list I come up with. My issue is what happens when i have more files that need to be added at a later date? How can i get the query to account for those yet to be added?

I am super new to Query's so I am sorry if I am not presenting the information correctly

 

[Gokhan Aycan]

You can use FileSystemObject for this. Change strFolder to your folder's address. And put an if check to catch Excel files Type. Might need "Microsoft Scripting Runtime" referenced to get Intellisense for FileSystemObject (can't exactly remember).

Public Sub test_folder_list()

    Dim fso As FileSystemObject
    Dim oFolder As Folder
    Dim oFiles As Files
    Dim oFile As File
   
    Dim strFolder as String

    strFolder = "D:\LazyD Charter\Apps"

    Set fso = New FileSystemObject
    Set oFolder = fso.GetFolder(strFolder)
    Set oFiles = oFolder.Files
   
    For Each oFile In oFiles
        Debug.Print oFile.Name, oFile.Type
    Next oFile
    
    Set oFiles = Nothing   
    Set oFolder = Nothing
    Set fso = Nothing

End Sub