Hi there,
This is my first post here, although I have utilised this forum many times by reading other threads to help me solve my excel problems. Unfortunately this time I have been unable to reach a solution myself, so I'm hopeful that someone may help educate me. I am relatively novice at VBA coding, I can somewhat understand what is happening in other peoples codes, but writing myself is certainly a work in progress. But it is a work in progress!
My Task:
I am a civil engineer who manages a large portfolio of small construction jobs. Each of these jobs has its own excel workbook that keeps track of all sorts of data (completion dates, budgets, defects etc.). I am aiming to create a summary tracking workbook in Excel 2013 that summaries the data of all these jobs. Ideally this summary workbook will:
1.) Automatically update on opening. So when data within any of the specific job workbooks is updated, the summary workbook reflects these changes.
2.) Pull in data from every workbook within a specified folder, so that new jobs can be added to the summary workbook by putting them in the specified folder without modifying the code.
3.) Extract all data from the source job workbooks within specified columns all the way to the last row containing data, rather than having to specify an exact range within the code. This is because the jobs do not necessarily have the same amount of data (e.g. one job may have 5 defects, whilst the next may have 50 defects).
My Attempt To-Date:
I have attempted to achieve the above tasks by merging together two pieces of other peoples written code, however unfortunately I haven't been able to get a code that achieves all my tasks yet.
Source 1: https://msdn.microsoft.com/en-us/library/office/gg549168(v=office.14).aspx
This code was written for Excel 2007/2010 and I haven't been able to get this to work properly in 2013. I'm unsure if that is a problem with how I'm running it or its an issue with the Excel version.
Sub MergeAllWorkbooks()
Dim SummarySheet As Worksheet
Dim FolderPath As String
Dim NRow As Long
Dim FileName As String
Dim WorkBk As Workbook
Dim SourceRange As Range
Dim DestRange As Range
' Create a new workbook and set a variable to the first sheet.
Set SummarySheet = Workbooks.Add(xlWBATWorksheet).Worksheets(1)
' Modify this folder path to point to the files you want to use.
FolderPath = "C:\Users\Peter\invoices"
' NRow keeps track of where to insert new rows in the destination workbook.
NRow = 1
' Call Dir the first time, pointing it to all Excel files in the folder path.
FileName = Dir(FolderPath & "*.xl*")
' Loop until Dir returns an empty string.
Do While FileName <> ""
' Open a workbook in the folder
Set WorkBk = Workbooks.Open(FolderPath & FileName)
' Set the cell in column A to be the file name.
SummarySheet.Range("A" & NRow).Value = FileName
' Set the source range to be A9 through C9.
' Modify this range for your workbooks.
' It can span multiple rows.
Set SourceRange = WorkBk.Worksheets(1).Range("A9:C9")
' Set the destination range to start at column B and
' be the same size as the source range.
Set DestRange = SummarySheet.Range("B" & NRow)
Set DestRange = DestRange.Resize(SourceRange.Rows.Count, _
SourceRange.Columns.Count)
' Copy over the values from the source to the destination.
DestRange.Value = SourceRange.Value
' Increase NRow so that we know where to copy data next.
NRow = NRow + DestRange.Rows.Count
' Close the source workbook without saving changes.
WorkBk.Close savechanges:=False
' Use Dir to get the next file name.
FileName = Dir()
Loop
' Call AutoFit on the destination sheet so that all
' data is readable.
SummarySheet.Columns.AutoFit
End Sub
The theory behind this code covers my Task 2. as it extracts data from all of the workbooks within a specified folder, rather than having to input each of the source workbook names within the code. However it does not satisfy Task 1 as instead of automatically updating my summary workbook, it creates a new workbook each time it is run - which is not what I'm trying to achieve. The original code doesn't cover Task 3 either as I'm required to set a source data range (e.g A9 to C9) - however within the same website link there is the following modification that extracts all data from specified columns up to the last row which contains data. This then achieves my Task 3.
Dim LastRow As Long
LastRow = WorkBk.Worksheets(1).Cells.Find(What:="*", _
After:=WorkBk.Worksheets(1).Cells.Range("A1"), _
SearchDirection:=xlPrevious, _
LookIn:=xlFormulas, _
SearchOrder:=xlByRows).Row
Set SourceRange = WorkBk.Worksheets(1).Range("A8:K" & LastRow)
So this code is close, but not quite there. Aside from the fact that I couldn't get it to run at all in Excel 2013!
Source 2: http://www.encodedna.com/excel/copy-data-from-closed-excel-workbook-without-opening.htm
Now from this source I found a slightly varied code, this one I could successfully run within Excel 2013.
Option Explicit
Private Sub Workbook_Open()
Call ReadDataFromCloseFile
End Sub
Sub ReadDataFromCloseFile()
On Error GoTo ErrHandler
Application.ScreenUpdating = False
Dim src As Workbook
' OPEN THE SOURCE EXCEL WORKBOOK IN "READ ONLY MODE".
Set src = Workbooks.Open("C:\Q-SALES.xlsx", True, True)
' GET THE TOTAL ROWS FROM THE SOURCE WORKBOOK.
Dim iTotalRows As Integer
iTotalRows = src.Worksheets("sheet1").Range("B1:B" & Cells(Rows.Count, "B").End(xlUp).Row).Rows.Count
' COPY DATA FROM SOURCE (CLOSE WORKGROUP) TO THE DESTINATION WORKBOOK.
Dim iCnt As Integer ' COUNTER.
For iCnt = 1 To iTotalRows
Worksheets("Sheet1").Range("B" & iCnt).Formula =
src.Worksheets("Sheet1").Range("B" & iCnt).Formula
Next iCnt
' CLOSE THE SOURCE FILE.
src.Close False ' FALSE - DON'T SAVE THE SOURCE FILE.
Set src = Nothing
ErrHandler:
Application.EnableEvents = True
Application.ScreenUpdating = True
End Sub
This code is input into the Workbook_Open() event in ThisWorkBook object in VBA. This means that the data is updated immediately when the summary workbook is opened - hence it satisfies my Task 1. However this code does not achieve Task 2. - it requires the source workbook to be specified within the code, so due to the sheer number of my source jobs and the need to regularly add new source jobs, it means this will still be a labour intensive task. It also does not satisfy my Task 3. as it requires a specific source data range to be input into the code.
I have made many attempts myself to merge these codes into one that satisfies all 3 tasks. I would post these, but I feel they may just add confusion. I'd be very grateful for any assistance with this task - as I may be approaching this from the wrong direction entirely.
Thanks and regards,
Cappy
This is my first post here, although I have utilised this forum many times by reading other threads to help me solve my excel problems. Unfortunately this time I have been unable to reach a solution myself, so I'm hopeful that someone may help educate me. I am relatively novice at VBA coding, I can somewhat understand what is happening in other peoples codes, but writing myself is certainly a work in progress. But it is a work in progress!
My Task:
I am a civil engineer who manages a large portfolio of small construction jobs. Each of these jobs has its own excel workbook that keeps track of all sorts of data (completion dates, budgets, defects etc.). I am aiming to create a summary tracking workbook in Excel 2013 that summaries the data of all these jobs. Ideally this summary workbook will:
1.) Automatically update on opening. So when data within any of the specific job workbooks is updated, the summary workbook reflects these changes.
2.) Pull in data from every workbook within a specified folder, so that new jobs can be added to the summary workbook by putting them in the specified folder without modifying the code.
3.) Extract all data from the source job workbooks within specified columns all the way to the last row containing data, rather than having to specify an exact range within the code. This is because the jobs do not necessarily have the same amount of data (e.g. one job may have 5 defects, whilst the next may have 50 defects).
My Attempt To-Date:
I have attempted to achieve the above tasks by merging together two pieces of other peoples written code, however unfortunately I haven't been able to get a code that achieves all my tasks yet.
Source 1: https://msdn.microsoft.com/en-us/library/office/gg549168(v=office.14).aspx
This code was written for Excel 2007/2010 and I haven't been able to get this to work properly in 2013. I'm unsure if that is a problem with how I'm running it or its an issue with the Excel version.
Sub MergeAllWorkbooks()
Dim SummarySheet As Worksheet
Dim FolderPath As String
Dim NRow As Long
Dim FileName As String
Dim WorkBk As Workbook
Dim SourceRange As Range
Dim DestRange As Range
' Create a new workbook and set a variable to the first sheet.
Set SummarySheet = Workbooks.Add(xlWBATWorksheet).Worksheets(1)
' Modify this folder path to point to the files you want to use.
FolderPath = "C:\Users\Peter\invoices"
' NRow keeps track of where to insert new rows in the destination workbook.
NRow = 1
' Call Dir the first time, pointing it to all Excel files in the folder path.
FileName = Dir(FolderPath & "*.xl*")
' Loop until Dir returns an empty string.
Do While FileName <> ""
' Open a workbook in the folder
Set WorkBk = Workbooks.Open(FolderPath & FileName)
' Set the cell in column A to be the file name.
SummarySheet.Range("A" & NRow).Value = FileName
' Set the source range to be A9 through C9.
' Modify this range for your workbooks.
' It can span multiple rows.
Set SourceRange = WorkBk.Worksheets(1).Range("A9:C9")
' Set the destination range to start at column B and
' be the same size as the source range.
Set DestRange = SummarySheet.Range("B" & NRow)
Set DestRange = DestRange.Resize(SourceRange.Rows.Count, _
SourceRange.Columns.Count)
' Copy over the values from the source to the destination.
DestRange.Value = SourceRange.Value
' Increase NRow so that we know where to copy data next.
NRow = NRow + DestRange.Rows.Count
' Close the source workbook without saving changes.
WorkBk.Close savechanges:=False
' Use Dir to get the next file name.
FileName = Dir()
Loop
' Call AutoFit on the destination sheet so that all
' data is readable.
SummarySheet.Columns.AutoFit
End Sub
The theory behind this code covers my Task 2. as it extracts data from all of the workbooks within a specified folder, rather than having to input each of the source workbook names within the code. However it does not satisfy Task 1 as instead of automatically updating my summary workbook, it creates a new workbook each time it is run - which is not what I'm trying to achieve. The original code doesn't cover Task 3 either as I'm required to set a source data range (e.g A9 to C9) - however within the same website link there is the following modification that extracts all data from specified columns up to the last row which contains data. This then achieves my Task 3.
Dim LastRow As Long
LastRow = WorkBk.Worksheets(1).Cells.Find(What:="*", _
After:=WorkBk.Worksheets(1).Cells.Range("A1"), _
SearchDirection:=xlPrevious, _
LookIn:=xlFormulas, _
SearchOrder:=xlByRows).Row
Set SourceRange = WorkBk.Worksheets(1).Range("A8:K" & LastRow)
So this code is close, but not quite there. Aside from the fact that I couldn't get it to run at all in Excel 2013!
Source 2: http://www.encodedna.com/excel/copy-data-from-closed-excel-workbook-without-opening.htm
Now from this source I found a slightly varied code, this one I could successfully run within Excel 2013.
Option Explicit
Private Sub Workbook_Open()
Call ReadDataFromCloseFile
End Sub
Sub ReadDataFromCloseFile()
On Error GoTo ErrHandler
Application.ScreenUpdating = False
Dim src As Workbook
' OPEN THE SOURCE EXCEL WORKBOOK IN "READ ONLY MODE".
Set src = Workbooks.Open("C:\Q-SALES.xlsx", True, True)
' GET THE TOTAL ROWS FROM THE SOURCE WORKBOOK.
Dim iTotalRows As Integer
iTotalRows = src.Worksheets("sheet1").Range("B1:B" & Cells(Rows.Count, "B").End(xlUp).Row).Rows.Count
' COPY DATA FROM SOURCE (CLOSE WORKGROUP) TO THE DESTINATION WORKBOOK.
Dim iCnt As Integer ' COUNTER.
For iCnt = 1 To iTotalRows
Worksheets("Sheet1").Range("B" & iCnt).Formula =
src.Worksheets("Sheet1").Range("B" & iCnt).Formula
Next iCnt
' CLOSE THE SOURCE FILE.
src.Close False ' FALSE - DON'T SAVE THE SOURCE FILE.
Set src = Nothing
ErrHandler:
Application.EnableEvents = True
Application.ScreenUpdating = True
End Sub
This code is input into the Workbook_Open() event in ThisWorkBook object in VBA. This means that the data is updated immediately when the summary workbook is opened - hence it satisfies my Task 1. However this code does not achieve Task 2. - it requires the source workbook to be specified within the code, so due to the sheer number of my source jobs and the need to regularly add new source jobs, it means this will still be a labour intensive task. It also does not satisfy my Task 3. as it requires a specific source data range to be input into the code.
I have made many attempts myself to merge these codes into one that satisfies all 3 tasks. I would post these, but I feel they may just add confusion. I'd be very grateful for any assistance with this task - as I may be approaching this from the wrong direction entirely.
Thanks and regards,
Cappy
