What name space to use in VB.net

Question

Writing a vb.net script(as a part of SSIS ETL) to convert xls to tsv file.I was trying to use the name space Imports Microsoft.Excel to include the below codes .But ,it show there is no such name space! What name space to be included to use the Excel open close and save as functionality as a part of the vb.net oExcel.Workbooks.Open oBook.SaveAs(sTsvPath, -4158)

The vb.net code is

Public Sub Main()

        Dim oExcel As Object
        Dim oBook As Object

        Dim sFileName As String
        Dim sFileNameOnly As String


        Dim sXlsPath As String
        Dim sTsvPath As String



        sFileName = CStr(Dts.Variables("User::Xls_File_Name").Value)


        sXlsPath = "H:\Xls_Files\" + sFileName

        sFileNameOnly = Path.GetFileNameWithoutExtension(sFileName)

        sTsvPath = "H:\Xls_Files\" + sFileNameOnly + ".Txt"


        oExcel = CreateObject("Excel.Application")


        oBook = oExcel.Workbooks.Open(sXlsPath)

        oBook.SaveAs(sTsvPath, -4158)

        oBook.Close(False)

        oExcel.Quit()

        Dts.TaskResult = ScriptResults.Success
    End Sub

Did you try searching for "vb.net excel namespace"? Hint: you want the Interop one. — Andrew Morton, Jan 12 '17 at 19:13
yes that ,Microsoft.Office.Interop.Excel namespace is only applicable to Microsoft office ,not in vb — user1254579, Jan 13 '17 at 10:47
This isn't built into .Net. You have to have Excel installed on the machine, and you have to use the interop assemblies to open an excel instance and tell it to do what you want. Yes, this does mean actually starting up Excel. — Joel Coehoorn, Jan 13 '17 at 19:01

Andrew Morton · Accepted Answer · 2017-01-13T18:59:30.703

First you need to add a reference to the Microsoft Excel 15.0 Object Library in the Solution Explorer pane. It appears in the COM objects tab when you choose "Add reference..." - your version number (e.g. 15.0) may be different.

Then in the code you have to add Imports Microsoft.Office.Interop.Excel, like this:

Option Infer On
Option Strict On

Imports System.IO
Imports Microsoft.Office.Interop.Excel

Module Module1

    Sub Main()

        Dim srcDir = "C:\temp"
        Dim srcFilename = "somefile.xls"
        Dim destFile = Path.Combine(srcDir, Path.GetFileNameWithoutExtension(srcFilename) & ".txt")

        File.Delete(destFile)

        Dim excel As Application = Nothing
        Dim wb As Workbook = Nothing

        Try
            excel = New Application
            wb = excel.Workbooks.Open(Path.Combine(srcDir, srcFilename))
            wb.SaveAs(destFile, XlFileFormat.xlCurrentPlatformText)

        Finally
            If wb IsNot Nothing Then
                wb.Close()
            End If
            If excel IsNot Nothing Then
                excel.Quit()
            End If

            ' see "The proper way to dispose Excel com object using VB.NET?"
            ' http://stackoverflow.com/a/38111107/1115360 for an explanation of the following:
            GC.Collect()
            GC.WaitForPendingFinalizers()
            GC.Collect()
            GC.WaitForPendingFinalizers()

        End Try

    End Sub

End Module

You will have to add in the DTS-related parts.

I used Path.Combine(srcDir, Path.GetFileNameWithoutExtension(srcFilename) & ".txt") for brevity rather than using Path.GetExtension and Path.ChangeExtension, which you would do in better-quality code. Also, you should wrap the File.Delete in a Try..Catch with an appropriate action in the Catch just in case something goes wrong there.

What name space to use in VB.net

1 Answers1

Linked