Microsoft KB Archive/198498

= ACC2000: Exported Line Separator Is Converted Incorrectly =

Article ID: 198498

Article Last Modified on 6/24/2004

-

APPLIES TO


 * Microsoft Access 2000 Standard Edition

-



This article was previously published under Q198498



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



SYMPTOMS
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:  Open the Microsoft Excel spreadsheet that contains the imported table with the vertical bars. On the Tools menu, point to Macro, and then click Visual Basic Editor. In the Visual Basic Editor, on the Insert Menu, click Module.  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  Close the Visual Basic Editor. On the Tools menu, point to Macro, and then click Macros.</li> In the Macros list, select changeStr, and then click Run.</li></ol>

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

<div class="status_section">

STATUS
Microsoft has confirmed that this is a problem in the Microsoft products that are listed at the beginning of this article.

<div class="moreinformation_section">

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: kbbug kbnofix KB198498

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.