DTS to Excel Calculation Problem

[VBorDeliver]

I have setup a simple DTS package that exports data from SQL to Excel which all works fine. This runs every night creating a new spreadsheet each time from a template.

The problem is that I have other excel sheets that contain formulas that link to this data. If I open one of the linked sheets without manually opening and saving the spreadsheet that contains the exported data then excel gives the error that the data in this sheet has not been recalculated and I have to open the spreadhseet and save.

If however I open and close the spreadsheet containing the exported data first the other spreadsheets are fine.

I do not really want to keep opening and closing the export spreadsheet every morning - has anyone come accross this before or have a solution?

 

[Kalpha7]

Hi,
You can use an ActiveX task in your DTS package and include the "RefreshAll" & "SAVE" methods for the Excel Spreadsheet. This will refresh the data in your Excel target.
For example:

Function Main()

dim xlapp, xlbooks,xlsheet,SheetName
set xlapp = createobject("Excel.Application")
set xlbooks = getobject("<FULL UNC(That's path & filename)OF EXCEL FILE>")
SheetName = "<SHEET NAME>"
set xlsheet = xlbooks.worksheets(Sheetname)
xlbooks.RefreshAll
xlbooks.Save
xlapp.quit

Main = DTSTaskExecResult_Success
End Function


--------------------------------------------------------

Good Luck