none
Find and Replace Carriage Return in Excel with VBA

    Question

  • Hi all,

    I need to find all <CR> character (carriage return) because when I export a table from Access to Excel, the data contains "[]" that are carriage returns.

    so i need to find these characters and replace with null

    can i do this with vba?

    many thanks

    Tuesday, September 19, 2006 11:19 PM

All replies

  • Hi, you could try something like this:

    Dim objExcel As Excel.Application     ' Excel object
    Dim wbkTemp As Excel.Workbook   ' Workbook
    Dim wksTemp as Excel.Worksheet     ' Worksheet

    ' Open record sets and objects

    Set objExcel = CreateObject("Excel.Application")
    Set wbkTemp = objExcel.Workbooks.Open(C:\Temp.xls)
    Set wksTemp = wbkTemp.Worksheets(strQuery)

    wksTemp.Activate
    wksTemp.Range("A2:H100").Select
    Selection.Replace What:=vbCrLf, Replacement:="", LookAt:=xlPart, _
    SearchOrder:=xlByRows, MatchCase:=False

    ' Close record sets and objects

      wbkTemp.Close True
      Set wbkTemp = Nothing
      objExcel.Quit
      Set objExcel = Nothing
    Friday, September 22, 2006 9:42 AM