Macro to lookup another workbook if present in folder

**kingofbeans** · 05-11-2013, 07:17 AM

Hi,

I would really appreciate someones help with a macro that is beyond my capabilities.

I have a spreadsheet I use to keep track information on clients. The clients provide information on our website, which can generate reports which tell me when/if they have provided information.

I would like to integrate this into my main tracker every so often.

I could do a vlookup each time but this would be time consuming and too simplistic.

I would therefore like a macro to lookup the website report IF the website report (a spreadsheet with random name) is in a 'lookup report' folder. It can lookup based on unique ID present in both spreadsheets. Once it has looked up values and copied into the main sheet, I would like it to move the report into a processed folder (renamed to the date it was moved), so that it isnt reprocessed later. This is the main part of my request.

Ultimately I would like it to be a bit more complex, but if you are able to help with the above initially, that would be great! The complexity I would like is - as it will overwrite existing data each time a new report is put in the report folder, i would like it to highlight the cells which have been updated (i.e. have different information than was in them originally) and put the old value (if not blank) in a comment for that cell.

I have attached a zip file with example 'Main' and 'report' spreadsheets, and the folder structure I would like to use.

Even if you cant do all of this, any tips / similar example would be greatly appreciated!

**p24leclerc** · 05-11-2013, 01:52 PM

Can you be more specific?
You have the Main.xlsx workbook which is probably the place you keep informations up to date. Right?

You then have a folder named Lookup Report where there will be only ONE workbook created by your Web site. Right?

Which information do you want to compare? There are something like 20 columns in your Main workbook.

Moving the report in the Processed folder should not be a problem.

About highlighting modified information, I assume it is in your Main workbook. Right?
Copying old information to a comment is something I've done before. Do you want to keep track of only the last change or do you want the comment to hold all previous data?
Regards

**kingofbeans** · 05-11-2013, 04:14 PM

Hi,

Everything is as you say. Main is the main tracker which should be updated by the one spreadsheet in the lookup report folder.

The columns which should should be updated in the main spreadsheet are the columns with text headings which are the same in main and reports.

The main sheet is empty now... But if there was a different date for an individual in reports compared with main, I need main to update for the new value from reports. In that updated cell should then show the old value in a comment.

Does this make sense?

The last thing is that once the main spreadsheet has been updated for the report spreadsheet (which represents) the website report; the 'processed' report should be moved to the processed folder (to prevent it being looked up again on open).

**p24leclerc** · 05-11-2013, 08:00 PM

Put the following macro into a module in your Main workbook. Do not forget to save this workbook under XLSM format to accept macros.

You'll have to change the paths in some of the lines of code to refer to your specific folders' location.

Please Login or Register  to view this content.

The macro is programmed so that it will erase the comments and the highlight of cells that have the same content at the moment of check.

One thing I tought of is the possibility for the WEB file to have new ID not present in your main workbook. If this is possible, then we'll have to modifiy this macro to include this feature.
Hope this helps

**p24leclerc** · 05-11-2013, 08:03 PM

Put the following macro into a module in your Main workbook. Do not forget to save this workbook under XLSM format to accept macros.

You'll have to change the paths in some of the lines of code to refer to your specific folders' location.

Please Login or Register  to view this content.

The macro is programmed so that it will erase the comments and the highlight of cells that have the same content at the moment of check.

One thing I tought of is the possibility for the WEB file to have new ID not present in your main workbook. If this is possible, then we'll have to modifiy this macro to include this feature.
Hope this helps

**kingofbeans** · 05-12-2013, 05:36 AM

Pierre,

Thank you so much! This really is excellent and works amazingly well!

Re your comment on IDs being present in the report but not the main spreadsheet, I had considered this but think it is quite unlikely. (The website is populated based on the entries in the main sheet). I guess that it is possible, so if we could have something in the macro to deal with this, that would be brilliant! Because this is unlikely, the way I would like it to deal with this case is to show the unique IDs which are in reports (but not main) in a message box.

Thanks again for your help! Wish there was a star x 100 button for this!