无法从SSIS运行宏... [英] Cannot run the macro ... from SSIS
问题描述
使用从SSIS运行Excel宏中的类似逻辑
我的脚本任务逻辑在SSIS中如下，
public void Main()
{
// TODO: Add your code here
string filename;//= Dts.Variables["User::filename"].Value.ToString();
filename = @"D:\VersionWithDummyData\finbalReviewTest.xlsm";
string S_Directory = @"D:\VersionWithDummyData\";
if (S_Directory.Substring(S_Directory.Length - 1, 1) != @"\")
{
S_Directory = S_Directory + @"\";
}
DirectoryInfo finfo = new DirectoryInfo(S_Directory);
if (filename.ToString().Substring(1, 2) != "~$")
{
try
{
xls.Application ExcelObj = new xls.Application();
ExcelObj.DisplayAlerts = true;
ExcelObj.Visible = true;
ExcelObj.DefaultFilePath = S_Directory;
xls.Workbook eBook = ExcelObj.Workbooks.Open(filename.ToString(), false, false,
Type.Missing, "", "", true, xls.XlPlatform.xlWindows, "",
false, false, 0, false, true, 0);
foreach (xls.WorkbookConnection wc in eBook.Connections)
{
if (wc.Type.ToString() == "xlConnectionTypeODBC")
{
wc.ODBCConnection.BackgroundQuery = false;
}
else
{
wc.OLEDBConnection.BackgroundQuery = false;
}
}
eBook.RefreshAll();
eBook.Save();
ExcelObj.Run("Module1",Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing
, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing
, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing
, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing
, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing
, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
eBook.Save();
ExcelObj.Workbooks.Close();
ExcelObj.Quit();
}
catch (COMException e)
{
MessageBox.Show(filename.ToString() + " has an issue with error " + e.Message);
}
}
Dts.TaskResult = (int)ScriptResults.Success;
}
excel(.xlsm)中可用的宏如下
Sub fileSave()
'
Dim newFileName As String, originalFileName As String, fileSaveName As String, fileNamePathSaved As String, fileNameSaved As String
Dim response As VbMsgBoxResult, currentRoute As String
'
ThisWorkbook.RefreshAll
ActiveWorkbook.Save ' save the current workbook before messing with it
Application.DisplayAlerts = False ' turns off alerts and messages
' Save file name and path into a variable
originalFileName = ActiveWorkbook.FullName ' gets the fullname with path
' originalFilePath = ActiveWorkbook.Path ' grabs the current path
Dim usingReplace As String
usingReplace = Replace(originalFileName, ".xlsm", ".xlsx")
ActiveWorkbook.SaveAs Filename:=usingReplace, FileFormat:=xlOpenXMLWorkbook
fileNameSaved = ActiveWorkbook.Name ' grabs the name of the saved file
Workbooks.Open Filename:=originalFileName 'reopens the original workbook file
Application.DisplayAlerts = True ' turns the alerts and messages back on
'provide an opportinity to clear the incident report flag
' If incidentFiled = True Then response = MsgBox("Do you want to clear the Incident Report?", vbInformation + vbOKCancel, "Incident Report Form")
If response = vbOK Then incidentFiled = False
'close the newly made file
' Workbooks(fileNameSaved).Close True ' sub terminates at this point
'
End Sub
以上宏被保存为Module1，当我尝试运行该程序包时，数据正在刷新，但是宏未得到执行
注意:尝试了类似文章中建议的所有解决方案
关闭仍然是错误发生的错误
重要提示:
*尝试从excel-> developer-> visual ***-> Module1->执行时执行宏，然后按预期方式工作.*
ExcelObj.Run("Module1"，Type.Missing，Type.Missing，Type.Missing，Type.Missing，Type.Missing ，Type.Missing，Type.Missing，Type.Missing，Type.Missing，Type.Missing ，Type.Missing，Type.Missing，Type.Missing，Type.Missing，Type.Missing ，Type.Missing，Type.Missing，Type.Missing，Type.Missing，Type.Missing ，Type.Missing，Type.Missing，Type.Missing，Type.Missing，Type.Missing ，Type.Missing，Type.Missing，Type.Missing，Type.Missing，Type.Missing)；
在这里，您只是指向"Module1"，而不是实际的宏. "Module1"是保存"fileSave"的位置.
尝试
更改
ExcelObj.Run("Module1",Type.Missing,.....
到
ExcelObj.Run("fileSave",Type.Missing,.....
use the similar logic from Run an Excel Macro from SSIS
My Script task logic is as follows in SSIS,
public void Main()
{
// TODO: Add your code here
string filename;//= Dts.Variables["User::filename"].Value.ToString();
filename = @"D:\VersionWithDummyData\finbalReviewTest.xlsm";
string S_Directory = @"D:\VersionWithDummyData\";
if (S_Directory.Substring(S_Directory.Length - 1, 1) != @"\")
{
S_Directory = S_Directory + @"\";
}
DirectoryInfo finfo = new DirectoryInfo(S_Directory);
if (filename.ToString().Substring(1, 2) != "~$")
{
try
{
xls.Application ExcelObj = new xls.Application();
ExcelObj.DisplayAlerts = true;
ExcelObj.Visible = true;
ExcelObj.DefaultFilePath = S_Directory;
xls.Workbook eBook = ExcelObj.Workbooks.Open(filename.ToString(), false, false,
Type.Missing, "", "", true, xls.XlPlatform.xlWindows, "",
false, false, 0, false, true, 0);
foreach (xls.WorkbookConnection wc in eBook.Connections)
{
if (wc.Type.ToString() == "xlConnectionTypeODBC")
{
wc.ODBCConnection.BackgroundQuery = false;
}
else
{
wc.OLEDBConnection.BackgroundQuery = false;
}
}
eBook.RefreshAll();
eBook.Save();
ExcelObj.Run("Module1",Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing
, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing
, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing
, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing
, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing
, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
eBook.Save();
ExcelObj.Workbooks.Close();
ExcelObj.Quit();
}
catch (COMException e)
{
MessageBox.Show(filename.ToString() + " has an issue with error " + e.Message);
}
}
Dts.TaskResult = (int)ScriptResults.Success;
}
And the macros available in the excel(.xlsm) is as follows
Sub fileSave()
'
Dim newFileName As String, originalFileName As String, fileSaveName As String, fileNamePathSaved As String, fileNameSaved As String
Dim response As VbMsgBoxResult, currentRoute As String
'
ThisWorkbook.RefreshAll
ActiveWorkbook.Save ' save the current workbook before messing with it
Application.DisplayAlerts = False ' turns off alerts and messages
' Save file name and path into a variable
originalFileName = ActiveWorkbook.FullName ' gets the fullname with path
' originalFilePath = ActiveWorkbook.Path ' grabs the current path
Dim usingReplace As String
usingReplace = Replace(originalFileName, ".xlsm", ".xlsx")
ActiveWorkbook.SaveAs Filename:=usingReplace, FileFormat:=xlOpenXMLWorkbook
fileNameSaved = ActiveWorkbook.Name ' grabs the name of the saved file
Workbooks.Open Filename:=originalFileName 'reopens the original workbook file
Application.DisplayAlerts = True ' turns the alerts and messages back on
'provide an opportinity to clear the incident report flag
' If incidentFiled = True Then response = MsgBox("Do you want to clear the Incident Report?", vbInformation + vbOKCancel, "Incident Report Form")
If response = vbOK Then incidentFiled = False
'close the newly made file
' Workbooks(fileNameSaved).Close True ' sub terminates at this point
'
End Sub
the above macros is saved as Module1, when i try to run the package, data is getting refreshed but the macros is not getting executed
Note: Tried all the solutions suggested in the similar post
bust still the error is error occurred
Important:
*the macros when tried to execute from excel-->developer-->visual***-->Module1-->execute, then working as expected.*
ExcelObj.Run("Module1",Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing , Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing , Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing , Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing , Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing , Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
Here you are just pointing towards "Module1" and not the actual macro. "Module1" is where the "fileSave" is saved.
Try this
Change
ExcelObj.Run("Module1",Type.Missing,.....
to
ExcelObj.Run("fileSave",Type.Missing,.....
这篇关于无法从SSIS运行宏...的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
