Navigation

	Home Page
	Index
	All Pages

Quick Search
Advanced Search »

Contributor Links

	Create a new Page
	Administration
	File Management
	Login/Logout
	Your Profile

Other Wiki Sections

Software

Hiding Excel Sheets from a Script Task - SQL Server Integration Services

In SQL Server Integration Services, the Script Task allows you to code in VB.NET. However, you're not allow to set references to COM objects, including the Excel object model. The following code provides a example of a work-around. The key is the CreateComObjectFromProgId function. Be sure to set Option Strict Off in the module where you use this code.

Public Sub Main()

    Dim app As Object

    Try

        app = CreateComObjectFromProgId("Excel.Application")

        If app IsNot Nothing Then

            Dim wbk As Object = app.Workbooks.Open("C:\Data\ExcelTest.xls")

            If wbk IsNot Nothing Then

                Dim sheet As Object = wbk.Sheets("Sheet3")

                If sheet IsNot Nothing Then

                    sheet.Visible = 0 '0 = hidden
                    wbk.Save()
                    app.Quit()

                End If

            End If

        End If

    Catch ex As Exception

        app.Quit()

    End Try

    Dts.TaskResult = Dts.Results.Success

End Sub

Private Function CreateComObjectFromProgId(ByVal progId As String) As Object

    Dim oType As Type = Type.GetTypeFromProgID(progId)

    If oType Is Nothing Then
        Return Nothing
    Else
        Return Activator.CreateInstance(oType)
    End If

End Function