Auto populate the newest data into a master spreadsheet

[Admaine123]

Hi Everyone,

I'm hoping you can help.

I receive a weekly report which I move into a folder, the name for the report will be different each time. What I'm aiming to achieve is to auto populate the data from the most up to date report into a master spreadsheet.

I was wondering if the lookup per se, could be based on the Folder name where all reports are dumped.

So as an example:

Within the folder there is 128723762.xls, 1236765028.xls and 4968603.xls, now when 68454934.xls (name will be different each time) is dumped into this location the data it contains will be added to the master spreadsheet that contains the data from the first 3 spreadsheets. Or a trigger from the master is run to pull it, whichever way works best.
Then the following week, another report will be dropped in to the folder (randomly numbered), so the data from there is to then be added to the master.

Hopefully I've explained it well enough.

Please let me know if you have any questions and I can assist.

Thanks in advance,

A

 

[LongToast]

Just my thoughts, so this will run automatically by pressing 1 button :

1. Create vba procedure, and in that procedure..
2. Loop files (blablabla.xls) in original folder.
2. Copy all data from blablabla.xls to master.xls.
3. Create archive folder automatically in some place and use date as name of that folder.
4. Cut blablabla.xls file (or save as) to the archive folder that you just create (if needed rename that blablabla.xls file, use date as file name too, so you can easily track them later if you need).
5. Delete automatically blablabla.xls from original folder (if you use save as, not cut), why you need to delete them from original folder? because the next week probably you will copy the same blablabla.xls to master.xls that's why you need to keep original folder clean.
6. Done.

and you can start with this link. hope it helps. regard.

 

[Admaine123]

Just my thoughts, so this will run automatically by pressing 1 button :

1. Create vba procedure, and in that procedure..
2. Loop files (blablabla.xls) in original folder.
2. Copy all data from blablabla.xls to master.xls.
3. Create archive folder automatically in some place and use date as name of that folder.
4. Cut blablabla.xls file (or save as) to the archive folder that you just create (if needed rename that blablabla.xls file, use date as file name too, so you can easily track them later if you need).
5. Delete automatically blablabla.xls from original folder (if you use save as, not cut), why you need to delete them from original folder? because the next week probably you will copy the same blablabla.xls to master.xls that's why you need to keep original folder clean.
6. Done.

and you can start with this link. hope it helps. regard.

Hey,

Many thanks for your reply, that's pretty much what I was after however, I don't necessarily need an archive folder to move the old reports to as the names of these files are never the same. Will this effect the process you mention above?

Thanks again.

 

[LongToast]

Hey,

Many thanks for your reply, that's pretty much what I was after however, I don't necessarily need an archive folder to move the old reports to as the names of these files are never the same. Will this effect the process you mention above?

Thanks again.

i think it's better if you use archive folder.
1. Looping process through files will read all files in original folder, if that file still in that folder after you copy it to master.xls, next week you will automatically copy it again (that become duplicate data in master.xls right?), except you do manual delete to that file.
2. For tracking purpose, if someday you need to track some data you have been copy, you only have to find out what date that data has been transferred to you, then you can find that original file in archive folder with date as name, easy right?

but it's your choice, i just give a thought, can be right can be wrong :D

 

[Admaine123]

Hey, yes I can try the archive then for sure. If I needed to say copy the contents columns A - H and from row 10 down and paste it in to the master underneath previous data, how would I go about doing that part?

Thanks in advance

 

[Admaine123]

i think it's better if you use archive folder.
1. Looping process through files will read all files in original folder, if that file still in that folder after you copy it to master.xls, next week you will automatically copy it again (that become duplicate data in master.xls right?), except you do manual delete to that file.
2. For tracking purpose, if someday you need to track some data you have been copy, you only have to find out what date that data has been transferred to you, then you can find that original file in archive folder with date as name, easy right?

but it's your choice, i just give a thought, can be right can be wrong :D

Hey, yes I can try the archive then for sure. If I needed to say copy the contents columns A - H and from row 10 down and paste it in to the master underneath previous data, how would I go about doing that part?

Thanks in advance

 

[LongToast]

This is NOT the best method in the world but this would do the trick for you.

1. Create 2 folder and 1 excel file (Master.xlsm), like this :

2. In Child Folder, looks like this :

3. Go to Master.xlsm, on developer tab, insert activex button, i named it "Copy Data From Childs"

4. In Master.xlsm, open VBA editor and put this code :

Option Explicit

Private Sub CommandButton1_Click()
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
    
    Dim wbMaster As Workbook: Set wbMaster = ThisWorkbook
    Dim wsMaster As Worksheet: Set wsMaster = wbMaster.Worksheets("Master Sheet")
    Dim childFolder As String
    Dim archiveFolder As String
    Dim objFSO As Object
    Dim objFolder As Object
    Dim xlFile As Object
    
    Dim masterLastRow As Long
    Dim childLastRow As Long
    
    childFolder = wbMaster.Path & Application.PathSeparator & "Child Folder"
    archiveFolder = wbMaster.Path & Application.PathSeparator & "Archive Folder"

    Set objFSO = CreateObject("Scripting.FileSystemObject")
    Set objFolder = objFSO.getfolder(childFolder)
    
    For Each xlFile In objFolder.Files
        If Right(xlFile, 4) = "xlsx" Or Right(xlFile, 3) = "xls" Then
        
            With Workbooks
                masterLastRow = wsMaster.Range("A" & Rows.Count).End(xlUp).Row
                .Open xlFile
                childLastRow = .Application.Worksheets("Child Sheet").Range("A" & Rows.Count).End(xlUp).Row
                .Application.Range("A1:B" & childLastRow).Copy wsMaster.Range("A" & masterLastRow + 1)
            End With
            
            objFSO.CopyFile xlFile, archiveFolder & Application.PathSeparator & Format(Now, "dd mmm yyyy") & "-" & xlFile.Name, True
                
            Workbooks(xlFile.Name).Close savechanges:=False
            
            Kill xlFile
        End If
    Next xlFile
    
    Set wsMaster = Nothing
    Set wbMaster = Nothing
    Set xlFile = Nothing
    Set objFolder = Nothing
    Set objFSO = Nothing
    
    Application.DisplayAlerts = True
    Application.ScreenUpdating = True
End Sub

5. In this line :

.Application.Range("A1:B" & childLastRow).Copy wsMaster.Range("A" & masterLastRow + 1)

change to your need e.g. Column A-H Row 10 down :

"A10:H" & childLastRow

and if you need to paste it in specific column in Master sheet, change this line

"A" & masterLastRow + 1

to column you want (i use column A as starter to paste).

hope it helps