This article was previously published under Q115576
Moderate: Requires basic macro, coding, and interoperability skills.
On This Page
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.
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.
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:
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.
For more information about importing spreadsheet data, search for
"spreadsheets, importing or linking" using the Microsoft Access for
Windows 95 Help Index.
Contact Microsoft Phone Numbers, Support Options and Pricing, Online Help, and more.
Customer Service For non-technical assistance with product purchases, subscriptions, online services, events, training courses, corporate sales, piracy issues, and more.
Newsgroups Pose a question to other users. Discussion groups and Forums about specific Microsoft products, technologies, and services.
Back to the top
