Form Fields with Returns export to excel problem

[Guest]

Hi
I have set up a template with form fields and I have several feilds that the
user enters several lines of data such as address .

The user then saves the form as data only to a text file that is imported
into an Excel workbook.

All works ok in so far as the deliminated text imports with each field in a
new column in the same row of the spreadsheet - which is great - except the
feilds where the user has user has created a multiline responce using the
enter key as in an address between lines of the address. In this instance the
import deliminated text wizard shoves the 2nd 3rd etc lines into new lines in
the spreadsheet - in column A which is not even inline with the column the
first line of data apears in for example;
The data files in from column A and the address data is to apear in Column G
- line one of the column ends up here in row 1 but the 2nd address line ends
up in row 2 clumn A

I have a form with over 80 form feilds to fill in and at least a dozen of
these will have multiline responces.

I dont want to have to seperate all the lines of the address out to seperate
feilds as some are not addresses but an area for the user to list
interests,activities etc and therefore can be any length of list.

Any help greatfully recieved

GreenBoy

 

[Cindy M -WordMVP-]

Hi =?Utf-8?B?R3JlZW5Cb3k=?=,

The problem you're encountering here is that
1. Excel doesn't use the same ANSI character to specify new lines as other
applications usually do.

2. Even if you used the correct character (Chr$10), Excel's text import will
remove it

Your best bet would be to use some VBA code to first process the text file,
substituting some other character (that the user won't be typing) for the ENTER
(Chr$(13) when coming from Word). Then, once the data has been imported into
Excel, use Find/Replace (again via VBA) to replace the substituted character
with Chr$(10).

An Excel programming group should be able to help you set this up so that you
can use a single macro to do the entire text-file-import.

I have set up a template with form fields and I have several feilds that the
user enters several lines of data such as address .

The user then saves the form as data only to a text file that is imported
into an Excel workbook.

All works ok in so far as the deliminated text imports with each field in a
new column in the same row of the spreadsheet - which is great - except the
feilds where the user has user has created a multiline responce using the
enter key as in an address between lines of the address. In this instance the
import deliminated text wizard shoves the 2nd 3rd etc lines into new lines in
the spreadsheet - in column A which is not even inline with the column the
first line of data apears in for example;
The data files in from column A and the address data is to apear in Column G
- line one of the column ends up here in row 1 but the 2nd address line ends
up in row 2 clumn A

I have a form with over 80 form feilds to fill in and at least a dozen of
these will have multiline responces.

I dont want to have to seperate all the lines of the address out to seperate
feilds as some are not addresses but an area for the user to list
interests,activities etc and therefore can be any length of list.

Cindy Meister
INTER-Solutions, Switzerland
http://homepage.swissonline.ch/cindymeister (last update Jun 8 2004)
http://www.word.mvps.org

This reply is posted in the Newsgroup; please post any follow question or reply
in the newsgroup and not by e-mail