Microsoft KB Archive/115576

= ACC: Imported MS Excel Carriage Returns Become Vertical Bars =

Article ID: 115576

Article Last Modified on 1/19/2007

-

APPLIES TO


 * Microsoft Access 2.0 Standard Edition
 * Microsoft Access 95 Standard Edition
 * Microsoft Access 97 Standard Edition
 * Microsoft Excel 97 Standard Edition
 * Microsoft Excel 95 Standard Edition
 * Microsoft Excel 2.0 Standard Edition
 * Microsoft Excel 2.01
 * Microsoft Excel 2.1 Standard Edition
 * Microsoft Excel 2.10c
 * Microsoft Excel 2.10d
 * Microsoft Excel 3.0 Standard Edition
 * Microsoft Excel 3.0a
 * Microsoft Excel 4.0 Standard Edition
 * Microsoft Excel 4.0a
 * Microsoft Excel 4.0c
 * Microsoft Excel 5.0 Standard Edition

-



This article was previously published under Q115576



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



SYMPTOMS
When you import a Microsoft Excel spreadsheet into Microsoft Access, the carriage return (CR) character appears as a vertical bar or a small box, depending on your version of Microsoft Excel and Microsoft Access. For simplicity, this article refers to vertical bars to indicate either vertical bars or small boxes.

For example, if you import a Microsoft Excel spreadsheet mailing list with complete addresses stored in single cells formatted with carriage returns, the addresses appear in Microsoft Access as single lines with vertical bars between the address items.

This article assumes that you are familiar with Visual Basic for Applications and with creating Microsoft Access applications using the programming tools provided with Microsoft Access. For more information about Visual Basic for Applications, please refer to your version of the "Building Applications with Microsoft Access" manual.

NOTE: Visual Basic for Applications is called Access Basic in Microsoft Access version 2.0. For more information about Access Basic, please refer to the "Building Applications" manual.



CAUSE
The CR character used in Microsoft Excel (ALT+ENTER) differs from that used in Microsoft Access (CTRL+ENTER). As a result, the CR characters in Microsoft Excel spreadsheets are not parsed into carriage returns in Microsoft Access, but into small vertical bars.



RESOLUTION
You can use the following sample user-defined function to convert the CR character used in Microsoft Excel to the CR character used in Microsoft Access. To create the function, follow these steps:

 Start Microsoft Access and open any database. Create a module and type the following line in the Declarations section if it is not already there:

Option Explicit

  Type the following procedure.

NOTE: In the following sample code, an underscore (_) at the end of a line is used as a line-continuation character. Remove the underscore from the end of the line when re-creating this code in Access Basic. Function ChangeStr (s As Variant, a As String, n As String,_                         c As Integer) As Variant ' This function changes all substrings "a" and string "s" ' to "n." The parameter "c" has the same function as in the ' InStr function. Dim temp As String, pos As Integer temp = "" If IsNull(s) Then ChangeStr = Null Exit Function End If        If a = "" Or s = "" Then ChangeStr = s           Exit Function End If        pos = InStr(1, s, a, c)         While pos > 0 temp = temp & Mid$(s, 1, pos - 1) & n           s = Right$(s, Len(s) - pos - Len(a) + 1) pos = InStr(1, s, a, c)        Wend ChangeStr = temp & s     End Function  Create a new update query. Drag the field that you want converted to the query grid.  In the Update To row of the query grid, type the following expression: ChangeStr([ ],Chr$(10),Chr$(13) & Chr$(10),0) where is the name of the field that you want to convert.  Run the query. Note that text containing vertical bars is divided into separate lines with carriage returns.</li></ol>

<div class="moreinformation_section">

Steps to Reproduce Behavior
<ol> In Microsoft Excel, create a spreadsheet and type the following data into cell A1:

Fred Test

111 Main

Anytown, USA

To insert a new line within a cell, press ALT+ENTER. Then type the following data into cell A2:

Joe Test

222 Broadway

Anytown, USA

</li> Save the spreadsheet and quit Microsoft Excel.</li> Start Microsoft Access and open any database.</li> In Microsoft Access 7.0 and 97, on the File menu, point to Get External Data, and then click Import.

In Microsoft Access 2.0, on the File menu, click Import.</li> In Microsoft Access 7.0 and 97, in the Import dialog box, select Microsoft Excel (*.xls) in the Files of type box.

In Microsoft Access 2.0, in the Import dialog box, select Microsoft Excel 2.0, 4.0, or 5.0, depending on the version of Microsoft Excel you are using, and then click OK.</li> In all versions, select the Microsoft Excel file that you created in step 1, and then click Import.</li> In Microsoft Access 7.0 and 97, in the Import Spreadsheet Wizard, note the vertical bars in the Sample data box. Click Cancel to return to the Database window.

In Microsoft Access 2.0, click OK in the Import Spreadsheet Options dialog box. Close the Select File dialog box and open the new table. Note the vertical bars between the data elements of each field value.</li></ol>

<div class="references_section">