Automate MS Excel / MS Word Document

Skills required:MS Suite: Excel, Word, Access (The current process uses only Excel and Word but I am open for any option. Load batched rec into an Excel spreadsheet which is used to create an individual Word doc using cells/groups of cells from the Excel doc. You must also be able to transfer results of these cells into predetermined locations on a Word Document. The final version of the document must be in PDF format. I have completed a Version of this process but it requires a manual - one rec at a time) feed into Excel which automatically populates the MS Word Document with pre-selected information. I am trying to automate this process. I want to be able to do the following: 1. Input a file of 1 to many recs 2. Create 1 - many MS word docs based on no of records). 3. Auto create a PDF version of the document 4. Load PDF doc to a predetermined folder on my hard drive. A step by step detailed doc is avaialble with requirements. ## Deliverables **Current Process** ** ** **We are currently using MS Excel to create calculations, Bubble and Bar Charts** **We are using MS Word for the Personal Skills Gap Assessment (PSGA)** ** ** **Step 1: Customer Takes an automated Survey (No changes required) ??" This should not be part of your bid.** o A customer completes a Personal Assessment Survey using “Survey Gizmo??. This survey consists of 200 questions. o When the customer is finished they hit a “submit?? button. o Survey Gizmo will create a single record (in CSV format) which contain person demographics for that customer and their individual answers. There are 218 columns for each customer record on the CSV file. **Step 2: Process Survey Gizmo Customer List - This should not be part of your bid.** o *For the following example: 40 individual customer surveys were taken on February 10, 2009. Keep in mind on any given day there may be zero to many surveys completed.* o Each morning I download all of the previous day’s surveys from Survey Gizmo. The file format that Survey Gizmo provides is a CSV file. There are 40 records on this file for this particular day. Remember each record represents one unique customer survey. We will then create 40 unique Personal Skill Gap Assessments in the following steps. o I will then take the downloaded CSV file containing the 40 records and move them (Copy/Paste) to the ALLDATA worksheet. o This worksheet contains a history of ALL customers that have completed surveys to date. If there were 100 customers that had taken surveys before today I will add the list of 40 to this worksheet. We now have 140 individual rows of data. We are set up to accept up to 50,000 rows of customer surveys. o Survey Gizmo will create a single record (in CSV format) which contain personal demographics for that customer and their individual answers. There are 218 columns for each customer record on the CSV file. o Excel then calculates averages for the current list of customers (currently 140 rows). These averages will be used later in the process. A single row will contain these averages. There are questions on the survey that require text based answers. These questions (columns) are not averaged. ** ** ** ** ** ** **The remaining steps should be part of your bid. This is where we need automation** ** ** **Step 3: Create individual PSGA reports for each of the 40 customers ** o The next step is to copy one of the 40 rows that were downloaded on February 10, 2009 and paste it to a specified row on the “INDScores?? worksheet. When I move each customer survey to the specified row the following occurs: § Worksheet values on the “INDScores?? worksheet are re-calculated based on the data on that customer record and the averages calculated on the “ALLDATA?? worksheet described above. § These re-calculated fields on the “INDScores?? worksheet are then fed into “INDChart?? worksheet. This worksheet contains each of the 30 bubble charts. 6 bar charts and other data that is transferred into the PSGA worksheet. Remember, only one PSGA worksheet is created at a time (one for each of the 40 customer survey records downloaded on that day). Also remember that the results of the bubble and bar charts are based on only 1 single customer survey records and an average of all customer surveys taken to date. § The PSGA has been created to automatically update itself based on the values in the Excel worksheet. § Objects are updated in the PSGA Word Document. An object can be a single data item (i.e. first and last name) from the individual customer survey record, it can be one of 30 Bubble Charts found on the worksheet, or can be one of 6 Bar Charts found on the worksheet. Each object has been strategically placed on the PSGA Word Document. § After the PSGA is automatically updated, I then create a PDF version of the document. § The PSGA document and a folder containing bonus material (more pdf material) is loaded to a folder. A new folder is created on a daily basis to hold all of the customers PSGAs for the day. The format is: MMDDYYYY_HHMMSS (current date and time folder created) § The individual customer folder that is under the daily folder is in the following format: MMDDYYYY_HHMMSS_Customer Name (these come from the customer record. Within the folder are the individual PSGA and the folder containing the bonus material. The bonus material folder and its parts can all be the same name. **Step 4: Customer Material Packaged and Sent - This should not be part of your bid.** o I then move the PSGA document to a flash drive. Also on the flash drive is a folder containing bonuses held in a separate folder. (The bonuses are the same for every customer.) o A label is created for the customer and the flash drive is packaged, addressed and sent to the customer. Steps 3 and 4 are created 40 times (one per customer survey). Each survey currently takes anywhere from 10 ??" 15 minutes per customer. This is what we would like to automate and for you to bid on: My vision is for the following to occur each morning Ø I will continue to move the CSV file from Survey Gizmo (the 40 records) to the “ALLDATA?? worksheet, however… Ø These 40 records are then batched so that the process of moving individual records to the “??INDScores?? worksheet is automated o Calculations by record are created o The PSGA document is created in PDF format (the format, and look & feel are critical). As long as the format/look & feel is good, I don’t care if the PSGA is created in Word or other format prior to creating the PDF version. o The PSGA and the bonus material are automatically loaded in the properly formatted designated folders. This automation should create the folders as well as load them I would prefer not to single feed the records but if that is all you can do then please bid appropriately. Here is a quick flow of what I envision: The boxes with double lines are my responsibility Final Note: I will be happy to provide you with a sample of the current spreadsheet and PSGA document for your review. I will also be glad to meet with you individually if you need help understanding my requirements. Prior to selecting a winner it will be necessary to discuss so I am sure you understand exactly what we are looking for. We can do a simple current process walkthrough to get a better idea of how the design flow is happening now, and then to see how the automation process will be built. * * *This broadcast message was sent to all bidders on Sunday Feb 15, 2009 12:12:23 AM: I am sending this out to all bidders: I have attached the files as requested to my original request. Please get them there. If you have any trouble finding or reading them let me know. The zip file contains the following: - Excel file with calculations - Two word documents containing part 1 and part two (summary) Assessments. Note: I would like to combine these into one single PDF file. I had to create two docs because of space limitations. - The requirements. I understand that some of the detail was not sent across accurately. Please include what tools will be required to run your solution on my personal desktop. For example, I currently do not own MS Access or other database. I want to let everyone know that I have already seen several bids for this project. When you make your bid please give me your best price. I will also consider the amount of time it will take you to complete the work. Thanks, Gary DiscoverHBB