Excel vba: combine multiple files in one sheet

Question

I have 100+ files in one folder. Each file has 3 lists, but only 1 list with data. I need to take that data from each file and combine it in a single file on 1 list. I wrote a sub for it, but I'm not sure how to go around selecting only the range needed (it varies from file to file) - in the same way you do it on keyboard with Ctrl + Shift + left arrow + down arrow. And how should I go around pasting it in the result workbook at exactly the first free line after the data that was pasted before?

Sub combine()
        Application.ScreenUpdating = False
        Application.DisplayAlerts = False
        Application.Calculation = xlCalculationManual

        Dim ExcelApp As Object
        Set ExcelApp = CreateObject("Excel.Application")
        ExcelApp.Visible = False
        ExcelApp.ScreenUpdating = False
        ExcelApp.DisplayAlerts = False
        ExcelApp.EnableEvents = False

        '**VARIABLES**
        Dim folderPath As String
        folderPath = "Y:\plan_graphs\final\mich_alco_test\files\"

        'COUNT THE FILES
        Dim totalFiles As Long
        totalFiles = 0
        Dim fileTitle As String
        fileTitle = Dir(folderPath & "*.xl??")
        Do While fileTitle <> ""
            totalFiles = totalFiles + 1
            fileTitle = Dir()
        Loop

        'OPENING FILES
        Dim resultWorkbook As Workbook
        Dim dataWorkbook As Workbook
        Set resultWorkbook = ExcelApp.Application.Workbooks.Open("Y:\plan_graphs\final\mich_alco_test\result.xlsx")


        fileTitle = Dir(folderPath & "*.xl??")

        'FOR EACH FILE
        Do While fileTitle <> ""
            Set dataWorkbook = ExcelApp.Application.Workbooks.Open(folderPath & fileTitle)
            dataWorkbook.Worksheets("List1").Range("A1").Select
            dataWorkbook.Worksheets("List1").Selection.CurrentRegion.Select


             `resultWorkbook.Range
             fileTitle = Dir()
         Loop

    ExcelApp.Quit
    Set ExcelApp = Nothing
End Sub

Why do you create a new application instance inside your current application instance? — Robin Mackenzie
– Robin Mackenzie, Commented Jul 26, 2017 at 8:04
When you talk about a list - do you mean different worksheets, or columns within a single worksheet, or ListObjects ? — Robin Mackenzie
– Robin Mackenzie, Commented Jul 26, 2017 at 8:13
@Robin Mackenzie I mean Worksheets by Lists. I have 100+ Workbooks and each has one Worksheet with data. — Ans
– Ans, Commented Jul 26, 2017 at 8:14

Sam · Accepted Answer · 2017-07-26 08:46:47Z

3

I may have misunderstood the question and unfortunately I cannot make a comment. If I've grasped this question wrong, i'll delete.

but I'm not sure how to go around selecting only the range needed

This suggests that you have a dynamic amount of data and want to use Range to grab the selections.

Supposing you know the column location of where said data is located (in this case my list starts at B2 and we don't know where it ends. You can use Range to dynamically select all data:

Dim rcell As Range
Dim rng As Range

Set rng = ActiveSheet.Range("B2", Range("B2").End(xlDown))

For Each rcell In rng.Cells
    Debug.Print rcell.Value
Next rcell

End Sub

First we define a Range variable and assign it to the range starting at B2 and using .End(xlDown) we can select a range ending at the final entry.

For further reading on .End() see here.

Hope this helps.

edited Jul 26, 2017 at 8:46

answered Jul 26, 2017 at 8:36

Sam

6746 silver badges22 bronze badges

Sign up to request clarification or add additional context in comments.

8 Comments

Ans Over a year ago

Thank you. Does the End(xlDown) immitate Ctrl+Shift+arrow Down? How can I given the End(xlDown) jumps to the 101th row, copy the range of "A3:F101"?

Sam Over a year ago

"Returns a Range object that represents the cell at the end of the region that contains the source range." If you have data which starts at the specified Range and ends at the 101th row, then this code will grab all values. So yes, in a sense :) If you want to get the range A3:F101 then just specify exactly that: ActiveSheet.Range("A3:F101").Select

Ans Over a year ago

Would Set rng = dataWorkbook.Worksheets("List1").Range("A3:F3", Range("A3").End(xlDown)) do the trick?

Ans Over a year ago

101 was an example. Sometimes it's 101, sometimes it's 64. But it always starts on the 3rd row.

Sam Over a year ago

Oh I see what you mean, yes with a bit of tweaking (I haven't tested it myself but I'm sure it would). May I suggest reading the link in my answer and just playing around with it.

|

Sam · Accepted Answer · 2017-07-26 08:31:43Z

2

You can do this without VBA. Use Get & Transform instead.
Here are a few steps to get you started:

Go to the Data Tab
Under Get & Transform, pick New Query - From File - From Folder
Select the folder containing all your 100+ files
Select the tab that contains your data
You are almost there. Do your final fixes (if needed)
Once you're done, click Close & Load

answered Jul 26, 2017 at 8:31

Sam

5,7411 gold badge21 silver badges36 bronze badges

Comments

ASH · Accepted Answer · 2017-07-26 12:18:07Z

0

This should do what you want.

https://www.rondebruin.nl/win/addins/rdbmerge.htm

answered Jul 26, 2017 at 12:18

ASH

20.5k28 gold badges117 silver badges247 bronze badges

Comments

Ignacio Cabrera · Accepted Answer · 2021-10-26 15:41:35Z

0

i have this Code VBA, its works, i can combine some files on one sheet. check it!

    Sub Open_Files()
Dim Hoja As Object

Application.ScreenUpdating = False
   'Definir la variable como tipo Variante
   Dim X As Variant
   'Abrir cuadro de dialogo
   X = Application.GetOpenFilename _
       ("Excel Files (*.xlsx), *.xlsx", 2, "Abrir archivos", , True)
    'Validar si se seleccionaron archivos
    If IsArray(X) Then ' Si se seleccionan
      'Crea Libro nuevo
       Workbooks.Add
      'Captura nombre de archivo destino donde se grabaran los archivos seleccionados
       A = ActiveWorkbook.Name
      
    '*/********************
   For y = LBound(X) To UBound(X)
   Application.StatusBar = "Importando Archivos: " & X(y)
     Workbooks.Open X(y)
     b = ActiveWorkbook.Name
       For Each Hoja In ActiveWorkbook.Sheets
        Hoja.Copy after:=Workbooks(A).Sheets(Workbooks(A).Sheets.Count)
       Next
       Workbooks(b).Close False
   Next
   Application.StatusBar = "Listo"
   Call Unir_Hojas
End If
Application.ScreenUpdating = False


End Sub

answered Oct 26, 2021 at 15:41

Ignacio Cabrera

1

1 Comment

Community Over a year ago

Your answer could be improved with additional supporting information. Please edit to add further details, such as citations or documentation, so that others can confirm that your answer is correct. You can find more information on how to write good answers in the help center.

Collectives™ on Stack Overflow

Excel vba: combine multiple files in one sheet

4 Answers 4

8 Comments

Comments

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

8 Comments

Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related