Version difference when opening text files?
 

Background: Users create a comma-delimited text file using specialised
software, that is imported later into a validated Excel spreadsheet for
processing. The structure of the text file is always identical, but the
field content changes. The import macro opens the file using

vaFile = Application.GetOpenFilename(FileFilter:="Text Files (*.txt; *.csv),
*.txt; *.csv", MultiSelect:=False)

Workbooks.OpenText Filename:=vaFile, Comma:=True

then copies the contents into the processing workbook. Up to now, the users
could either save the source text file as .TXT or .CSV with identical results.

Now however, some users have moved from Excel 2000 to Excel 2003. If they
save the source as .CSV, no change, but if they save as .TXT, some blank rows
are not copied through, and it throws all the processing formulae out of
sync. The problem seems to stem from places in the text file where there are
two "line-feed" characters next to each other (the kind that present as
little boxes in notepad).

To make it worse, the source text files are saved to a write-once area, and
can't be amended. I'm not even supposed to allow the copied in data to be
amended. Since I can't do anything to change the source text files, is there
anything I can do to the macro code to compensate? (p.s. yes, I know I can
tell the users to save as .CSV, but if they forget the data will become
almost inaccessible)

Thanks, Geoff.

Version difference when opening text files?
 

Use Chip Pearson's code to read in the file:

http://www.cpearson.com/excel/imptext.htm import/export text files

--
Regards,
Tom Ogilvy


"Geoff C" wrote in message
...
Background: Users create a comma-delimited text file using specialised
software, that is imported later into a validated Excel spreadsheet for
processing. The structure of the text file is always identical, but the
field content changes. The import macro opens the file using

vaFile = Application.GetOpenFilename(FileFilter:="Text Files (*.txt;
*.csv),
*.txt; *.csv", MultiSelect:=False)

Workbooks.OpenText Filename:=vaFile, Comma:=True

then copies the contents into the processing workbook. Up to now, the
users
could either save the source text file as .TXT or .CSV with identical
results.

Now however, some users have moved from Excel 2000 to Excel 2003. If they
save the source as .CSV, no change, but if they save as .TXT, some blank
rows
are not copied through, and it throws all the processing formulae out of
sync. The problem seems to stem from places in the text file where there
are
two "line-feed" characters next to each other (the kind that present as
little boxes in notepad).

To make it worse, the source text files are saved to a write-once area,
and
can't be amended. I'm not even supposed to allow the copied in data to be
amended. Since I can't do anything to change the source text files, is
there
anything I can do to the macro code to compensate? (p.s. yes, I know I can
tell the users to save as .CSV, but if they forget the data will become
almost inaccessible)

Thanks, Geoff.

Version difference when opening text files?
 

Tom, tried this, but it doesn't work. It doesn't seem to recognise the line
feed characters, so I end up with one big row. It's these characters that
seem to be the problem, but I can't avoid them being there.

"Tom Ogilvy" wrote:

Use Chip Pearson's code to read in the file:

http://www.cpearson.com/excel/imptext.htm import/export text files

--
Regards,
Tom Ogilvy


"Geoff C" wrote in message
...
Background: Users create a comma-delimited text file using specialised
software, that is imported later into a validated Excel spreadsheet for
processing. The structure of the text file is always identical, but the
field content changes. The import macro opens the file using

vaFile = Application.GetOpenFilename(FileFilter:="Text Files (*.txt;
*.csv),
*.txt; *.csv", MultiSelect:=False)

Workbooks.OpenText Filename:=vaFile, Comma:=True

then copies the contents into the processing workbook. Up to now, the
users
could either save the source text file as .TXT or .CSV with identical
results.

Now however, some users have moved from Excel 2000 to Excel 2003. If they
save the source as .CSV, no change, but if they save as .TXT, some blank
rows
are not copied through, and it throws all the processing formulae out of
sync. The problem seems to stem from places in the text file where there
are
two "line-feed" characters next to each other (the kind that present as
little boxes in notepad).

To make it worse, the source text files are saved to a write-once area,
and
can't be amended. I'm not even supposed to allow the copied in data to be
amended. Since I can't do anything to change the source text files, is
there
anything I can do to the macro code to compensate? (p.s. yes, I know I can
tell the users to save as .CSV, but if they forget the data will become
almost inaccessible)

Thanks, Geoff.

Version difference when opening text files?
 

Did you specify the proper delimiters?

If you want to send me a copy of the text file, I will adjust the code to
process it.



--
Regards,
Tom Ogilvy

"Geoff C" wrote in message
...
Tom, tried this, but it doesn't work. It doesn't seem to recognise the
line
feed characters, so I end up with one big row. It's these characters that
seem to be the problem, but I can't avoid them being there.

"Tom Ogilvy" wrote:

Use Chip Pearson's code to read in the file:

http://www.cpearson.com/excel/imptext.htm import/export text files

--
Regards,
Tom Ogilvy


"Geoff C" wrote in message
...
Background: Users create a comma-delimited text file using specialised
software, that is imported later into a validated Excel spreadsheet
for
processing. The structure of the text file is always identical, but
the
field content changes. The import macro opens the file using

vaFile = Application.GetOpenFilename(FileFilter:="Text Files (*.txt;
*.csv),
*.txt; *.csv", MultiSelect:=False)

Workbooks.OpenText Filename:=vaFile, Comma:=True

then copies the contents into the processing workbook. Up to now, the
users
could either save the source text file as .TXT or .CSV with identical
results.

Now however, some users have moved from Excel 2000 to Excel 2003. If
they
save the source as .CSV, no change, but if they save as .TXT, some
blank
rows
are not copied through, and it throws all the processing formulae out
of
sync. The problem seems to stem from places in the text file where
there
are
two "line-feed" characters next to each other (the kind that present
as
little boxes in notepad).

To make it worse, the source text files are saved to a write-once
area,
and
can't be amended. I'm not even supposed to allow the copied in data to
be
amended. Since I can't do anything to change the source text files, is
there
anything I can do to the macro code to compensate? (p.s. yes, I know I
can
tell the users to save as .CSV, but if they forget the data will
become
almost inaccessible)

Thanks, Geoff.