open and save an excel 2003 sheet every ten minutes

Question

I am using excel 2003. My requirement is to open and save an excel sheet every ten minutes.

My Sheet uses a ODBC connection to get data and it refreshes every time when it opens.

I created a scheduled task to run a bat file which calls a VB script which will open the excel sheet and save it before closing it.

Bat file

C:\SaveExcel.vbs C:\MySheet.xls
Exit

VB Script:

    Dim args, objExcel
    Set args = WScript.Arguments
    Set objExcel = CreateObject("Excel.Application")
    objExcel.Workbooks.Open args(0)
    objExcel.Visible = True
    objExcel.Workbooks.Open "C:\Program Files\Microsoft Office\OFFICE11\Library\Analysis\FUNCRES.XLA"
    objExcel.Addins("Analysis Toolpak").Installed = True
    objExcel.Addins("Analysis ToolPak - VBA").Installed = True

    objExcel.ActiveWorkbook.RefreshAll
    WScript.Sleep 10000  ' Waiting  as refresh may take upto ten seconds
    objExcel.ActiveWorkbook.Save
    objExcel.ActiveWorkbook.Close(0)
    objExcel.Quit

The problem with the above is the addin "Analysis Toolpak' is not loaded ,hence most of the formulas are not working when i open the sheet using above VB.

The addins works if i open via CMD.

CMD>"C:\Program Files\Microsoft Office\OFFICE11\EXCEL.EXE" "C:\MySheet.xls"

I never woked with VB , is there a way to merge the cmd and vb to meet my requirement?

Thanks

Answer 1

Try loading the Analysis ToolPak - VBA also. That could cause the problems you see...

AddIns("Analysis ToolPak - VBA").Installed = True