[X]My Assistant

[rgutierrez10]

Hello,

I am aware of the scripts that merge all the excel files in a particular folder into one master file (see link below). All the Sheet1's of each file are grouped into one master file.

I use this to combine 30 reports I receive every month (1x per day), but on the master file, I still have to add a column to state which date the report pertains to. None of my original 30 reports say in which date they were run, so I have to add a column on the merged worksheet and click-drag the dates. This mildly defeats the purpose of a script.

My question is: Is there a way to add to that script and add an additional column to the merged data that specified the "Date modified" or "date created" of the report it originated from?

This would save me so much time and would prevent many mistakes, so your help will be appreciated. I am a ripe beginner to scripts.


Link to Show Script to merge excel files

[dflak]

Do the file names have a date stamp on them indicating the day for which they are for? E.g something like 20151130 or even 11-30-15?

When are the reports run? Are they run in the evening for the day's event or are they run the following morning for the previous day? It is possible to read the file creation date but we'd have to know what it means.

I assume you want one tab per daily report.

What else can you tell us about these files. Do they have a fixed range or does the number of rows vary?

[dflak]

Oh, and one more thing. What version of Excel are you using. VB6 went out with Excel 2000, I believe. Any help anyone could give you might not be backwards compatible.

[rgutierrez10]

Oops, I am actually on Excel 2007. The File names unfortunately do not have any date on them, but they do have a unique "report number" for that instance. You actually gave me a great ID.

The file names look roughly like this:

"ALL EMPLOYEES_Report#224"
"ALL EMPLOYEES_Report#265"
"ALL EMPLOYEES_Report#364"

If you can help me get just the report number on an extra column called "Report #", I can just copy the folder location into my Google Chrome, and Chrome will get me a text version of all the file names containing the file name and the Date Created. I could then do an Index Match of the report name and get the the Created Date. I would still need your help getting the report name on the column, or, preferably, the date of the file.

Thanks,
Robert

[rgutierrez10]

Oh and for your other questions: Data is for the following morning for the previous day. The report I received this morning shows how data looked yesterday (not sure yet, but any sort of date would be a good start, I can figure out how to treat the dates later)

[dflak]

OK, you still want them by date, is that correct?

I have some code in my archives that does something similar. I'll dig it up and get back to you later in the day.

[rgutierrez10]

Okay, thanks. Yes, I would prefer for it to be done by date.

Small side note (I know I should be posting elsewhere, I also use this script to extract the excel file from the automated email I get from our report server into my MS Outlook mail. Could you tell me what's wrong with it? It only worked the very first day I set up the rule...

Public Sub saveAttachtoDisk(itm As Outlook.MailItem)
Dim objAtt As Outlook.Attachment
Dim saveFolder As String
saveFolder = "H:\Shared\SGX Summary\Finance\Robert G\Alpha List Report\"
For Each objAtt In itm.Attachments
objAtt.SaveAsFile saveFolder & "\" & objAtt.DisplayName
Set objAtt = Nothing
Next
End Sub

[dflak]

Yes, you should be posting the other question either in the Outlook Forum or the Excel Forum.

[dflak]

This is what I came up with. It is a spreadsheet that searches the directory specified for the files of interest and consolidates them into a single workbook. I've used a different approach than in the original model. I use Dir() to get the files and instead of computing the last row, I copy the entire page in.

The syntax for using Dir() is like so:

CODE

FileName = Dir(Path & "\" & FileTemplate)
While FileName <> ""
    Do stuff
    FileName = Dir()
Wend

The first Dir incantation says "go to this path and find files that match the template." So if you enter something like Sample *.xls* it will match Sample A.xls, Sample XYZ.xlsx, Sample .xlszzy and so on. It will ignore files that don't match the pattern.

The second Dir incantation is simply Dir() - it "remembers" the path and pattern from the first incantation.

Dir returns "" when it runs out of files.

I made the spreadsheet so you can enter the Input Directory, Input File Template, Output Directory and Output file name where indicated. These are named ranges that get read into the code, so you don't have to change the code. Just fill in the appropriate values. I have my test values in there now. This spreadsheet can be used to consolidate any conglomeration of files.

There is an instance where the code will fail. That is if you have two files that match the same template that were created on the same date. This will cause the code to attempt to add a duplicate tab name and cause the program to crash. I suppose I could add error trapping and add a warning for when that happens. Just make sure this doesn't happen.

CODE

Option Explicit

Sub MakeReport()
Dim IDir As String, IFile As String             ' Input directory and file "template"
Dim ODir As String, OFile As String             ' Output directory and report name
Dim xlFileIn As Workbook, xlFileOut As Workbook ' Input and output file descriptors
Dim FileName As String                          ' Actual File Name gotten from input directoru
Dim FileDate As Date                            ' Creation Date of the file
Dim TabName As String                           ' Tab Name (date string)

' Initialize Variables
IDir = Range("IDir")
IFile = Range("IFile")
ODir = Range("ODir")
OFile = Range("OFile")

' Silence Alarms and page refreshes
Application.DisplayAlerts = False
Application.ScreenUpdating = False

' Create the report file
Set xlFileOut = Workbooks.Add
xlFileOut.SaveAs FileName:=ODir & "\" & OFile

' Check to see if there are any report files if not, exit the program
FileName = Dir(IDir & "\" & IFile)
If FileName = "" Then
    MsgBox "There are no report files in the directory", vbOKOnly, "No Files found"
    Exit Sub
End If

' Loop through the rest of the matching files in the input directory
While FileName <> ""
    ' Get the file date for the report file
    FileDate = FileDateTime(IDir & "\" & FileName)
    
    ' Open Filename
    Set xlFileIn = Workbooks.Open(IDir & "\" & FileName)
    ' Create the tab name take a day off the file date to get previous day's date.
    TabName = Format(FileDate - 1, "yyyymmdd")
    
    ' Add a new worksheet to the report book
    xlFileOut.Sheets.Add(After:=xlFileOut.Sheets(xlFileOut.Sheets.Count)).Name = TabName
    
    ' Copy the data from the input file and paste it to the output file.
    xlFileIn.Sheets(1).Cells.Copy
        xlFileOut.Sheets(TabName).Range("A1").PasteSpecial xlPasteValues
    
    ' Wait for the copy to complete before trying to go on with the code.
    DoEvents
    
    ' Close the input file
    Workbooks(FileName).Close savechanges:=False
    
    ' Get next file
    FileName = Dir()
Wend

' Each report was added to the end of the report file. So the first page is blank.
' Remove the first page.
xlFileOut.Sheets(1).Delete

' Close the report
Workbooks(OFile).Close savechanges:=True

' Turn updating and alerts back on.
Application.ScreenUpdating = True
Application.DisplayAlerts = False

End Sub

Attached File(s)

 Consolidate_Reports.zip ( 15.08K )Number of downloads: 3

 

[rgutierrez10]

Dan, I aspire to know as much VBA as you do. I digested your script and understood the logic, but the only issue is that it actually separates the reports into separate tabs vs. combining all excel files into ONE sheet, and adding the the FileDate next on each column. I suppose I can use INDEX and MATCH + INDIRECT to look up values from tab names, but I would prefer if the date is added on each of the rows with data:


The emailed version of the excel report always look like this:

A B C D E F G
1 "EMPLOYEE COUNT RPT"
2 X X X X X X
3 X X X X X X
4 X X X X X X
5 X X X X X X
6 X X X X X X
~~~~~~~
I'm wondering if the date value can be entered in all rows G2:G9999. This way, when data from different report dates is pasted into the same worksheet, I can differentiate the source. If this is not possible, I would be satisfied with entering the FileDate only on G2, I can use File and Select Blanks to fill the dates for each report. Any further help will be profoundly appreciated.

[rgutierrez10]

Dan, I aspire to know as much VBA as you do. I digested your script and understood the logic, but the only issue is that it actually separates the reports into separate tabs vs. combining all excel files into ONE sheet, and adding the the FileDate next on each column. I suppose I can use INDEX and MATCH + INDIRECT to look up values from tab names, but I would prefer if the date is added on each of the rows with data:


The emailed version of the excel report always look like this:

__A B C D E F G
1 "EMPLOYEE COUNT RPT"
2 X X X X X X
3 X X X X X X
4 X X X X X X
5 X X X X X X
6 X X X X X X
~~~~~~~
I'm wondering if the date value can be entered in all rows G2:G9999. This way, when data from different report dates is pasted into the same worksheet, I can differentiate the source. If this is not possible, I would be satisfied with entering the FileDate only on G2, I can use File and Select Blanks to fill the dates for each report. Any further help will be profoundly appreciated.

[dflak]

Well, apparently I misunderstood the requirement. However, I think it would be a minor change in that most of the critical pieces are in there. We will have to re-instate finding the last row. I'll get with this when I get a break in the action.

Whenever I work on a project like this, I add it to the library of programs I have.

As for learning VB. Search the web to see how others have done it. Ask questions here and experiment, experiment, experiment . At least in Excel, you have a macro recorder which produces fair code, but it usually gives you the syntax you need.

[dflak]

Here is the latest. There are some changes. On the Control Panel sheet I put in some dates in Columns E to G. This create a date stamp for the output file name in Cell B6. I also put a button to run the macro on the page.

Other than that, it was fairly straightforward. I had to make allowances for the first file copied in. You do want to copy the headers on this file in, while you want to skip the headers on all the rest of the files.

Then there is the matter of the dates. For the rest of the data, I copied in whole blocks of data. For the date, I just filled in the first row associated with that block of data. I fixed this at the end where I use a For Each loop. The cl variable (short for cell) acts like a "pointer." It starts at the beginning of the range (in this case Cell B1) and moves to the next cell when the loop hits next. So cl points to a cell and you can get its contents by using cl.Value and you can "point" to adjoining cells using Offset.

The logic is to go down column B (which I assume has complete data) until I reach the end. For each cl, I look one column to the left (date). If the cell is blank, I fill it in with the date from the cell above it.

I also made the assumption that you have a maximum of 26 columns of data. You can customize this to meet your needs.

Attached File(s)

 Consolidate_Reports2.zip ( 15.08K )Number of downloads: 3

 

[rgutierrez10]

Dan, I may be missing something, but the 2nd version you attached looks exactly like the first one. I am not able to see the button or the dates on column E/F.

I also attached a copy of what the report looks like (names and ID#s have been changed, but everything else is original). I figured this would help.

Attached File(s)

 Book1_sample_alpha_list_report.zip ( 7.06K )Number of downloads: 2

 

[dflak]

Try this version

Attached File(s)

 Consolidate_Reports3.zip ( 19.85K )Number of downloads: 10

 

[dflak]

After seeing the organization of your report, I can see that you will have the change the section where I calculate StartRow.

[rgutierrez10]

Holy c.r.a.p. This works perfectly. I didn't have to change the StartRow. Your macro added the report date on my second row of data. The way the consolidated report is formatted, I can fill in the empty spaces between the dates with the date on top. I've attached a PDF of how the file looked after running your macro. God bless people like you who are willing to help out others like this.

Attached File(s)

 Doc1.pdf ( 102.28K )Number of downloads: 5

 

[dflak]

I'll take all the blessing I can get, but I'm here only because there were a lot of patient people before me who helped me up the learning curve. Glad I could help

[rgutierrez10]

Dan, is there any way to make the date on the first column of the consolidated report "mm dd yy" only (exclude hour and time). I know I could change the format but it does not change the datevalue. I need the datevalue to always be only the date, no extra hours or minutes because it throws off some GETPIVOTDATA formulas I use.

[dflak]

Try replacing this code:

CODE

    ' The first column is the date - fill in the first row only - we'll fix this later.
    If NumFiles = 1 Then
        Osh.Cells(2, 1) = FileDate
    Else
        Osh.Cells(OLastRow, 1) = FileDate
    End If

with this:

CODE

    ' The first column is the date - fill in the first row only - we'll fix this later.
    If NumFiles = 1 Then
        Osh.Cells(2, 1) = int(FileDate)
    Else
        Osh.Cells(OLastRow, 1) = int(FileDate)
    End If