Refresh Excel using SSIS script task

Question

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

so you just need vb script that will open xls file, refresh it & close it for email purpose? — Amol Chavan
– Amol Chavan, Commented Mar 21, 2012 at 8:51
FYI, Microsoft currently recommends that you do not automate Office applications in a server environment: support.microsoft.com/kb/257757 Can you create the file from scratch and then email it? stackoverflow.com/questions/151005/… — Pondlife
– Pondlife, Commented Mar 21, 2012 at 9:53
why even bother with SSIS? You can just add a startup macro to the excel file that refreshes the data upon opening the file. Just distribute the file to your audience and everytime they open it, the macro will automagically refresh the data...assuming they have access to the network. — Bill Anton
– Bill Anton, Commented Mar 21, 2012 at 12:20
Pondlife, thanks for your comment. I was actually creating the file from scratch when it was just a bunch of data, but now the client wants formatted excel with filters and pivots and color coding. Now my file has raw data with filters on one worksheet and 2 pivots on 2 other sheets. By linking the 3 sheets separately to the database table I can easily refresh all data and preserve formatting. All I need to do at this point is to have a script do what I do manually now, namely hit 'Refresh All' button and save the file. — VA.
– VA., Commented Mar 21, 2012 at 13:17
iPolvo, no, the clients are outside of our network and will not be able to refresh the file on their own. thanks for your suggestion though! — VA.
– VA., Commented Mar 21, 2012 at 13:18

Amol Chavan · Accepted Answer · 2012-03-21 18:32:18Z

9

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

edited Mar 21, 2012 at 18:32

answered Mar 21, 2012 at 18:05

Amol Chavan

4,0201 gold badge23 silver badges33 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Amol Chavan Over a year ago

@user1281185 : If this is what you needed ,then you can accept this as answer by clicking on check-mark presnt at left side

MOT Over a year ago

I have been using the above code as part of a SSIS package. It has suddenly stopped refreshing. Any reason why this may be the case?

gusmundo · Accepted Answer · 2017-05-24 06:44:57Z

1

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.

answered May 24, 2017 at 6:44

gusmundo

93 bronze badges

Comments

SaSH_17 · Accepted Answer · 2019-01-08 13:24:23Z

1

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

answered Jan 8, 2019 at 13:24

SaSH_17

4455 silver badges16 bronze badges

Comments

Gary · Accepted Answer · 2018-10-03 13:58:50Z

0

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

answered Oct 3, 2018 at 13:58

Gary

3,3142 gold badges30 silver badges30 bronze badges

Comments

philipvago · Accepted Answer · 2024-11-05 18:47:01Z

Need to add this to the imports section of SSIS:

Imports Microsoft.Office.Interop.Excel

'SSIS didn;t like SET commands, thus we updated to remove them...

' Create an Excel instance
   Dim oExcel
   Dim oWorkbook ' added this
   oExcel = CreateObject("Excel.Application") 

 ' Disable Excel UI elements
   oExcel.Visible = True
   oExcel.DisplayAlerts = False
   oExcel.AskToUpdateLinks = False
   oExcel.AlertBeforeOverwriting = False

   oWorkbook = oExcel.Workbooks.Open("absolute path to your file")
   oWorkbook.EnableConnections = true ' added!
   oWorkbook.RefreshAll
   oWorkbook.Save

   oExcel.Quit
   oWorkbook = Nothing
   oExcel = Nothing

Collectives™ on Stack Overflow

Refresh Excel using SSIS script task

5 Answers 5

2 Comments

Comments

Comments

Comments

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

2 Comments

Comments

Comments

Comments

Comments

Linked

Related