ACC2000: Exported Line Separator Is Converted Incorrectly

ID: Q198498

The information in this article applies to:

SYMPTOMS

Moderate: Requires basic macro, coding, and interoperability skills.

If you export a Microsoft Access table that contains line separators to Microsoft Excel format, the line separators will not appear as expected in Microsoft Excel. Instead, you see two vertical characters where the line separators should be.


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:


    5. 
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.


STATUS

Microsoft has confirmed this to be a problem in the Microsoft products listed at the beginning of this article.


MORE INFORMATION

Steps to Reproduce Problem

  1. In Microsoft Access, open the sample database Northwind.mdb.


  2. Click Tables under Objects, click to select the Suppliers table, and then on the File menu, click Export.


  3. In the Save As Type box, select Microsoft Excel 97-2000.


  4. Save the file in the My Documents folder as Suppliers.


  5. Open Microsoft Excel.


  6. On the File menu, click Open and move to the My Documents folder.


  7. Select Suppliers and click Open.


Note that in the spreadsheet you see vertical bars in some of the addresses in the E column. These are the addresses that originally contained line breaks in the Suppliers table in Northwind.mdb.

Additional query words: pra 


Keywords          : kbdta 
Version           : WINDOWS:2000
Platform          : WINDOWS 
Issue type        : kbbug

Last Reviewed: July 13, 1999