Automate Excel to update a list of spreadsheets that links to another spreadsheet (the spreadsheets are accessible through webdav links that are protected by ssl and user authentication)
$30-5000 USD
Completed
Posted over 11 years ago
$30-5000 USD
Paid on delivery
I want to automate the task of updating several spreadsheets that needs updating when a master spreadsheet is updated/replaced. I'm using Office 2010 on Windows 7 but I would like the service to be able to run on windows server 2008.
The master spreadsheet is located on a network drive and could be either xlsx or xls. The spreadsheets to be updated are of type xls and are located on a server application and accessed through webdav (https) and secured with authentication.
## Deliverables
This is the manual process that I want made automatic:
* When I replace or update the master spreadsheet on the network mounted drive:
* I open the "webdav" spreadsheet one by one, either by choosing Edit in Internet Explorer (uses Sharepoint activex component) or I open them from the recent list in excel.
* Excel opens a dialog asking for username and password that I fill out
* When the spreadsheet is loaded, I enable it for editing and for updating links. That makes excel to update the cells based on the content of the master spreadsheet.
* I save and close the spreadsheet.
Specification:
I want a service that detects when the master file it is replaced or updated. When the service detects a change in the master file, it opens each "webdav" excel document specified in a list, authenticate, enable updating of links and saves it. When it is done, it should email me that it has successfully updated the files.
The email should list every "webdav" file that has been updated correctly
If an error occurs, I want the email to say which "webdav" file was not updated and why.
Potential errors that I can foresee now:
-Not able to edit the excel file (file is locked)
-Not able to authenticate with that username/password
-Not able to update external links (if the network drive is not visible or the file has changed name)
-Not able to save the file
-Excel is not present
-Not able to open Excel
-Any other error
The subject of the email should be different when there is an error than when all updates are successful (, so that I have the possibility to create a filter on the email.)
It should use a configuration file that is easy to maintain like ".ini" or yaml that specifies the master file, the list of "webdav" excel files, the credentials (username/password) to use and the email address and any other necessary configuration.
The service should be registered so it starts automatically when the computer is restarted.
The service should be easy to install on a new computer
I want the source code as part of the deliverable.