How to include file names while merging multiple files through VBA

Joined
Aug 23, 2019
Messages
4
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
 

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying
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.
 
Last edited:
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
Try this

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

Code:
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
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,581
Messages
6,114,461
Members
448,573
Latest member
BEDE

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top