An alternative view
Excel is very good at popping up dialogs on the console and then hanging until they are actioned by a user. This is a very bad thing on a server, as it will freeze the process and leak running excel instances. It also requires you to have excel installed on the server itself.
Generally you would be far better off scheduling a SSIS job via agent that reads the spreadsheet through an OLEDB driver and then replicating the calculation on a server-side job. What exactly does the macro do?
I've done a few ETL jobs off Excel sources in my day and (IMO) the best way to deal with data off excel is to avoid having to invoke EXCEL.EXE at all costs. It is very finicky about dangling COM references, so you have to be very careful about disposing of all COM objects created. In some cases the default references (Worksheet, Workbook, Range etc.) create opaque references behind the scenes that you cannot actually tidy up programatically as the type library does not expose any facility to do so.
The .NET primary interop assemblies add extra complications to this as they generate their own references that have to be explicitly tidied up as well. There is a significant impedance mismatch between COM and .Net - to the extent that several books have been written on making COM and .Net components play nicely together.
Fortunately WSH doesn't involve .Net, but COM remoting on Excel COM servers is not something I would recommend doing from within a DBMS.
Two much safer approaches
Open the workbook in the OLEDB driver - read the sheets into a staging table and then extract the data form there. This doesn't even need Excel to be installed on the server, and is quite robust.
Unravel the .xlsx zip file and get out the worksheets from that - this actually works better than you would think. The sheetxx.xml files are in a fairly straightforward format and the only other thing you are likely to need is sharedStrings.xml. Generally you shouldn't need to do this with SQL Server if you have SSIS available, but it is quite a useful trick if you are working with (say) Oracle on a non-Windows host.
EDIT:
In order to use Excel through OLE automation you need to have Excel installed on the machine you're running it on. Generally it's not a great plan to have Excel installed on a server as it's not particularly secure. It's also a desktop tool and has a tendency to leak COM references and running Excel instances if you don't dot your I's and cross your T's with your COM reference creation and disposal.
SSIS has an excel data source. You can see it by creating a SSIS project in BIDS and creating a new connection manager. One of your options will be Excel.
However, If you need to query a sharepoint list you're better off querying it programatically without using Excel at all. A little google-fu should turn up some examples of how to do this, e.g. here.. You can do this through a standalone .Net app or through a script task in the SSIS package (script tasks are .Net custom tasks that you can build within a SSIS package).
If you do this you're probably better off developing it outside SSIS (use Visual C# Express if you don't have any other option) and then porting it across to the script task. If you're familiar with Python, IronPython or Boo are great tools for frigging with .Net APIs interactively to get something working.
