Keeping Excel Formatting Characteristics

[Bryce Dalley]

I'm importing DATA from excel into a memo field in Access.
My problem is that in Excel there is spacing that does not
convert to Access. It gives me squares and bold l's. I
know that this is because of the spacing that I've done
within Excel, but is there anyway I can get this spacing
within the cell to covert to access?

For example, In one excel cell I have the data

Process to eliminate problemAlt Enter)
-Communicate with HR (Alt Enter)
-Develop a compromise (Alt Enter)

When I bring this to Access it looks like this.

Process to eliminate problem:ll-Commnicate with HRll-
Develop a compromise.

All of the data is lumped together instead of nicely
spaced out.

If the data is imputted directly into access I can have
the proper spacing, but there has to be a way to keep the
formatting from an input file? Right?

Is there anyway that this formatting can convert to
Access. I'm using Access 97, but my company is upgrading
to XP.

Can anyone help?

 

[Ken Snell]

After you've imported the data, run an update query on the data. You'll need
to convert the Chr(10) character (Alt-Enter in EXCEL) to the combination of
Chr(13) & Chr(10) so that ACCESS will properly display the memo field's
data.

EXCEL uses the "line feed" character (Chr(10)) to make new lines in cells,
but ACCESS uses both the "carriage return" and "line feed" characters (in
that order) to make new lines in fields.

 

[Bryce Dalley]

How do I change that in the query? I'm not a VBA expert
so any other help would be great.

 

[Ken Snell]

Create a query (design view) based on the table where you put the imported
data. Change it to an update query (see the icon for type of query).

Put the memo field on the query grid. In the UpdateTo: cell, type this
expression (this assumes that you have ACCESS 2002):

Replace([NameOfMemoField], Chr(10), Chr(13) & Chr(10))

where NameOfMemoField is to be replaced with the actual name of the memo
field.

If you have ACCESS 2000 or earlier, you can't use Replace in the query
itself. In that case, use this expression:

RReplace([NameOfMemoField], Chr(10), Chr(13) & Chr(10))

and put this function in a regular module:

Public Function RReplace(strStringValue As String, strCurrString As String,
strNewString As String)
RReplace = Replace(strStringValue, strCurrString, strNewString)
End Function