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

回答1:

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

回答2:

Old post but 4M01 answer has helped me out heaps.

In my case I had to put a sleep just after opening the workbook to ensure the file loads correctly.

i.e.

oWorkbook = oExcel.Workbooks.Open("absolute path to your file")
Threading.Thread.Sleep(3000)
oWorkbook.RefreshAll

Note also in VS 2015 set is no longer required. so just remove "set " for the code to work.

回答3:

This may not exactly fit your needs, but if it helps you or someone I was able to just use a simple

Execute Process Task

with the

• Executable as /..path../Excel.exe and the
• Arguments as the desired file (full path) to be opened

回答4:

In my case (Connection to MS SQL DB), I had to uncheck the "enable background refresh" option for working fine.

Excel: Data > Connections > Properties > (uncheck) enable background refresh