ACC: Imported MS Excel Carriage Returns Become Vertical Bars

Last reviewed: August 29, 1997
Article ID: Q115576
The information in this article applies to:
  • Microsoft Access versions 2.0, 7.0, 97
  • Microsoft Excel 97 for Windows
  • Microsoft Excel for Windows 95, version 7.0
  • Microsoft Excel, 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:

          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.

STATUS

This feature is being reviewed and will be considered for inclusion in a future release of Microsoft Access.

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 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 if
Keywords : kbinterop PgmHowTo PgmParse
Version : 2.0 7.0 97
Platform : WINDOWS
Hardware : x86
Issue type : kbprb
Solution Type : kbcode


THE INFORMATION PROVIDED IN THE MICROSOFT KNOWLEDGE BASE IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND. MICROSOFT DISCLAIMS ALL WARRANTIES, EITHER EXPRESS OR IMPLIED, INCLUDING THE WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. IN NO EVENT SHALL MICROSOFT CORPORATION OR ITS SUPPLIERS BE LIABLE FOR ANY DAMAGES WHATSOEVER INCLUDING DIRECT, INDIRECT, INCIDENTAL, CONSEQUENTIAL, LOSS OF BUSINESS PROFITS OR SPECIAL DAMAGES, EVEN IF MICROSOFT CORPORATION OR ITS SUPPLIERS HAVE BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES. SOME STATES DO NOT ALLOW THE EXCLUSION OR LIMITATION OF LIABILITY FOR CONSEQUENTIAL OR INCIDENTAL DAMAGES SO THE FOREGOING LIMITATION MAY NOT APPLY.

Last reviewed: August 29, 1997
© 1998 Microsoft Corporation. All rights reserved. Terms of Use.