In reference to vbScript opens up excel but doesn't load macro/modules?
Now I tried to run my vbscript from SQL Job. Though I get Job executed successfully, it didn't open up my excel file(Checked Task Scheduler). I went to view the history for this job and found
Message Executed as user: GSOPS4\SYSTEM. Microsoft (R) Windows Script Host Version 5.7 Copyright (C) Microsoft Corporation. All rights reserved.C:\WINDOWS\system32\TestTOPTMay307.vbs(5, 1) Microsoft Excel: Microsoft Excel cannot access the file '\gsops4\data_extracts\TestTOPTMay307.xlsm'. There are several possible reasons: The file name or path does not exist. The file is being used by another program. The workbook you are trying to save has the same name as a currently open workbook. Process Exit Code 0. The step succeeded.
I also followed the suggestions in this thread Run a C# Console Application from SQL Server Agent (Job)?
However I couldn't get it to work. This thing has been annoying me for past 48 hours. Any clue to what's causing it and how do I successfully execute my job( run my vbscript file from SQL) will be highly appreciated. Thanks all in adv!
Any thoughts?
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.