excelvbscriptssisrefresh

Refresh Excel using SSIS script task


I have an Excel file that gets external data from database table. I need to refresh the file automatically and email it. I intend to use SSIS script task to run some VB script that would open the file, refresh data, save and close (obviously without bringing up the application). then I'll use email task to send the file by email. All I need is the script that refreshes the file and being total noob in VB or C# I have to ask if anyone has a script that does that lying around and which I could customize for my file and use in my script task. I'll appreciate any hints! thanks a lot, Vlad


Solution

  • Hope this is what you looking for

     ' Create an Excel instance
       Dim oExcel
       Set oExcel = CreateObject("Excel.Application") 
    
     ' Disable Excel UI elements
       oExcel.Visible = True
       oExcel.DisplayAlerts = False
       oExcel.AskToUpdateLinks = False
       oExcel.AlertBeforeOverwriting = False
    
       Set oWorkbook = oExcel.Workbooks.Open("absolute path to your file")
       oWorkbook.RefreshAll
       oWorkbook.Save
    
       oExcel.Quit
       Set oWorkbook = Nothing
       Set oExcel = Nothing