How to Refresh Pivot table using Office 2010 driver

• simon phoenix-479217

  SSCommitted

  Points: 1573

  More actions

  June 13, 2011 at 3:48 pm

  #99395

  Hello

  All,

  I have refreshing the pivot table using script task inside the SSIS package and it worked great. But in my new server they are not installing Office application and all that will be availiable is drivers. How do i get my pivot table refreshed using script? Script that i am using at the moment with office application.

  Public Sub Main()

  Dim objXLS_Application As Object

  Dim objXLS_Workbook As Object

  Dim s_outputfilename As String

  objXLS_Application = CreateObject("Excel.Application")

  s_outputfilename= "\\C:\Test.xlsm"

  objXLS_Workbook = objXLS_Application.Workbooks.Open(s_outputfilename)

  Call objXLS_Application.Run("'" & s_outputfilename & "'" & "!MacroRefreshAll")

  Call objXLS_Workbook.Save()

  Call objXLS_Workbook.close()

  objXLS_Workbook = Nothing

  Call objXLS_Application.Quit()

  objXLS_Application = Nothing

  Dts.TaskResult = ScriptResults.Success

  End Sub

• Welsh Corgi

  SSC Guru

  Points: 116520

  More actions

  June 19, 2011 at 1:46 pm

  #1340896

  You may want to try the last post on the following thread:

  http://social.msdn.microsoft.com/forums/en-US/sqlintegrationservices/thread/8b52557b-cd13-4db6-87b6-230b7e051a9e/

  For better, quicker answers on T-SQL questions, click on the following...
  http://www.sqlservercentral.com/articles/Best+Practices/61537/

  For better answers on performance questions, click on the following...
  http://www.sqlservercentral.com/articles/SQLServerCentral/66909/