December 31, 2014 at 8:34 am
Hi Guys,
Simple Script Task, just rename Excel tab, here is my code...
Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Runtime
Imports Microsoft.Office.Interop.Excel
Imports System.IO
Imports System.Text
<System.AddIn.AddIn("ScriptMain", Version:="1.0", Publisher:="", Description:="")> _
<System.CLSCompliantAttribute(False)> _
Partial Public Class ScriptMain
Inherits Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase
Enum ScriptResults
Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success
Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
End Enum
Public Sub Main()
Dts.VariableDispenser.LockForRead("User::FileName") ' File Name Variable
Dim variablesList As Variables
Dts.VariableDispenser.GetVariables(variablesList)
'Dim SFileName As String
'SFileName = variablesList("User::Filename").Value.ToString
' Dim vars As Variables 'New Added For Variable
Dim oMissing As Object = System.Reflection.Missing.Value
Dim xl As New Microsoft.Office.Interop.Excel.ApplicationClass()
Dim xlBook As Microsoft.Office.Interop.Excel.Workbook
Dim xlSheet As Microsoft.Office.Interop.Excel.Worksheet
'Dts.VariableDispenser.LockForRead("Filename") 'New Added For Variable
'Start For Variable
'Dim File As String 'New Added For Variable
'File = CType(vars("Filename").Value, String) 'New Added For Variable
'MsgBox(Prompt:="Filename")
'Dim laPath As String = "C:\Excel\ABC_dr_daily_lf_10-07-14.xls" 'My File Name
Dim lapath As String = variablesList("User::FileName").Value.ToString 'New Added For Variable
'MsgBox(Prompt:="Filename")
xlBook = DirectCast(xl.Workbooks.Open(laPath, oMissing, oMissing, oMissing, oMissing, oMissing, _
oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, _
oMissing, oMissing, oMissing), Workbook)
xlSheet = DirectCast(xlBook.Worksheets.Item(1), Worksheet)
xlSheet.Name = "data"
xlBook.Save()
xl.Application.Workbooks.Close()
Dts.TaskResult = ScriptResults.Success
End Sub
End Class
Finally I intalled MS Excel 2007 on my Dev Server. Here is the error that I am receiving....
Error: System.Reflection.TargetInvocationException: Exception has been thrown by the target of an invocation. ---> System.InvalidCastException:
Unable to cast COM object of type 'Microsoft.Office.Interop.Excel.ApplicationClass' to interface type 'Microsoft.Office.Interop.Excel._Application
'. This operation failed because the QueryInterface call on the COM component for the interface with IID '{000208D5-0000-0000-C000-000000000046}' failed due to the following error: Library not registered.
Please help me, this is very urgent.
Thank You.
Viewing 0 posts
You must be logged in to reply to this topic. Login to reply