ACC2000: Exported Line Separator Is Converted Incorrectly

• Microsoft Access 2000

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:




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.

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.

Additional query words: pra

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