Updating Master Excel Sheet from multiple spreadsheet Dynamically

[excelnubie4help]

Hello, mrExcel! This is my first post, so please be kind. I have some moderate VBA experience. Here is the situation I am seeking advice on.

I am part of a compliance team and I handle the 'automation' part. We are currently in the midst of a User Access Review that is being handled via excel spreadsheets (long story). There is one 'master' spreadsheet that contains all reviews (currently all approval/reject in the column are blank) with over 55,000 entries. Each reviewer will be emailing us back with their approvals/rejections based on the reviews they were responsible for (they filter for their name on the 'approver' column, not really important).

Here is my question:

Is there a way to dynamically create data connections to spreadsheets? The way this would be structured is as each spreadsheet from the reviewers come in (varies per day), they are stored in a cloud folder. Each spreadsheet is in a .xlsx format and the file name is saved with the unique approver's login. EX. (LOGIN.xlsx)

I would like to create a macro that for each approver's spreadsheets in that folder, the macro would find the corresponding entries in the Master sheet, and updates the approval/reject column in the master. I would also need to keep track of which ones are already updated in the master, as not to be redundant.

This is just a loose idea of how I could structure this, but I am definitely open to other suggestions that may better suit my needs.

Is there also a way to have a front end version of the Master? Basically after the macro would update the master, the reviewers need to be able to go onto our cloud and see a view only version with their approval/rejections present.

Should I use a SQL database as the master and excel as my front end? I have been toying with different scenarios and would LOVE some advice.

I tried explaining the situation as best as I can. Thank you for any advice and help!

 

[JLGWhiz]

Hi excelnubie4help, welcome to the Forum.
If your stored .xlsx files are all in the same folder in the cloud and are the only files in the folder, then you could use a fairly standard method of opening the files and querying for the reviewer's input. I would think that a means of controlling the folder so that only current files are found there would be prudent. Or perhaps use folders with the same name but a date suffix, eg "Review_30Nov15". Then you could do your query for updates to the master by date. The method of qaery that I had in mind uses the Dir function and loops through the directory to open each workbook with a .xlsx extension and do whatever needs to be done, then closes each workbook in turn until all workbooks in that folder have been processed. A simple illustration with comments, assuming the code will be hosted by the Master workbook:

Sub updater()
Dim wb As Workbook, sh As Worksheet, fPath As String, fName As String
fPath = "S:\external\xlreports\review_" & Format(#11/30/2015#, "ddmmmyy") & "\"
If Not Right(fPath, 1) = "\" Then fPath = fPath & "\" 'This will add the backslash before the file name if none exists.
fName = Dir(fPath & "*.xlsx") 'Finds only .xlsx files in the folder.
    Do While fName <> ""
        Set wb = Workbooks.Open(fPath & fName)
            For Each sh In wb.Sheets 'If data is to be processed from more than one sheet per file.
                'add code here to do your process
            Next 'If you only have one sheet per file, you can forego the For...Next loop.
            wb.Close False 'assumes no changes to .xlsx file
            fName = Dir 'Gets next file name from Folder
    Loop
End Sub