How to include file names while merging multiple files through VBA

[kaywantstobeexcelmaster]

Hey, there!

I am trying to merge about 400 excel files into one master file, which I have been able to successfully do. HOWEVER, I also want the first column to include all the file names corresponding to the data. Can anybody help with that?

I am quite new to VBA and trying really hard to learn. Any help would be much appreciated.

Thanks
Kay

 

[DanteAmor]

Welcome to the forum!

Which sheet or which sheets do you want to include?
Which row or rows, which column or columns?

So, for example,
book1, sheet1, range A1:D10
copy and paste into masterbook, sheet1, range A1:D10.
book2, sheet1, range A1:D2; where does it paste? on sheet1 below the previous data, or on a new sheet?


All that information you must provide, and surely several experts could help you.

 

[kaywantstobeexcelmaster]

Hi, Dante!

Sure.

So, here are the details:
1. Each of the 400 files has the exact same 4 columns.
2. The rows are numeric entries based on data collected from 400 different categories.
3. There is only one sheet in every file.
4. So, for example
Book 1 Sheet 1 has data
A B C D
0.1 0.3 0.5 0.2

Other books would have data in the same format.

What I would like is to have data from all books to be combined in a single book with four columns A, B, C, D .... and 400 rows with entries from all books.

BUT, there is also a twist. All the books have a specific name describing the categories. For eg. KayWL0192 (WL - weight limit, 01 - Zone, 92 - year). What I want is another column to be added in the combined book where the name of the book will be added in the row corresponding to the entry from that book. So. the final book should probably look like:

Name of Book A B C D
KayWL0192 0.1 0.3 0.4 0.2
KayWL0294 0.9 0.4 0.2 0.0

and, so on.

Note: Some books might have multiple rows.


I really hope this helps.

 

[kaywantstobeexcelmaster]

Hi, Dante!

Sure.

So, here are the details:
1. Each of the 400 files has the exact same 4 columns.
2. The rows are numeric entries based on data collected from 400 different categories.
3. There is only one sheet in every file.
4. So, for example
Book 1 Sheet 1 has data
A B C D
1 3 2 10

Other books would have data in the same format.

What I would like is to have data from all books to be combined in a single book with four columns A, B, C, D .... and 400 rows with entries from all books.

BUT, there is also a twist. All the books have a specific name describing the categories. For eg. KayWL0192 (WL - weight limit, 01 - Zone, 92 - year). What I want is another column to be added in the combined book where the name of the book will be added in the row corresponding to the entry from that book. So. the final book should probably look like:

Book Name A B C D
KayWL0192 1 3 2 10
KayWL0294 9 5 7 20

and, so on.

Note: Some books might have multiple rows.


I really hope this helps

 

[DanteAmor]

Try this

Change C:\books\ by your folder with files.

Sub include_file_names()
  Dim sh1 As Worksheet, sh2 As Worksheet, wFolder As String, wFile As Variant
  Dim wb1 As Workbook, wb2 As Workbook, lr As Long
  
  Application.ScreenUpdating = False
  Set wb1 = ThisWorkbook
  Set sh1 = wb1.Sheets(1)
  sh1.Rows("2:" & Rows.Count).ClearContents
  
  wFolder = "[COLOR=#ff0000][B]C:\books\[/B][/COLOR]"
  If Right(wFolder, 1) <> "\" Then wFolder = wFolder & "\"
  
  If Dir(wFolder, vbDirectory) = "" Then
    MsgBox "The folder does not exist!", vbCritical, "include_file_names"
    Exit Sub
  End If
  
  wFile = Dir(wFolder & "*.xls*")
  Do While wFile <> ""
    Set wb2 = Workbooks.Open(wFolder & wFile)
    Set sh2 = wb2.Sheets(1)
    sh2.Range("A1:D" & sh2.Range("A" & Rows.Count).End(xlUp).Row).Copy
    lr = sh1.Range("A" & Rows.Count).End(xlUp)(2).Row
    sh1.Range("B" & lr).PasteSpecial xlPasteValues
    sh1.Range("A" & lr & ":A" & sh1.Range("B" & Rows.Count).End(xlUp).Row).Value = wFile
    wb2.Close
    wFile = Dir()
  Loop
  Application.ScreenUpdating = True
  MsgBox "End Process!", vbInformation, "include_file_names"
End Sub

 

[kaywantstobeexcelmaster]

Hi Dante!

Thank you. The code does include the file name for which I am happy, but it only somehow includes the first row and dismisses all rows below it.

UPDATE: I found that it I put all files in a single folder and on the Home Bar, use "Data>Get Data>From Folder>Combine and Load> Okay" I get pretty much the same result with all files combined together with individual file names.

 

[DanteAmor]

This already worked for you?