On the next line we'll open the workbook.Using the Source and strFile variables we set earlier.

If fileExt = 'xlsx' Or fileExt = 'xls' Or fileExt = 'xlsm' _Īnd strFile 'consolidate_reports.xlsm' Then (Note the underscore allows you to continue your code on a separate line, ensure you type a space before the 'line continuation' underscore.)

Now we need to use an IF statement so that the only files opened are Excel files.

Within the Do loop place set the variable fileExt to the value of the file extension of the currently looped file.įileExt = Right(strFile, Len(strFile) - InStr(strFile, '.')).

Basically, I'll need to loop through all of the files in the selected folder opening only the Excel files. So now it's time to build our looping statement.That value is then placed into the strFile variable. (Remember the Source variable will hold the folder selected by the user). Next, we'll use the Dir() method to get all of the files in the selected folder.The result of the function is placed into the Source variable we created earlier. On the next line, we'll add the line that will call the GetFolder() function that we've yet to create.Now to add the code to remove any unwanted pop ups that come with copying and pasting code.Next we need to Dimension or declared the variables that are going to hold the folder name, file name and file extension.Type the following bold text:.We will type the VBA code needed within the Sub/End Sub.Īll new code will be emboldened, so you know which bit of code you need to add.).Now it's time to start the Sub Procedure.Don't know what I'm talking about? Then read further). (This line makes it easier to track your variables should things go wrong. Click at the top of the new blank module and type: Option Explicit.Modules are also created when you use the Macro record button.) (Modules are where you will place the VBA code for this lesson. In the Project Explorer right click on VBAProject(consolidate_reports.xlsm) and choose Insert - Module.Ensure that you can see the Project Explorer window on the left.On the Developer Tab click the Visual Basic Icon on the left to open up the Visual Basic Editor.(File - Options - Customise Ribbon and check the Developer Tab). (Remember to save it as a macro enabled workbook. Open a New Excel file and save the file as as consolidate_reports.xlsm.The contain 4 sample data files, Which are the ones you should use for this exericse.

Before you start, you will want to download the exercise files. The openMyFiles() function was designed to loop through the various Excel files in the chosen folder, the other was to simply get the folder that was chosen. Well actually one function and one sub-procedure. Two functions, I used, to solve this problem. Writing the VBA Consolidation Code The openMyFiles() function So we worked on this problem during lunchtime and although this might not be the most efficienty way of accomplishing this, especially with thousands of documents, it certainly beats having to do it manually. The script would then select a range in file and copy and paste it back to the main file.xls file would be for Excel workbooks that were created in older versions). Then, after having selected the folder, VBA would loop through and select and open only the.They would then select a folder in which there would be excel and a number of other files.The user would click on a form button which would open a browse dialog box.When I quizzed them about their requirements for their VBA procedure it involved the following: Their question was: 'How can we, using VBA, consolidate data from multiple spreadsheets?'. (Which incidently was SMC Pneumatics based in Milton Keynes). I conducted the final day of a 3 day VBA training course and on this day we I took the opportunity to answer a particluar problem of the client. This is a step-by-step guide on how I achieved this.

Use VBA to consolidate data from multiple sheets into one spreadsheet. Using VBA to Consolidate Data from Multiple Sheets Why do you need to consolidate data? Background

Consolidate data in excel