How to import data from multiple workbooks using VBA

 This post will explain how to import data from multiple workbooks using VBA The code will locate the spreadsheets, open them, identify the relevant information and copy it to a master spreadsheet

IN THIS POST we have already talked on how to loop through different folders and understand if they contain excel sheets. We will now move  to the next step by copying information from the excel sheets to our master file 

The below picture provides a snapshot of what we want to achieve 

The code assumes that all the "Customer1, Customer2,..." folders are placed under a main folder called CUSTOMERS.

The code will go through the following key steps:

1. Locate the main folder

2. Identify all the subfolders and loop through them 

3. Determine if the subfolder contains an excel file

4. Open the excel file and copy relevant info the master workbook

We will leverage on the content available at THIS POST to address the first 3 points and then provide full details on point 4 

The code starst by setting the variables that will be used

Cell C1 of the active sheet, contains the filepath to the main folder 

The FileSystem Object will allow to access the main folder.

The FileSystem Object allows to access all folders on the pc, and perform specific actions such at creating and writing TXT files. 

THIS POST explains how to create TXT files using the FileSystem object.It is important to know that the FileSystem object works only on Windows PCs.

The variables are set 

Dim fs, f, f1, s, sf

    Dim fpath As String

    Dim openWB As Workbook

The path to the main folder is reported in cell C1 of the activesheet

    fpath = Range("c1").Value

The FileSystem object is created enabling the code to access the folders on the PC

    Set fs = CreateObject("Scripting.FileSystemObject")

The GetFolder property allows to identify a specific folder. 

The path of the main folder is provided to the GetFolder method allowing to identify the main folder

    Set f = fs.GetFolder(fpath)

The GetFolder method of the FileSystem object returns the object represented by the main folder

Applying the .SubFolders method to the main folder returns a collection of all the SubFolders 

    Set sf = f.SubFolders

Next step is to loop through all the subfolders and loop through all the files placed in each subfolder

In order to do so a double loop is executed 

The variable i is set to 2. It will be used to determine the row where to copy the file path

 i = 2

The first loop starts looping through all the subfolders

    For Each sb In sf

A second loop goes through all the files  contained in each subfolder. It uses the .Files property applied to the subfolders in order to return a collection of files 

        For Each f In sb.Files

Each file contained in the subfolder is analysed to understand if its file path contains the string ".xl"

The ".xl" has been selected as the identifier of an excel file because when naming a file, the dot can be used only at the start of the file extension, and the "xl" string is common to all excel files.

Additionally the wild character * has been used before and after the ".xl" string. This will allow to identify any excel file regardless of the length of its name and the type of extension (xlsx, xlsm, xlsb, xls,...)

If the file contains the ".xl" string then it is an excel file 

If f.Path Like "*.xl*" Then

If an excel file is idetified the code assings the excel file to the openWB variable and opens it 

 If f.Path Like "*.xl*" Then

        Set openWB = Workbooks.Open(f.Path)

It is assumed that the information we are searching for is always contained in a sheet called Report of the spreadsheets and it is therefore imported on the sheet called Test of the main workbook.

Note that the main workbook is referred to using the object ThisWorkbook which always identifies the workbook from which the code is run. 

The spreadsheet that has been identified and opened is the ActiveWorkbook as it has been just opened and therefore it is the active one 

ThisWorkbook.Sheets("Test").cells(i,1).Value = ActiveWorkbook.Sheets("Report").Range("g3").Value

Once the data has been copied the openWB is closed

  openWB.Close

The variable i is increased by 1 to ensure that the data form the next sheets will be copied on the following row

        i = i + 1

        Else: End If

The second loop moves on to the next item and when it reaches the bottom then the first loop moves forward to the next item

Next 

Next 

End Sub

The full code is reported below for convenience

Sub searchfileinfolder()

    Dim fs, f, f1, s, sf

    Dim fpath As String

    Dim openWB As Workbook

    fpath = Range("c1").Value

    Set fs = CreateObject("Scripting.FileSystemObject")

    Set f = fs.GetFolder(fpath)

    Set sf = f.SubFolders

    i = 2

    For Each sb In sf

        For Each f In sb.Files

        If f.Path Like "*.xl*" Then

        Set openWB = Workbooks.Open(f.Path)

ThisWorkbook.Sheets("Test").cells(i,1).Value = ActiveWorkbook.Sheets("Report").Range("g3").Value

  openWB.Close

        i = i + 1

        Else: End If

       Next

    Next

End Sub