none
Replace a character in excel (VB.net) RRS feed

  • Question

  • Hi,

    I got an exception on the following line. How can I fix it? Thanks for your comment in advance.

     xlRange1 = ws.Range(UnitRange).Select

    System.InvalidCastException: 'Unable to cast object of type 'System.Boolean' to type 'Microsoft.Office.Interop.Excel.Range'.'

    Dim xlRange1 As Excel.Range = Nothing

    Public ws As Excel.Worksheet = Nothing

        Public xlApp As Excel.Application
        Public xlWorkBooks As Excel.Workbooks
    xlApp = CType(System.Runtime.InteropServices.Marshal.GetActiveObject("Excel.Application"), Microsoft.Office.Interop.Excel.Application) 

    ws =xlApp.Workbooks(1)..Worksheets(1)

     

     xlRange1 = ws.Range(UnitRange).Select
            For Each row In xlRange1
                ws.substitute(xlRange1, " dB", vbNullString)
                ws.substitute(xlRange1, "Gbps", vbNullString)
                ws.substitute(xlRange1, "mv", vbNullString)
                ws.substitute(xlRange1, "uV", vbNullString)
                ws.substitute(xlRange1, "ps", vbNullString)
                ws.substitute(xlRange1, " mui", vbNullString)
                ws.substitute(xlRange1, " ui", vbNullString)
                ws.substitute(xlRange1, " Gsymbols/s", vbNullString)
            Next

    Jay


    Monday, October 28, 2019 7:28 AM

Answers

  • Hi,

    You can try my code as follows:

    Change your  xlRange1 = ws.Range(UnitRange).Select to xlRange1 = ws.Range("A1:A6"), and then change your code ws.substitute(xlRange1, " dB", vbNullString) to xlRange1.Replace(" dB", vbNullString), cause it belongs to VBA not VB.NET.

    https://docs.microsoft.com/en-us/office/vba/api/excel.worksheetfunction.substitute

    Public ws As Excel.Worksheet = Nothing
        Private Sub Button3_Click(sender As Object, e As EventArgs) Handles Button3.Click
            Dim xlRange1 As Excel.Range = Nothing
            xlApp = CType(System.Runtime.InteropServices.Marshal.GetActiveObject("Excel.Application"), Microsoft.Office.Interop.Excel.Application)
            ws = xlWorkBook.Worksheets(1)
            xlRange1 = ws.Range("A1:A6")
            For Each row In xlRange1
                xlRange1.Replace("3", vbNullString)
                'ws.(xlRange1, " dB", vbNullString)
                'ws.substitute(xlRange1, "Gbps", vbNullString)
                'ws.substitute(xlRange1, "mv", vbNullString)
                'ws.substitute(xlRange1, "uV", vbNullString)
                'ws.substitute(xlRange1, "ps", vbNullString)
                'ws.substitute(xlRange1, " mui", vbNullString)
                'ws.substitute(xlRange1, " ui", vbNullString)
                'ws.substitute(xlRange1, " Gsymbols/s", vbNullString)
            Next
        End Sub

    Hope it help to you.

    Best Regards,

    Julie


    MSDN Community Support Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.



    Monday, October 28, 2019 10:04 AM
    Moderator

All replies

  • Hi,

    You can try my code as follows:

    Change your  xlRange1 = ws.Range(UnitRange).Select to xlRange1 = ws.Range("A1:A6"), and then change your code ws.substitute(xlRange1, " dB", vbNullString) to xlRange1.Replace(" dB", vbNullString), cause it belongs to VBA not VB.NET.

    https://docs.microsoft.com/en-us/office/vba/api/excel.worksheetfunction.substitute

    Public ws As Excel.Worksheet = Nothing
        Private Sub Button3_Click(sender As Object, e As EventArgs) Handles Button3.Click
            Dim xlRange1 As Excel.Range = Nothing
            xlApp = CType(System.Runtime.InteropServices.Marshal.GetActiveObject("Excel.Application"), Microsoft.Office.Interop.Excel.Application)
            ws = xlWorkBook.Worksheets(1)
            xlRange1 = ws.Range("A1:A6")
            For Each row In xlRange1
                xlRange1.Replace("3", vbNullString)
                'ws.(xlRange1, " dB", vbNullString)
                'ws.substitute(xlRange1, "Gbps", vbNullString)
                'ws.substitute(xlRange1, "mv", vbNullString)
                'ws.substitute(xlRange1, "uV", vbNullString)
                'ws.substitute(xlRange1, "ps", vbNullString)
                'ws.substitute(xlRange1, " mui", vbNullString)
                'ws.substitute(xlRange1, " ui", vbNullString)
                'ws.substitute(xlRange1, " Gsymbols/s", vbNullString)
            Next
        End Sub

    Hope it help to you.

    Best Regards,

    Julie


    MSDN Community Support Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.



    Monday, October 28, 2019 10:04 AM
    Moderator
  • Maybe split the line:

       xlRange1 = ws.Range(UnitRange)

       xlRange1.Select

       . . .

    Monday, October 28, 2019 10:33 AM
  • Thanks  very much for you all!
    Tuesday, October 29, 2019 1:00 AM