Recognising New lines in memo fields imported from Excel 
Author Message
 Recognising New lines in memo fields imported from Excel

Hello everyone,

My problem is that I am experiencing incompatibility between Excel and
Access when I import memo fields from Excel spreadsheet tables into
Microsoft Access.

Access uses CTRL Enter to insert a newline into the text in a cell, whereas
Excel uses ALT Enter for the same function.

When mail merging fields from Access or Excel Word recognises both standards
but Access does not recognise the newline inserted by Excel and does not
format new lines as intended on Access forms and reports.

How do I get my newlines entered in Excel ( about 2000 records a week) to be
recognised by Access? Am I missing something obvious or has anyone else
found a way round this?

Any ideas?



Tue, 12 Jul 2005 06:07:02 GMT  
 Recognising New lines in memo fields imported from Excel

Quote:

>Hello everyone,

>My problem is that I am experiencing incompatibility between Excel and
>Access when I import memo fields from Excel spreadsheet tables into
>Microsoft Access.

>Access uses CTRL Enter to insert a newline into the text in a cell, whereas
>Excel uses ALT Enter for the same function.

>When mail merging fields from Access or Excel Word recognises both standards
>but Access does not recognise the newline inserted by Excel and does not
>format new lines as intended on Access forms and reports.

>How do I get my newlines entered in Excel ( about 2000 records a week) to be
>recognised by Access? Am I missing something obvious or has anyone else
>found a way round this?

Not at all obvious.  Actually, it's not how you type the new
line using Alt vs Ctrl keys, it's what character codes are
stored in the string.  Access places a Cr and an Lf in the
string while Excel only uses Lf.

If you know that a string came from Excel, you can use the
Replace function to change the Lf to Cr Lf.

accessvar = Replace(excelvar, Chr(10), Chr(13) & Chr(10))

But don't ever do this twice on the same string as it will
keep adding Cr characters every time you do it.

--
Marsh
MVP [MS Access]



Tue, 12 Jul 2005 06:55:50 GMT  
 Recognising New lines in memo fields imported from Excel
Thank you for your help, Marshall. I have tested a little subroutine which
seems to do the job and I shall integrate it into my import routine to
ensure that it runs once only on the Excel input.

I seem to recall this particular anomaly first arising when Adam and Eve
first invented the ASCII code... A pity that Microsoft Office should
perpetuate it.

A flag on TransferSpreadsheet would be a much cleaner solution - is there a
way we could suggest this to the Microsoft Office Development team?

Quote:
----- Original Message -----

Newsgroups: microsoft.public.access.modulesdaovba
Sent: Thursday, January 23, 2003 10:55 PM
Subject: Re: Recognising New lines in memo fields imported from Excel


> >Hello everyone,

> >My problem is that I am experiencing incompatibility between Excel and
> >Access when I import memo fields from Excel spreadsheet tables into
> >Microsoft Access.

> >Access uses CTRL Enter to insert a newline into the text in a cell,
whereas
> >Excel uses ALT Enter for the same function.

> >When mail merging fields from Access or Excel Word recognises both
standards
> >but Access does not recognise the newline inserted by Excel and does not
> >format new lines as intended on Access forms and reports.

> >How do I get my newlines entered in Excel ( about 2000 records a week) to
be
> >recognised by Access? Am I missing something obvious or has anyone else
> >found a way round this?

> Not at all obvious.  Actually, it's not how you type the new
> line using Alt vs Ctrl keys, it's what character codes are
> stored in the string.  Access places a Cr and an Lf in the
> string while Excel only uses Lf.

> If you know that a string came from Excel, you can use the
> Replace function to change the Lf to Cr Lf.

> accessvar = Replace(excelvar, Chr(10), Chr(13) & Chr(10))

> But don't ever do this twice on the same string as it will
> keep adding Cr characters every time you do it.

> --
> Marsh
> MVP [MS Access]



Fri, 15 Jul 2005 06:24:01 GMT  
 Recognising New lines in memo fields imported from Excel

Quote:

>Thank you for your help, Marshall. I have tested a little subroutine which
>seems to do the job and I shall integrate it into my import routine to
>ensure that it runs once only on the Excel input.

>I seem to recall this particular anomaly first arising when Adam and Eve
>first invented the ASCII code... A pity that Microsoft Office should
>perpetuate it.

>A flag on TransferSpreadsheet would be a much cleaner solution - is there a
>way we could suggest this to the Microsoft Office Development team?

I'l try to remember to do that, but don't hold your breath.
Even if they agreed to do it, it's too late for Office 11
and I'm guessing that we won't see Office 12 until 2005 or
later.
--
Marsh
MVP [MS Access]
Quote:
>----- Original Message -----

>Newsgroups: microsoft.public.access.modulesdaovba
>Sent: Thursday, January 23, 2003 10:55 PM
>Subject: Re: Recognising New lines in memo fields imported from Excel


>> >Hello everyone,

>> >My problem is that I am experiencing incompatibility between Excel and
>> >Access when I import memo fields from Excel spreadsheet tables into
>> >Microsoft Access.

>> >Access uses CTRL Enter to insert a newline into the text in a cell,
>whereas
>> >Excel uses ALT Enter for the same function.

>> >When mail merging fields from Access or Excel Word recognises both
>standards
>> >but Access does not recognise the newline inserted by Excel and does not
>> >format new lines as intended on Access forms and reports.

>> >How do I get my newlines entered in Excel ( about 2000 records a week) to
>be
>> >recognised by Access? Am I missing something obvious or has anyone else
>> >found a way round this?

>> Not at all obvious.  Actually, it's not how you type the new
>> line using Alt vs Ctrl keys, it's what character codes are
>> stored in the string.  Access places a Cr and an Lf in the
>> string while Excel only uses Lf.

>> If you know that a string came from Excel, you can use the
>> Replace function to change the Lf to Cr Lf.

>> accessvar = Replace(excelvar, Chr(10), Chr(13) & Chr(10))

>> But don't ever do this twice on the same string as it will
>> keep adding Cr characters every time you do it.

>> --
>> Marsh
>> MVP [MS Access]



Fri, 15 Jul 2005 11:24:41 GMT  
 
 [ 4 post ] 

 Relevant Pages 

1. Runtime Lines on a memo field are dropped on new records

2. How to import to Outlook new fields from Excel

3. Append memo field to a different memo field

4. Memo fields - problems reporting 2 memo fields

5. Import a FoxPro Memo field into Outlook

6. Import Access memo field into Outlook

7. Import to Memo field

8. Importing Text into Jet memo field--please help

9. Insert a line break into a memo field?

10. Breaking memo field into lines of 79 characters.

11. Insert line break in memo field w/ code

12. AC97-Line Feeds in memo field?

 

 
Powered by phpBB® Forum Software