Setting Excel Cell colors from Script Task
Hello Evreyone,
I am trying to open a excel file form Script task, I am able to do that. In my code I am getting problem When I am setting the background color for some range I have written a code and note able to code for color function
Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Runtime
Imports System.IO
Imports Microsoft.Office.Interop.Excel
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()
Dim appexcel As Object
Dim newbook As Object
Dim Sheet As Object
Dim objFSO As Object
Dim Connection As String = Dts.variables("User::V_FilePath").value + "\TMPLT_Error.xlsx"
Dim NFileName As String = Dts.variables("User::V_FilePath").value + "\TMPLT_Error_M.xlsx"
'MsgBox(Connection)
appexcel = CreateObject("Excel.Application")
newbook = appexcel.Workbooks.Open(Connection)
Sheet = newbook.worksheets("Error")
If Not (Sheet.Range("A2").text = "") Then
Sheet.Columns("E:AD").Locked = False
Sheet.Range("A2:AF" & Sheet.Range("A65536").End(XlDirection.xlUp).Row.ToString).wraptext = True
Sheet.Range("A2:D" & Sheet.Range("A65536").End(XlDirection.xlUp).Row.ToString).Select
Getting Problem Here How to color the selected range with lets say Red
Sheet.Protect(Password:="ALF")
appexcel.DisplayAlerts = False
newbook.Save()
appexcel.DisplayAlerts = True
newbook.Close(False)
'appexcel.close()
appExcel.quit()
End If
objFSO = CreateObject("Scripting.FileSystemObject")
objFSO.MoveFile(Connection, NFileName)
Dts.TaskResult = ScriptResults.Success
End Sub
End Class
Thanks in Advance
Sumit
September 29th, 2012 8:32am