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 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 |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
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!