locked
VB.NET open an Excel file (XLSX) then find a named worksheet (e.g "Order") then save that worksheet as a CSV file RRS feed

  • Question

  • I've been asked to pre-process an XLSX file using a VB.NET application. I thought the task of opening an Excel file (XLSX) then find a named worksheet (e.g "Order") then save that worksheet as a CSV file would be simple. However having written the code and run the program it appears not to create a CSV file. If anyone has written a working VB.NET code which does the above then please advise. I've used an XLSX provided to me by a customer (which opens OK in Excel 2013 on my PC). The file contains a number of worksheets and I can save the 'order' worksheet as a CSV file in MS Excel (but not in VB.NET code). I have included the main function (which saves the file)

       

    ' Save the 'orders' worksheet of the XLSX file to a CSV file - to be processed


       

    PrivateFunctionProcessMSExcel(ByValpsFileName AsString) AsBoolean


           

    DimxlApp AsObject' Microsoft.Office.Interop.Excel.Application = Nothing


           

    DimxlBook AsObject' Microsoft.Office.Interop.Excel.Workbook = Nothing


           

    DimxlDetailSheet AsObject' Microsoft.Office.Interop.Excel.Worksheet = Nothing


           

    DimbOK AsBoolean= False


           

    ConstxlCSV = 6

           

    Try


                xlApp = CreateObject(

    "Excel.Application") ' , Microsoft.Office.Interop.Excel.Application)


                xlBook = xlApp.Workbooks.Open(psFileName)

                xlApp.DisplayAlerts =

    False


                xlApp.Visible =

    False


               

    Try


                    xlDetailSheet = xlBook.Worksheets(

    "Order") ', Microsoft.Office.Interop.Excel.Worksheet)


                   

    DimsName AsString= Replace(xlBook.Name, ".xlsx", ".csv")

                    xlDetailSheet.Visible =

    True


                    xlDetailSheet.SaveAs(Filename:=sName, FileFormat:=xlCSV, CreateBackup:=

    False' XlFileFormat.xlCSV)


                    bOK =

    True


               

    Catchex AsException


                    RhErrorDisplay(

    "Unable to access Excel sheet 'Order'"& vbCrLf & vbCrLf & ex.Message)

                   

    Exit Try


               

    EndTry


           

    Catchex AsException


                RhErrorDisplay(

    "Error reading Excel spreadsheet."& vbCrLf & vbCrLf & ex.Message)

                bOK =

    False


           

    Finally


               

    ' xlBook.Save()


                xlBook.Close(SaveChanges:=

    False)

                xlApp.Quit()

               

    'If Not xlDetailSheet Is Nothing Then xlDetailSheet = Nothing


               

    'If Not xlBook Is Nothing Then xlBook = Nothing


               

    'If Not xlApp Is Nothing Then xlApp = Nothing


                releaseobject(xlDetailSheet)

                releaseobject(xlBook)

                releaseobject(xlApp)

           

    EndTry


           

    Return(bOK)

       

    EndFunction

    Monday, June 8, 2015 2:15 PM

Answers

  • Hi Nigel Smith,

    According to the description, you were export specific worksheet to CSV file format. Since the code is not well formated and there are many errors, I wrote a simple sample to ahcieve the goal for your reference:

    Imports Excel = Microsoft.Office.Interop.Excel
    
    Public Class ExportExcelToCSV
    
          Sub Export(ByVal fileName As String)
            Dim excelApp As Excel.Application
            Dim xlBook As Excel.Workbook
            Dim xlSheet As Excel.Worksheet
            excelApp = CreateObject("Excel.Application")
            excelApp.DisplayAlerts = False
            xlBook = excelApp.Workbooks.Open(fileName)
            xlSheet = xlBook.Worksheets("Order")
    
            Dim sName As String = Replace(xlBook.FullName, ".xlsx", ".csv")
            xlSheet.SaveAs(sName, Excel.XlFileFormat.xlCSV)
            xlBook.Close()
            excelApp.Quit()
        End Sub
    
    End Class

    In addtion, here is an helpful article about automate Excel application using VB.Net:
    How to automate Microsoft Excel from Visual Basic .NET

    Regards & Fei


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    • Marked as answer by Nigel Smith Tuesday, June 9, 2015 8:55 AM
    Tuesday, June 9, 2015 2:32 AM

All replies

  • Hi, 

    Let me show you only how to create a csv file.
    This is my code. 
    ------------------------------
      Private TextFile As IO.StreamWriter
      Private TextLine As String  ' -- one line for output
      Private cellData As String  ' -- for removing comma in cell data

    Private Sub prc_Export_CSV()
      ' --- create an instance of file
      Me.TextFile = New IO.StreamWriter(Me.ExportFileName, False, System.Text.Encoding.UTF8)
      ' --- write column header
      Me.TextLine = ""
      For myCol As Integer = 0 To Me.dgv_Export.ColumnCount - 1
        If myCol = Me.dgv_Export.ColumnCount - 1 Then
          Me.TextLine += Replace(Me.dgv_Export.Columns(myCol).Name, "Exp_", "")  ' -- final column : no comma
        Else
          Me.TextLine += Replace(Me.dgv_Export.Columns(myCol).Name, "Exp_", "") & ","  ' -- comma added
        End If
      Next
      Me.TextFile.WriteLine(Me.TextLine) ' -- write with CR/LF
      ' --- output a cell date
      For myRow As Integer = 0 To Me.dgv_Export.RowCount - 1
        Me.TextLine = ""
          For myCol As Integer = 0 To Me.dgv_Export.ColumnCount - 1
            If IsDBNull(Me.dgv_Export.Item(myCol, myRow).Value) Then
              Me.cellData = ""
            Else
              Me.cellData = Me.dgv_Export.Item(myCol, myRow).Value
              Me.cellData = Replace(Me.cellData, ",", "") ' -- remove commna from data
            End If
            Select Case myCol
              Case Is = Me.dgv_Export.ColumnCount - 1
                Me.TextLine += Me.cellData ' -- no comma
              Case Else
                Me.TextLine += Me.cellData & "," ' -- comma added
            End Select
          Next
          Me.TextFile.WriteLine(Me.TextLine) ' -- write with CR/LF
      Next
      ' ---
      Me.TextFile.Close() ' -- close StreamWriter
      Me.TextFile.Dispose() ' -- release StreamWriter
    End Sub
    -----------------------------

    Best regards,

    • Edited by Ashidacchi Monday, June 8, 2015 3:20 PM
    Monday, June 8, 2015 3:10 PM
  • Hi Nigel Smith,

    According to the description, you were export specific worksheet to CSV file format. Since the code is not well formated and there are many errors, I wrote a simple sample to ahcieve the goal for your reference:

    Imports Excel = Microsoft.Office.Interop.Excel
    
    Public Class ExportExcelToCSV
    
          Sub Export(ByVal fileName As String)
            Dim excelApp As Excel.Application
            Dim xlBook As Excel.Workbook
            Dim xlSheet As Excel.Worksheet
            excelApp = CreateObject("Excel.Application")
            excelApp.DisplayAlerts = False
            xlBook = excelApp.Workbooks.Open(fileName)
            xlSheet = xlBook.Worksheets("Order")
    
            Dim sName As String = Replace(xlBook.FullName, ".xlsx", ".csv")
            xlSheet.SaveAs(sName, Excel.XlFileFormat.xlCSV)
            xlBook.Close()
            excelApp.Quit()
        End Sub
    
    End Class

    In addtion, here is an helpful article about automate Excel application using VB.Net:
    How to automate Microsoft Excel from Visual Basic .NET

    Regards & Fei


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    • Marked as answer by Nigel Smith Tuesday, June 9, 2015 8:55 AM
    Tuesday, June 9, 2015 2:32 AM
  • Thanks. The code supplied works well. I may consider doing an alternative function which could strip out the blank rows from the worksheet (as the customer file has a large number of empty rows - which will slow down the import to the 3rd part software of the order.csv file.

        Private Function ExportMSExceltoCSV(ByVal fileName As String)
            Dim excelApp As Excel.Application = Nothing
            Dim xlBook As Excel.Workbook = Nothing
            Dim xlSheet As Excel.Worksheet = Nothing
            Dim bOK As Boolean = False
    
            Try
                excelApp = CreateObject("Excel.Application")
                excelApp.DisplayAlerts = False
                xlBook = excelApp.Workbooks.Open(fileName)
                xlSheet = xlBook.Worksheets("Order")
                Dim sName As String = Replace(xlBook.FullName, ".xlsx", ".csv")
                xlSheet.SaveAs(sName, Excel.XlFileFormat.xlCSV)
                ' Might be usefull to check the CSV file exists !
                bOK = True
            Catch ex As Exception
            Finally
                xlBook.Close()
                excelApp.Quit()
            End Try
    
            Return bOK
        End Function

    Tuesday, June 9, 2015 8:57 AM
  • Just need to modify the code (as per my original posting) - so that it's compatible with earlier versions of MS Excel (e.g. late binding, not early binding)
    Tuesday, June 9, 2015 11:42 AM
  • Here is the code sample (with Early Binding).

        ' Early Binding (Export a worksheet to a CSV file)
        Private Function ExportMSExceltoCSV(ByVal fileName As String)
            Dim excelApp As Object = Nothing    ' Excel.Application
            Dim xlBook As Object = Nothing      ' Excel.Workbook
            Dim xlSheet As Object = Nothing     ' Excel.Worksheet
            Dim bOK As Boolean = False
            Const xlCSV = 6
    
            Try
                excelApp = CreateObject("Excel.Application")
                excelApp.DisplayAlerts = False
                xlBook = excelApp.Workbooks.Open(fileName)
                If Not xlBook Is Nothing Then
                    xlSheet = xlBook.Worksheets("Order")
                    Dim sName As String = Replace(xlBook.FullName, ".xlsx", ".csv")
                    xlSheet.SaveAs(sName, xlCSV) 'Excel.XlFileFormat.xlCSV
                    ' Might be usefull to check the CSV file exists !
                    bOK = True
                Else
                    ' Throw New Exception("Warning: unable to open XLSX file")
                    RhErrorDisplay("Error : unable to open XLSX file.")
                End If
    
            Catch ex As Exception
                RhErrorDisplay("Error reading Excel spreadsheet." & vbCrLf & vbCrLf & ex.Message)
            Finally
                If Not xlBook Is Nothing Then xlBook.Close()
                If Not excelApp Is Nothing Then excelApp.Quit()
            End Try
    
            Return bOK
        End Function

    Thursday, June 11, 2015 11:03 AM