使用SSIS脚本任务刷新Excel

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

我有一个Excel文件,从数据库表中获取外部数据。我需要自动刷新文件并通过电子邮件发送。我打算使用SSIS脚本任务来运行一些VB脚本来打开文件,刷新数据,保存和关闭(显然没有启动应用程序)。然后我将使用电子邮件任务通过电子邮件发送文件。我需要的只是刷新文件的脚本,并且在VB或C#中总是noob我不得不问是否有人有一个脚本可以做到这一点,我可以自定义我的文件并在我的脚本任务中使用。我会感激任何提示!弗拉德,非常感谢

3 个解决方案

#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.

老帖但4M01的回答帮助了我。

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.

注意也不再需要在VS 2015中设置。所以只需删除“set”即可使代码生效。

#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

可执行为/..path../Excel.exe和

Arguments as the desired file (full path) to be opened

参数作为要打开的所需文件(完整路径)

#1