Created
August 20, 2017 03:05
-
-
Save Zhouxing-Su/487f73b6eadbd53d2dc26864f494fb10 to your computer and use it in GitHub Desktop.
Consolidate all worksheets from multiple workbooks (.xlsx files) into single (current) workbook.
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
' http://www.excel-easy.com/vba/examples/import-sheets.html | |
' https://trumpexcel.com/combine-multiple-workbooks-one-excel-workbooks/ | |
' https://stackoverflow.com/questions/579797/open-excel-file-for-reading-with-vba-without-display | |
Sub ConsolidateWorkbooks() | |
Application.ScreenUpdating = False | |
Call AppendWorkbook("D:\workspace\cpp\InventoryRoutingProblem\Deploy\Doc\7 Analysis\", "2.16.CorrelationBetweenObjAndCost.xlsx", "2.16") | |
Call AppendWorkbook("D:\workspace\cpp\InventoryRoutingProblem\Deploy\Doc\7 Analysis\", "2.18.CorrelationBetweenObjAndCost.xlsx", "2.18") | |
Call AppendWorkbook("D:\workspace\cpp\InventoryRoutingProblem\Deploy\Doc\7 Analysis\", "2.20.CorrelationBetweenObjAndCost.xlsx", "2.20") | |
Call AppendWorkbook("D:\workspace\cpp\InventoryRoutingProblem\Deploy\Doc\7 Analysis\", "2.26.CorrelationBetweenObjAndCost.xlsx", "2.26") | |
Application.ScreenUpdating = True | |
ThisWorkbook.Worksheets(1).Activate | |
End Sub | |
Sub AppendWorkbook(fileDir As String, filename As String, sheetNamePrefix As String) | |
Dim sheet As Worksheet | |
Workbooks.Open Filename:=fileDir & filename, ReadOnly:=True | |
ActiveWindow.Visible = False | |
For Each sheet In Workbooks(filename).Sheets | |
sheet.Copy After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count) | |
ActiveSheet.Name = sheetNamePrefix '& sheet.Name | |
Next sheet | |
Workbooks(filename).Close False | |
End Sub |
@canadaduane Sorry, I have no idea about your error.
I'm not an expert on Excel or VBA, this snippet is obtained by macro recorder.
Maybe it's just because your files are too big.
I suspect it has something to do with the way Excel creates ranges in its worksheets. I don't know much about it, but it seems the app pre-allocates cells as a range, and I'm wondering if I need to somehow tell it to pre-allocate a larger range when importing larger files.
In any case, thanks for your thoughtful reply!
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
When running this, I received this puzzling error:
(When using Copy/Paste I get a different error related to the size. I have about 100k rows in the sheet).