使用SSIS脚本任务刷新Excel [英] Refresh Excel using SSIS script task

问题描述

我有一个Excel文件，该文件从数据库表中获取外部数据.我需要自动刷新文件并通过电子邮件发送.我打算使用SSIS脚本任务来运行一些VB脚本，该脚本将打开文件，刷新数据，保存并关闭(显然不启动应用程序).然后我将使用电子邮件任务通过电子邮件发送文件.我需要的是刷新文件并在VB或C#中完全无效的脚本，我必须问是否有人有一个脚本可以执行此操作，并且可以为我的文件进行自定义并在脚本任务中使用. 我将不胜感激！ 多谢， 弗拉德

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

推荐答案

希望这就是您想要的

 ' 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

这篇关于使用SSIS脚本任务刷新Excel的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！