I have finished testing the system I proposed in my original answer. It is not exactly the same, for reasons I will explain later, but it matches in all important details. I am posting it as new answer so there is no confusion.
To test it, I created some workbooks which I named Test1, Test2, Test3 and so on. Within each workbook I created a worksheet “Shipped”. Each of these worksheets had a different number of rows and columns. Each cell contained “T-R-C” where T was the test number, R was the row and C was the column. These values made it very easy to check that data was copied correctly from the attachments to the consolidated worksheet. After deleting most of the rows so the structure was visible, the result of consolidation was:
You can see that my code can combine all the rows and all the columns from as many emails as required. My emails are not a week apart but that is not important.
My recommendation is that you try my macro as it is. You can then discuss the appearance with your colleagues, and we can then discuss how to change my macro to match your exact requirements.
Create a new disc folder and within it create two new workbooks: one ordinary (xlsx) and one macro-enabled (xlsm).
Name the ordinary workbook “Consolidated Data.xlsx”. Within it, rename the default worksheet as “Shipped”.
The name of the macro-enabled workbook is unimportant as is the name of the worksheet. Within the VBA Editor, create three modules and name then "LibExcel", "LibOutlook" and "ModConsolidate". Naming modules is not essential but dividing macros up by purpose and naming modules for those purposes makes life much easier.
I will tell you to move the code below to one of these three modules.
Module "ModConsolidate" is for code I have written specifically for your requirement. Module "LibExcel" is for code from my library of Excel related routines. Module "LibOutlook" is for code from my library of Outlook related routines.
When I end a project, I look through it to see if there is any code I might wish to use again. If there is, I extract it and save it in "PERSONAL.XLSB" which I use as my library. Any macro saved in this workbook is available to all other workbooks. Don’t bother today but when you have some spare time look up how to create "PERSONAL.XLSB". When you have created it, move modules "LibExcel" and "LibOutlook" to it. In "LibExcel", I have routines to find the last used row and column of a worksheet and to check is a named worksheet exists. In "LibOutlook" I have routines for opening and closing an instance of Outlook from Excel.
When I start a project, I look through my library for routines that might be appropriate. If necessary, a routine will be enhanced to provide functionality that I had not needed before. The result is I have a library of useful functions that get more powerful, and larger, as I complete each project.
I said I would have version numbers on the workbook I created for you. Unfortunately, the macros that handle this and related functionality are too large to post to Stack Overflow.
This code should go in LibExcel:
' Routines useful with Excel
Option Explicit
Public Sub FindLastRowCol(ByRef Wsht As Worksheet, ByRef RowLast As Long, _
ByRef ColLast As Long)
' Sets RowLast and ColLast to the last row and column with a value
' in worksheet Wsht
' The motivation for coding this routine was the discovery that Find by
' previous row found a cell formatted as Merge and Center but Find by
' previous column did not.
' I had known the Find would miss merged cells but this was new to me.
' Dec16 Coded
' 31Dec16 Corrected handling of UserRange
' 15Feb17 SpecialCells was giving a higher row number than Find for
' no reason I could determine. Added code to check for a
' value on rows and columns above those returned by Find
' 25Jun17 Found column with value about that found by Find
Dim ColCrnt As Long
Dim ColLastFind As Long
Dim ColLastOther As Long
Dim ColLastTemp As Long
Dim ColLeft As Long
Dim ColRight As Long
Dim Rng As Range
Dim RowIncludesMerged As Boolean
Dim RowBot As Long
Dim RowCrnt As Long
Dim RowLastFind As Long
Dim RowLastOther As Long
Dim RowLastTemp As Long
Dim RowTop As Long
With Wsht
Set Rng = .Cells.Find("*", .Range("A1"), xlFormulas, , xlByRows, xlPrevious)
If Rng Is Nothing Then
RowLastFind = 0
ColLastFind = 0
Else
RowLastFind = Rng.Row
ColLastFind = Rng.Column
End If
Set Rng = .Cells.Find("*", .Range("A1"), xlValues, , xlByColumns, xlPrevious)
If Rng Is Nothing Then
Else
If RowLastFind < Rng.Row Then
RowLastFind = Rng.Row
End If
If ColLastFind < Rng.Column Then
ColLastFind = Rng.Column
End If
End If
Set Rng = .Range("A1").SpecialCells(xlCellTypeLastCell)
If Rng Is Nothing Then
RowLastOther = 0
ColLastOther = 0
Else
RowLastOther = Rng.Row
ColLastOther = Rng.Column
End If
Set Rng = .UsedRange
If Rng Is Nothing Then
Else
If RowLastOther < Rng.Row + Rng.Rows.Count - 1 Then
RowLastOther = Rng.Row + Rng.Rows.Count - 1
End If
If ColLastOther < Rng.Column + Rng.Columns.Count - 1 Then
ColLastOther = Rng.Column + Rng.Columns.Count - 1
End If
End If
If RowLastFind < RowLastOther Then
' Higher row found by SpecialCells or UserRange
Do While RowLastOther > RowLastFind
ColLastTemp = .Cells(RowLastOther, .Columns.Count).End(xlToLeft).Column
If ColLastTemp > 1 Or .Cells(RowLastOther, 1).Value <> "" Then
Debug.Assert False
' Is this possible
' Row after RowLastFind has value
RowLastFind = RowLastOther
Exit Do
End If
RowLastOther = RowLastOther - 1
Loop
ElseIf RowLastFind > RowLastOther Then
Debug.Assert False
' Is this possible
End If
RowLast = RowLastFind
If ColLastFind < ColLastOther Then
' Higher column found by SpecialCells or UserRange
Do While ColLastOther > ColLastFind
RowLastTemp = .Cells(.Rows.Count, ColLastOther).End(xlUp).Row
If RowLastTemp > 1 Or .Cells(1, ColLastOther).Value <> "" Then
'Debug.Assert False
' Column after ColLastFind has value
' Possible causes:
' * Find does not recognise merged cells
' ' Find does not examine hidden cells
ColLastFind = ColLastOther
Exit Do
End If
ColLastOther = ColLastOther - 1
Loop
ElseIf ColLastFind > ColLastOther Then
Debug.Assert False
' Is this possible
End If
ColLast = ColLastFind
End With
End Sub
Public Function WshtExists(ByRef Wbk As Workbook, ByVal WshtName As String) As Boolean
' Returns True if Worksheet WshtName exists within
' * if Wbk Is Nothing the workbook containing the macros
' * else workbook Wbk
' 21Aug16 Coded by Tony Dallimore
' 14Feb17 Coded alternative routine that cycled through the existing worksheets
' matching their names against WshtName to check if use of "On Error Resume Next"
' was the faster option. I needed to call the routines 6,000,000 times each to
' get an adequate duration for comparison. This version took 33 seconds while
' the alternative took 75 seconds.
' 21Feb20 Added "As Boolean" to declaration. Do not understand how routine worked
' without it.
Dim WbkLocal As Workbook
Dim Wsht As Worksheet
If Wbk Is Nothing Then
Set WbkLocal = ThisWorkbook
Else
Set WbkLocal = Wbk
End If
Err.Clear
On Error Resume Next
Set Wsht = WbkLocal.Worksheets(WshtName)
On Error GoTo 0
If Wsht Is Nothing Then
WshtExists = False
Else
WshtExists = True
End If
End Function
This code should go in LibOutlook
' Routines useful with Outlook.
Option Explicit
Public Sub OutAppClose(ByRef OutApp As Outlook.Application, ByVal Created As Boolean)
' If Created is True, quit the current instance if Outlook.
If Created Then
OutApp.Quit
End If
Set OutApp = Nothing
End Sub
Public Function OutAppGetCreate(ByRef Created As Boolean) As Outlook.Application
' Return a reference to the Outlook Application.
' Set Created to True if the reference is to a new application and to
' False if the reference is to an existing application.
' If Nothing is returned, the routine has been unable to get or create a reference.
' Only one instance of Outlook can be running. CreateObject("Outlook.Application")
' will return a reference to the existing instance if one is already running or
' will start a new instance if one is not running. The disadvantage of using
' CreateObject, is the caller does not know if Outlook was running so does not know
' whether or not to quit Outlook when it has finished using Outlook. By setting
' Created, this routine allows the caller to only quit if this is appropriate.
Set OutAppGetCreate = Nothing
On Error Resume Next
Set OutAppGetCreate = GetObject(, "Outlook.Application")
On Error GoTo 0
If OutAppGetCreate Is Nothing Then
On Error Resume Next
Set OutAppGetCreate = CreateObject("Outlook.Application")
On Error GoTo 0
If OutAppGetCreate Is Nothing Then
Call MsgBox("I am unable to access Outlook", vbOKOnly)
Exit Function
End If
Created = True
Else
Created = False
End If
End Function
This code should go in ModConsolidate:
Option Explicit
' * Need reference to "Microsoft Outlook nn.n Object Library"
' where nn.n depends on the version of Office being used.
' * Needs reference to "Microsoft Scripting Runtime"
Const HeaderForData As String = "Data from email received"
Const WbkConName As String = "Consolidated Data.xlsx"
Const WshtName As String = "Shipped" ' Also used for name of workbooks
Sub ConsolidateDataFromShippedWshts() ()
' Outlook used "ol" as a prefix for its constants. I do not use the same
' prefix to avoid a clash.
Dim OutApp As Outlook.Application
Dim OutAppCreated As Boolean
Dim ColConLast As Long ' Last column of worksheet "Shipped" in consolidated workbook
Dim ColSrcLast As Long ' Last column of worksheet "Shipped" in source workbook
Dim DateLatestExisting As Date ' Date of last block of data in consolidated workbook
Dim DateStr As String ' Date extracted from header row
Dim FldrShipped As Outlook.Folder ' Outlook Folder containing source emails
Dim InxA As Long ' Index into attachments
Dim InxI As Long ' Index into mail items
Dim InxW As Long ' Into into WbkSrcNames
Dim ItemsShipped As Items ' Items in source folder
Dim Path As String ' Disc folder containing workbooks
Dim Rng As Range ' Various uses
Dim RowConCrnt As Long ' Current row of worksheet "Shipped" in consolidated workbook
Dim RowConLast As Long ' Last row of worksheet "Shipped" in consolidated workbook
Dim RowSrcLast As Long ' Last row of worksheet "Shipped" in source workbook
Dim WbkCon As Workbook ' Consolidated workbook
Dim WbkMacros As Workbook ' This workbook
Dim WbkSrc As Workbook ' Workbook extracted from email
Dim WbkSrcName As String ' Name of workbook extracted from email
Dim WbkSrcNameDates As Collection ' Collection of the names and dates of workbooks extracted from emails
Dim WshtCon As Worksheet ' Worksheet "Shipped" in consolidated workbook
Dim WshtSrc As Worksheet ' Worksheet "Shipped" in source workbook
Application.ScreenUpdating = False
Set WbkMacros = ThisWorkbook
Path = WbkMacros.Path
' ### Change if you want a different name for consolidated workbook
Set WbkCon = Workbooks.Open(Path & "\" & WbkConName)
Set WshtCon = WbkCon.Worksheets(WshtName)
' Find last used row of consolidated worksheet
Call FindLastRowCol(WshtCon, RowConLast, ColConLast)
If RowConLast = 0 Then
' No data added yet
DateLatestExisting = 0
Else
' Search up for header for last block of data added
With WshtCon
Set Rng = .Columns(1).Find( _
What:=HeaderForData, After:=.Cells(RowConLast + 1, 1), _
LookIn:=xlValues, LookAt:=xlPart, _
SearchOrder:=xlByRows, SearchDirection:=xlPrevious, _
MatchCase:=False, SearchFormat:=False)
If Rng Is Nothing Then
Debug.Assert False
' It should not be possible to be here. Either the worksheet is empty
' and RowColLast = 0 or one or more blocks of data, each with a header,
' have been added. It appears the worksheet is not as it should be.
DateLatestExisting = 0
Else
DateStr = Mid$(.Cells(Rng.Row, 1).Value, Len(HeaderForData) + 2)
If IsDate(DateStr) Then
DateLatestExisting = DateValue(DateStr) + TimeValue(DateStr)
Else
Debug.Assert False
' It should not be possible to be here. The text after HeaderForData
' should be a valid date. It appears the worksheet is not as it should be.
DateLatestExisting = 0
End If
End If
End With
End If
Set OutApp = OutAppGetCreate(OutAppCreated)
If OutApp Is Nothing Then
' OutAppGetCreated() failed. The user has already been told.
Exit Sub
End If
' ### Change to access folder where you store these emails
Set FldrShipped = OutApp.Session.Folders("MyName@MyIsp").Folders("Test")
' Create list of items in folder sorted by ReceivedTime
Set ItemsShipped = FldrShipped.Items
ItemsShipped.Sort "ReceivedTime", True
Set WbkSrcNameDates = New Collection
' Read items, newest first, until reach an item at or before DateLatestExisting
' Save xlsx attachment, if any, and record names in WbkSrcNames
For InxI = 1 To ItemsShipped.Count
If TypeName(ItemsShipped(InxI)) = "MailItem" Then
If ItemsShipped(InxI).ReceivedTime <= DateLatestExisting Then
' No more unprocessed emails
Exit For
End If
' Save Xlsx attachment, if any
For InxA = 1 To ItemsShipped(InxI).Attachments.Count
If LCase(Right$(ItemsShipped(InxI).Attachments(InxA).FileName, 5)) = ".xlsx" Then
' Have found required attachment. Save with name based on date received
WbkSrcName = WshtName & " " & Format(ItemsShipped(InxI).ReceivedTime, "yymmdd hhmmss") & ".xlsx"
ItemsShipped(InxI).Attachments(InxA).SaveAsFile Path & "\" & WbkSrcName
WbkSrcNameDates.Add VBA.Array(WbkSrcName, ItemsShipped(InxI).ReceivedTime)
Exit For
End If
Next
End If
Next
Call OutAppClose(OutApp, OutAppCreated)
If WbkSrcNameDates.Count = 0 Then
' No new emails with xlsx attachments
WbkCon.Close SaveChanges:=False
Call MsgBox("No new emails containing an xlsx attachment", vbOKOnly)
Set WshtCon = Nothing
Set WbkCon = Nothing
Set WbkMacros = Nothing
Exit Sub
End If
' WbkSrcNameDates contains the names and received dates of the new workbooks
' with the newest first.
' Extract names in reverse order (oldest first) and add contents of worksheet
' "Shipped" to bottom of worksheet "Shipped" of consolidated workbook
For InxW = WbkSrcNameDates.Count To 1 Step -1
Set WbkSrc = Workbooks.Open(Path & "\" & WbkSrcNameDates(InxW)(0))
If WshtExists(WbkSrc, WshtName) Then
' Worksheet "Shipped" exists
Set WshtSrc = WbkSrc.Worksheets(WshtName)
Call FindLastRowCol(WshtSrc, RowSrcLast, ColSrcLast)
RowConCrnt = RowConLast + 1 ' Advance to first free row
With WshtCon.Cells(RowConCrnt, 1)
.Value = HeaderForData & " " & Format(WbkSrcNameDates(InxW)(1), "d-mmm-yy h:mm:ss")
.Font.Bold = True
End With
RowConCrnt = RowConCrnt + 1
With WshtSrc
.Range(.Cells(1, 1), .Cells(RowSrcLast, ColSrcLast)).Copy _
Destination:=WshtCon.Cells(RowConCrnt, 1)
End With
RowConLast = RowConCrnt + RowSrcLast - 1
End If
WbkSrc.Close SaveChanges:=False
Next
' Position cursor to header for latest data
Application.ScreenUpdating = True
WshtCon.Activate
WshtCon.Cells(RowConLast - RowSrcLast, 1).Select
Application.Goto ActiveCell, True
WbkCon.Close SaveChanges:=True
Set WshtCon = Nothing
Set WbkCon = Nothing
Set WbkMacros = Nothing
End Sub
At the top of ModConsolidate, it says it needs references to "Microsoft Outlook nn.n Object Library", where nn.n depends on the version of Office being used, and "Microsoft Scripting Runtime". If you are unsure what that means, ask and I will add an explanation.
Line 173 of ModConsolidate is Set FldrShipped = OutApp.Session.Folders("MyName@MyIsp").Folders("Test"). This references the Outlook folder in which I placed the test emails. Replace my Outlook folder with the one holding these emails on your system. Place as many of these emails as you have in that folder.
Run macro ConsolidateDataFromShippedWshts(). This macro will:
- Open workbook “Consolidated Data.xlsx”
- Check worksheet “Shipped” and find that it is empty.
- Open Outlook if not already open.
- Access the Outlook folder and extract the workbook from every email because worksheet “Shipped” is empty. Workbooks will be saved with the name “Shipped yymmdd hhmmss.xlsx”. If worksheet “Shipped” had not been empty, it would only have extracted workbooks from the newer emails.
- Close Outlook if it was not open.
- Open each of the new workbooks in turn and add the contents of their worksheet “Shipped” to worksheet “Shipped” within “Consolidated Data.xlsx”.
I have tested macro ConsolidateDataFromShippedWshts() thoroughly but only with my fake workbooks and emails. It should work properly unless I have misunderstood the nature of your workbooks and emails. If something goes wrong, describe the problem to me and I will try to diagnose the cause.
If everything works as expected. Review “Consolidated Data.xlsx” and discuss it with your colleagues. While you are doing that, I will start adding more information about my macro to this answer.
