Run command on each Excel file in a specific folder

Hi there! I'm pretty new to VBA, and I'm trying to automate our time tracking process for one of our departments as much as I can. What I'd like to do is run a command from our main payroll prep Excel file (File A, for simplicity) that will open the first person's time sheet in a given folder (File B1), and copy the necessary values into a table from a separate file (File C) that will later be uploaded into our payroll system. Then File B1 will close, and File B2 will open for the next employee's hours to be copied into File C.

Can anyone help with just the first few steps on how to get the loop started to open one file at a time and copy the values, taking into consideration that all the files will have unique names for each employee and each pay period (i.e. the file names in coding can't be static because they change each pay period).

Thanks, everyone!

Hi and welcome

The below should get you started with looping through each Excel file in a specified folder.
Using Dir it will not matter what the file names are as long as they have an Excel file extension of one type or another.

PHP Code: 

Private Sub ImportDataFromAllWorkbooksInAFolder()
    Dim FolderName As String
    Dim FileName As String

    FolderName = "C:\Folder\Sub Folder" 'AMEND THIS TO YOUR RELEVANT FOLDER PATH
    FileName = Dir(FolderName & "*.xl??")
    
    Do While FileName <> ""
        Workbooks.Open (FolderName & FileName)
        'CODE HERE FOR COPYING DETAILS FROM EACH WORKBOOK INTO YOUR MASTER WORKBOOK
        Workbooks(FileName).Close
        FileName = Dir()
    Loop
End Sub 


Impossible to give more specific code on how to copy details from the individual workbooks into your master one without some more detail on exactly what needs copying. Perhaps a sample file (desensitized) showing what needs copying from and where it needs copying too will help with that.

Let us know how you get on.

BSB

Thank you, BSB! After thinking this through (the hard way, of course), I'm thinking it would make the most sense to just copy the files (all with just one tab each) over to a new workbook. Is that something I can find a simple code to run?

Thank you again!