ACC: Imported MS Excel Carriage Returns Become Vertical Bars

• Microsoft Access versions 2.0, 7.0, 97
• Microsoft Excel 97 for Windows
• Microsoft Excel for Windows 95, version 7.0
• Microsoft Excel for Windows, versions 2.x, 3.x, 4.x, 5.0

SYMPTOMS

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

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:

1. Start Microsoft Access and open any database.



2. Create a module and type the following line in the Declarations section if it is not already there:

   Option Explicit




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




4. Create a new update query. Drag the field that you want converted to the query grid.



5. In the Update To row of the query grid, type the following expression:

   
         ChangeStr([<fieldname>],Chr$(10),Chr$(13) & Chr$(10),0) 

   where <fieldname> is the name of the field that you want to convert.



6. Run the query. Note that text containing vertical bars is divided into separate lines with carriage returns.

MORE INFORMATION

Steps to Reproduce Behavior

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




2. Save the spreadsheet and quit Microsoft Excel.



3. Start Microsoft Access and open any database.



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



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



6. In all versions, select the Microsoft Excel file that you created in step 1, and then click Import.



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

REFERENCES

For more information about importing spreadsheet data, search for "spreadsheets, importing or linking" using the Microsoft Access for Windows 95 Help Index.

Additional query words: pipes

Keywords : kbinterop
Version : WINDOWS:2.0,2.x,3.x,4.x,5.0,7.0,97
Platform : WINDOWS
Issue type : kbprb