3

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

Joel Coehoorn
  • 399,467
  • 113
  • 570
  • 794
VA.
  • 33
  • 1
  • 1
  • 3
  • so you just need vb script that will open xls file, refresh it & close it for email purpose? – Amol Chavan Mar 21 '12 at 08:51
  • FYI, Microsoft currently recommends that you do not automate Office applications in a server environment: http://support.microsoft.com/kb/257757 Can you create the file from scratch and then email it? http://stackoverflow.com/questions/151005/create-excel-xls-and-xlsx-file-from-c-sharp – Pondlife Mar 21 '12 at 09: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 Mar 21 '12 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. Mar 21 '12 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. Mar 21 '12 at 13:18
  • 4MO1, that's exactly right, just open the file, refresh and save for further emailing. – VA. Mar 21 '12 at 13:19

4 Answers4

8

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
Amol Chavan
  • 3,835
  • 1
  • 21
  • 32
  • @user1281185 : If this is what you needed ,then you can accept this as answer by clicking on check-mark presnt at left side – Amol Chavan Mar 22 '12 at 04:07
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

SaSH_17
  • 405
  • 5
  • 15
0

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.

gusmundo
  • 1
  • 2
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
Gary
  • 3,254
  • 2
  • 27
  • 30