none
Search and Replace in Excel RRS feed

  • Question

  • Hello everyone! I am trying to write a subroutine that searches an entire workbook for specific char values and replaces them with another char value.

    Specifically chr(10) with chr(11) and chr(15) with null.

    Any ideas on how to do this?

    I am working in VS2017 with VSTO in VB.net

    Thanks!

    Wednesday, June 27, 2018 5:44 PM

Answers

  • Figured it out:

       

    PrivateSubcharKill_Click(sender AsObject, e AsRibbonControlEventArgs) HandlescharKill.Click

           

    DimactiveWorkSheet AsMicrosoft.Office.Interop.Excel.Worksheet =

                Globals.ThisAddIn.Application.ActiveWorkbook.Worksheets(1)

           

    DimworkSheet AsMicrosoft.Office.Tools.Excel.Worksheet =

                Globals.Factory.GetVstoObject(activeWorkSheet)

           

    DimworkRange AsExcel.Range = workSheet.Cells

            workRange.Replace(vbLf, vbVerticalTab, SearchOrder:=2, MatchCase:=

    True)

            workRange.Replace(

    "^", vbVerticalTab, SearchOrder:=2, MatchCase:=True)

            workRange.Replace(vbCr,

    "", SearchOrder:=2, MatchCase:=True)

       

    EndSub

    • Marked as answer by stopiamwarren Wednesday, June 27, 2018 10:25 PM
    Wednesday, June 27, 2018 10:25 PM