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

kansfan2001

New Member
Joined
Aug 22, 2020
Messages
21
Office Version
  1. 365
Platform
  1. Windows
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 :)



VBA Code:
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

New Member
Joined
Aug 22, 2020
Messages
21
Office Version
  1. 365
Platform
  1. Windows
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 :)

1628972054262.png
 

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.

Gokhan Aycan

Active Member
Joined
Aug 8, 2021
Messages
423
Office Version
  1. 365
Platform
  1. Windows
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).

VBA Code:
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
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,152,571
Messages
5,770,920
Members
425,652
Latest member
Pemby

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
Top