ACC2000: Exported Line Separator Is Converted Incorrectly

RESOLUTION

To avoid this behavior, when you export from Microsoft Access, in the Export dialog box, click the Save Formatted option, and then click Save.

However, if you are unable to re-export the data from Microsoft Access, you can eliminate the vertical bar characters from the Microsoft Excel spreadsheet as follows:

1. Open the Microsoft Excel spreadsheet that contains the imported table with the vertical bars.
2. On the Tools menu, point to Macro, and then click Visual Basic Editor.
3. In the Visual Basic Editor, on the Insert Menu, click Module.
4. In the new module, type the following:

   Sub changeStr()
   
      EndCell = Range("A1").SpecialCells(xlCellTypeLastCell).Address
   
      ActiveSheet.Range("A1:" & EndCell).Replace _
      What:=Chr(13) & Chr(10), Replacement:=Chr(10), _
      SearchOrder:=xlByColumns, MatchCase:=True
   
   End Sub
   					

5. Close the Visual Basic Editor.
6. On the Tools menu, point to Macro, and then click Macros.
7. In the Macros list, select changeStr, and then click Run.

Note that the vertical bars are gone, and the line breaks are where they should be.