I am trying to streamline the production of a document that has to be created on a regular basis. There are tasks associated with detailed instructions on how to complete them, and each of these has a specific ID number that is reused across various projects. I sucked up as many of these task IDs as I could into an access database.
For each project, somebody else pulls a list of all of the required tasks from another database that I cannot access and puts them into an Excel workbook with a standard format. For each project, I need to:
- Pull in all relevant tasks by ID number
- Compare them to the standard list in my database which has the steps to complete the task and some other info
- Generate a document with a specific format using the specific information about the tasks
Writing a macro in excel seemed like a lot of work, but the best bet, but I can't be sure that the access database will always stay in the same place that it belongs. Since the database is the consistent item across all projects, I thought about writing a macro in Access and having a dialog asking where the spreadsheet is would work, but using a table to compare the two lists seemed more complex than simply having a comparison sheet generated within excel. I didn't really consider writing a macro within Word, though that is a possibility too.
I am trying to find the most logical way to create this setup. Using 3 different office programs seems fairly convoluted, but I am limited in the software that I have available to accomplish this:
- Office 2007
- Beyond Compare
- The other programs installed (I can't install additional software) don't seem remotely relevant to the task
Does anyone have any idea how to go about this?