I have no knowledge on computer programming and I need a bit of help. I'm using automate.io (a drag and drop integration software) to take a new row in excel and insert it into salesforce. That bit works all ok.
What I worry about is my excel document it is connected to an SQL server and auto refreshes every minute. The problem is that I have to have the Excel document open at all times for this to auto refresh to take place.
To combat this I used task scheduler to open the document at 7am even when there is no one logged in.
My question is, will this work and is it reliable?
Only your testing can answer that.
Watch out for false positives, e.g. new record in database not picked up or not refreshed, and therefore not input to SalesForce in a timely manner.
Here are some ways to achieve what you want, in approximate descending order of reliability:
Get a third party to integrate the two databases directly (SalesForce and your SQL server), with updates triggered by any change in the data in your SQL server. There is a whole sub-industry of SalesForce integration businesses and individuals who would consider taking this on in return for money.
Get a standalone script (not Excel) running on a server near your database to monitor your DB for changes, and push new records to SalesForce via a direct API.
Get a standalone script (not Excel) running on a server near your database to monitor your DB for changes, and push new records to text files (not Excel) which are subsequently loaded into SalesForce.
Get Excel to refresh your DB for changes regularly via a data link (i.e. what you outlined), but have it new records to text files (not Excel) which are subsequently loaded into SalesForce.
Get Excel to refresh your DB for changes regularly via a data link (i.e. what you outlined), and have it push new records to SalesForce via third-party software as a substitute for actual integration.
You will notice your proposed solution is at or near the end. The list may inspire you to determine what tweaks you might make to the process to move up the list a little ways, without changing the approach completely (unless you want to and can justify it).
Any tweak that removes a link in the dependency chain helps reliability. Shorter chains are generally more reliable. Right now your chain sounds something like: Database > Server?/internet?/network? > Excel data link > Excel file > Task scheduler > internet > automate.io > API > Force.com > SalesForce.
Transaction volume, mission criticality, and other subjective criteria will help guide you as to what is most appropriate in your situation.