Member Avatar for moone009

I'm working on an app to parse Excel files. I need to remove invalid characters in column 2 and then in column 3 I need to trim and upper that column.

Any ideas?

Dim sql As String
        Dim xlApp As Excel.Application
        Dim xlWorkBook As Excel.Workbook
        Dim xlWorkSheet As Excel.Worksheet

        ' xlApp = New Excel.ApplicationClass
        xlApp = New Excel.Application
        xlWorkBook = xlApp.Workbooks.Open("c:\test13.xlsx")
        xlWorkSheet = xlWorkBook.Worksheets("sheet1")
        sql = "SELECT * FROM [Sheet1$]"

    [B]    'Remove Invalid Characters in Column 2[/B]
        xlWorkSheet.Cells(2:2)  {",", "%", "#", "a", "b", "c"} and Trim(vbUpperCase)

        [B][U]'Remove Invalid Characters in Column 3[/U][/B]
        xlWorkSheet.Cells(3:3) trim(upper)


        xlWorkBook.Close()
        'xlApp.Quit()

        releaseObject(xlApp)
        releaseObject(xlWorkBook)
        releaseObject(xlWorkSheet)

    End Sub

    Private Sub releaseObject(ByVal obj As Object)
        Try
            System.Runtime.InteropServices.Marshal.ReleaseComObject(obj)
            obj = Nothing
        Catch ex As Exception
            obj = Nothing
        Finally
            GC.Collect()
        End Try
  • 2 Contributors
  • 1 Reply
  • 386 Views
  • 18 Hours Discussion Span
  • Latest Post Latest Post by sandeepparekh9
Member Avatar for sandeepparekh9

change your code to this :

Dim sql As String
        Dim xlApp As Microsoft.Office.Interop.Excel.Application
        Dim xlWorkBook As Microsoft.Office.Interop.Excel.Workbook
        Dim xlWorkSheet As Microsoft.Office.Interop.Excel.Worksheet


        xlApp = New Microsoft.Office.Interop.Excel.Application
        xlWorkBook = xlApp.Workbooks.Open(txtExcelFile.Text)
        xlWorkSheet = xlWorkBook.Worksheets("sheet1")
        sql = "SELECT * FROM [Sheet1$]"


        For i As Integer = 1 To xlWorkSheet.Rows.Count - 1

            If xlWorkSheet.Cells(i, 2).value <> Nothing Then


                xlWorkSheet.Cells(i, 2) = xlWorkSheet.Cells(i, 2).value.ToString().Replace("^", "")
                xlWorkSheet.Cells(i, 2) = xlWorkSheet.Cells(i, 2).value.ToString().Replace("`", "")
                xlWorkSheet.Cells(i, 2) = xlWorkSheet.Cells(i, 2).value.ToString().Replace("#", "")
                xlWorkSheet.Cells(i, 2) = xlWorkSheet.Cells(i, 2).value.ToString().Replace("@", "")
                xlWorkSheet.Cells(i, 2) = xlWorkSheet.Cells(i, 2).value.ToString().Replace("&", "")
                xlWorkSheet.Cells(i, 2) = xlWorkSheet.Cells(i, 2).value.ToString().Replace("*", "")
                xlWorkSheet.Cells(i, 2) = xlWorkSheet.Cells(i, 2).value.ToString().Replace("!", "")

                xlWorkSheet.Cells(i, 3) = xlWorkSheet.Cells(i, 3).value.ToString().ToUpper()
            Else
                xlWorkBook.Close()
                xlApp.Quit()
                releaseObject(xlApp)
                releaseObject(xlWorkBook)
                releaseObject(xlWorkSheet)
                Return
            End If
        Next
Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.