Combine All Workbooks from One Folder to One Master file

[Suresh1980]

Hi Everyone,

I have gone through the post related to the topic. It was very helpful.

I kindly request someone to help me out with a similar problem.

My requirement is, I have multiple Excel Files (Say 20 files) with only one sheet (Sheet name: Data Extracted) saved in the same folder as the master file (file name: Consolidate from folder). The sheet will have approximately 10-15 columns and 23 rows (starting from Row 10).

All I want to do is, except the first two columns from all the files to be copied into Master file starting from column C (Because, the master file and all the files in the folder has same information in the first two columns (A & B).

So, once we run the macro, the first two columns should not be disturbed, and the information from all the files from column C till the data available needs to be pasted without any blank columns into master file.

For example, assuming we have 10 files and each file has 10 columns (except first two columns); Once we run the Macro, the final output in the master file (Consolidate from folder) should have only 23 rows and 100 columns (except first two columns) and without any blank columns.

Apologies If its confusing.

[Yongle]

Welcome to the forum Suresh1980.
I think you have explained very well.

1 Please confirm that the folder ONLY contains :
- file named "Consolidate from folder" and
- files to be consolidated (each one containing a worksheet called "Data Extracted")
2 Are there any blank columns in the original files?
3 Is row 10 in original files to be copied into row 1 in the masterfile (if so the masterfile will contain rows 1 to 23) ?
4 What is the name of the worksheet in the masterfile?
thanks

[Suresh1980]

Hi Yongle,

Thank you very much for your time.

1 Please confirm that the folder ONLY contains :
- file named "Consolidate from folder" and
- files to be consolidated (each one containing a worksheet called "Data Extracted") - Yes
2 Are there any blank columns in the original files? - No blank columns
3 Is row 10 in original files to be copied into row 1 in the masterfile (if so the masterfile will contain rows 1 to 23) ? - The information to be copied is from row 10 - row 32 (totally 23 rows) to the masterfile as 2nd row. YES, the masterfile will contain only 23 rows (row 2 to 24).

[mancubus]

i adopted, one of vbax masters, snb's code as below...

test the code with a copy of your master file.



Sub vbax_52535_CombineAllWorkbooks_M_snb()

sn = Split(CreateObject("wscript.shell").Exec("cmd /c Dir """ & ThisWorkbook.Path & "\*.xls*"" /b").StdOut.ReadAll, vbCrLf)

For j = LBound(sn) To UBound(sn)
If sn(j) <> ThisWorkbook.Name And Len(sn(j)) > 0 Then
With GetObject(sn(j)).Sheets("Data Extracted").UsedRange.Offset(9, 2)
ThisWorkbook.Sheets(1).Cells(2, Columns.Count).End(xlToLeft).Offset(, 1).Resize(23, .Columns.Count - 2) = .Resize(23, .Columns.Count - 2)
.Value
.Parent.Parent.Close 0
End With
End If
Next
End Sub

[Suresh1980]

Hi Mancubus,

I am getting the following error which is highlighted in Blue bold

Compile error: Invalid or unqualified reference ( With GetObject(sn(j)).Sheets("Data Extracted").UsedRange.Offset(9, 2).Resize(.Rows.Count - 9, .Columns.Count - 2)

Regards
Suresh

[Suresh1980]

Hi Yongle,

Any luck for me?

Regards
Suresh

[mancubus]

Rows.Count was replaced with 23 previously, since you wanted to import specific number of rows.

i corrected invalid use of With - End With block (which causes the error) in post #4.

consolidation will be in leftmost worksheet because of ThisWorkbook.Sheets(1). you can replace 1 with actual consolidation sheet's name. (such as ThisWorkbook.Sheets("Master") or ThisWorkbook.Sheets("Consolidation").)

[mancubus]

@suresh1980

helpers in forums mostly provide help in their free times. so you should wait. 1 day, 2 days, even a week or more is not a long time even in paid help forums.

in some cases you can get responses in a couple of minutes as well.

on the other hand, there may be other posters who may offer different solutions. they may work on this issue after Office hours.



Perhaps there is only one cardinal sin: impatience. Because of impatience we were driven out of Paradise, because of impatience we cannot return.

W.H.Auden

[Suresh1980]

Apologies for rushing...

I don't how it works, no problem. I will wait.

Regards
Suresh

[Yongle]

@suresh1980
I noticed that mancubus was working with you to provide a solution which is why I did not post anything else. His suggestion is, in fact, very similar to my approach although his code is written in a more concise format. I think you and mancubus are now very close to solving your problem.
It would only confuse matters if I provided you with an alternative solution at this time. I will post my solution for you after you have marked the thread as solved. You will be surprised how different some parts of the code look, despite the approach being almost identical.

[mancubus]

@yongle

please post any code you have written. it's always good to learn and practice different approaches.


ps: i started working on a solution when there was no replies to the thread. only after i posted the code i adopted, i've seen the messages.

[mancubus]

@suresh1980

test the amended code in post 4.

if it won't work for you, post some workbooks to be consolidated, like:
Go Advanced, (scroll down the page),
Manage Attachments,
Add Files,
Browse, (select files to upload),
Upload

replace the sensitive data in these workbooks. we only want to she the table and data structures in your files.

[mancubus]

below worked for me with the attached test files...




Sub vbax_52535_CombineAllWorkbooks_M_snb()

sn = Split(CreateObject("WScript.Shell").Exec("cmd /c Dir """ & ThisWorkbook.Path & "\*.xls*"" /b").StdOut.ReadAll, vbCrLf)

For j = LBound(sn) To UBound(sn)
If sn(j) <> ThisWorkbook.Name And Len(sn(j)) Then
With GetObject(ThisWorkbook.Path & "\" & sn(j)).Sheets("Data Extracted").UsedRange.Offset(9, 2)
ThisWorkbook.Sheets(1).Cells(2, Columns.Count).End(xlToLeft).Offset(, 1).Resize(23, .Columns.Count - 2) = .Resize(23, .Columns.Count - 2).Value
.Parent.Parent.Close 0
End With
End If
Next


End Sub

[Yongle]

An alternative for you. Amend the masterfile sheet name where indicated and try out on a test copy of your masterfile
My test files are attached

Note that FileName = Dir(Path & "\*.xlsx", vbNormal) assumes your file extensions are xlsx - amend if necessary to xls


Sub Combine()

Application.ScreenUpdating = False
Application.EnableEvents = False

Dim Path As String, FileName As String
Dim Wkb As Workbook, MasterWB As Workbook
Dim WS As Worksheet, WSm As Worksheet
Dim ColsToCopy As Long, ColumnOffset As Long

Set MasterWB = ThisWorkbook
Set WSm = MasterWB.Sheets("SheetName") '<<< amend SheetName
ColumnOffset = 2

Path = Application.ActiveWorkbook.Path
FileName = Dir(Path & "\*.xlsx", vbNormal)

Do Until FileName = ""

If FileName <> MasterWB.Name Then
Set Wkb = Workbooks.Open(FileName:=Path & "\" & FileName)
Set WS = Wkb.Sheets("Data Extracted")

ColsToCopy = WS.UsedRange.Columns.Count - 2
WS.Range("C10").Resize(23, ColsToCopy).Copy

WSm.Range("A1").Offset(1, ColumnOffset).PasteSpecial xlAll
ColumnOffset = ColumnOffset + ColsToCopy

End If
Application.DisplayAlerts = False
Wkb.Close False
Application.DisplayAlerts = True
FileName = Dir()
Loop


Application.EnableEvents = True
Application.ScreenUpdating = True


End Sub

[mancubus]

please post your questions here. pm's not needed.

[Suresh1980]

Hi Yongle and Mancubus,

Thank you very much for helping me out.

It was working perfectly fine and exactly matching my requirements.

You both are my hero.

I created a new post to thank you both in case if it fails to post now.

Regards
Suresh