Automated Comparison Procedure

[ScottXJ]

Hi,

Recently I received excellent info from Zathras and some other members on developing a comparsion procedure to compare two Excel spreadsheets and exporting the exceptions to a new spreadsheet.

I would like to automate this process and to do so, I would like to perform the following (I think this is the best approach but any comments/suggestions would be welcome):

1. Export Access query results to an Excel spreadsheet (I know how to do this).
2. Run procedure to reformat the spreadsheet (I have an Excel macro that I have recorded to do this). I would also like to be able to select another spreadsheet for reformatting using a directory window (like Windows Explorer).
3. Run comparison procedure to perform comparison of selected spreadsheets. Again, some way of selecting spreadsheets from a directory window would be required to allow me to select the newly formatted spreadsheets for comparison.

4. It would be nice if this entire process could be invoked from a command button.

Any help would be greatly appreciated. I can email or post any code I do have if required.

Sorry for the long post!

Thanks,
Scott.

 

[TonyJollans]

Hi Scott,

You seem to have nearly everything you need. You can use Application.GetOpenFilename to pull a file name from the user. It invokes the File Open Dialog but passes the name back to you instead of actually opening the file.

If you add a button to your worksheet (using the Forms toolbar) you can then attach your code to it (from memory it pops up asking you what code to run when you add it).

Enjoy,
Tony

 

[ScottXJ]

Tony,

Thanks for your input. I'll give it a try and let you know what happens.

Scott.