How to copy non-contiguous columns to a text file

[GS]

joeu2004 was thinking very hard :

Funny you mention that. That was indeed my original design. But I
discovered that alters the current workbook name; and from what I read, the
only way to name it back again is to do a SaveAs with the original name. Too
presumptuous, not to mention other issues.

(If anyone knows how to do something like a SaveAs without changing the
workbook name, I'm all ears. I'm thinking of perhaps some object methods --
like FileSystemObject -- that would write the worksheet or selection to a
tab-delimited text file.)

I thought the objective was to let Jennifer choose the filename. I'm
not understanding why you want to preserve the workbook name if you go
with using a new wkb (with 1 wks) over using a tmp wks.

Right. That is what I concluded as well. And another benefit is: it might
avoid triggering a recalculation cycle of the current workbook, which I
believe adding and deleting a worksheet does. (In my implemenation, that
happens only once when I restore oldCalc, if it was Automatic.)

But at the point, I was unsure what the performance issues might be; and I
wrestled with the question of whether to create the new workbook in the same
instance of Excel or in a new instance, which further isolate any other
unforeseen side-effects.

I agree in general, though I feel using the current instance is easier
to manage (without much concern for unforeseen issues given the context
of the task).

In the final analysis, I felt that I had worked long enough on the
implementation, and it would be better to defer such changes (and
investigations) until Jennifer provides feedback, if any, about the behavior
and performance of the current implementation.
Ditto!


Joeu2004 wrote:
I do this first because it is fun, second for my own
edification, and third to help others -- although it is a close tie among all
three.

This is mutual.

 

[joeu2004]

After-thought....

I also suggest you end the line with a semi-colon so
the file doesn't contain a blank line at the end.

Garry wrote later:

I wonder if using SaveAs and specifying ".txt" as the
output filetype would be any faster than using VBA.
If so then using a new wkb instead of a temp wks would
be appropriate.

The two suggestions are inconsistent. Ironically, SaveAs produces a text
file with an empty line at the end. Probably another reason why I had not
compunctions about doing the same with Print# statements ;-).

Anyway, I implemented this suggestion in "copy to textfile v2.xls" at
http://www.box.net/shared/3qu5oikbfgeoryn6prdy.

I do prefer it. I expect is more efficient. As I said, this was my
original design; but I had only created a new worksheet, not a new workbook.
The latter was an after-thought, but by then I was burned out on the task.
I thought the change would be more difficult than it proved to be.


I wrote previously:

it might avoid triggering a recalculation cycle of
the current workbook, which I believe adding and
deleting a worksheet does.

Empirical tests (with a volatile UDF) indicates that this is __not__
correct, that is about workbooks [*]. Happy to be wrong about that .

 

[joeu2004]

Re-download the file at the same link,
http://www.box.net/shared/tgi09xdin8nr77jb36bd.

And download the file "copy to textfile v2.xls" at
http://www.box.net/shared/3qu5oikbfgeoryn6prdy.

It incorporates a suggestion that Garry made, namely to create the temporary
worksheet in a temporary workbook instead of your workbook.

I presume that this will perform better, perhaps a lot better because Excel
writes to the file.

But you might want to compare the behavior and performance of the two
versions just to be sure.

 

[Rick Rothstein]

Ironically, SaveAs produces a text file with an empty

line at the end. Probably another reason why I had not compunctions about
doing the same with Print# statements ;-).

You can suppress the "empty line" (actually, a trailing newline character
sequence) by affixing, not concatenating, a semi-colon at the end of the
last Print # statement. So, if you were writing a loop, it could look
something like this...

MaxRow = 1000
For X = 1 To MaxRow
If X = MaxRow Then
Print #1, ArrayOfText(X);
Else
Print #1, ArrayOfText(X)
End If
Next

or, if you concatenated all the text to be written to the file into a single
String variable, then it would look like this...

Print #2, AllTheText;

Rick Rothstein (MVP - Excel)

 

[GS]

You can suppress the "empty line" (actually, a trailing newline character
sequence) by affixing, not concatenating, a semi-colon at the end of the last
Print # statement. So, if you were writing a loop, it could look something
like this...

MaxRow = 1000
For X = 1 To MaxRow
If X = MaxRow Then
Print #1, ArrayOfText(X);
Else
Print #1, ArrayOfText(X)
End If
Next

or, if you concatenated all the text to be written to the file into a single
String variable, then it would look like this...

Print #2, AllTheText;

Rick Rothstein (MVP - Excel)

Hi Rick,
This is precisely the reason I substituted my first WriteDataToTextFile
routine with the one I now use, which allows me to dump large amounts
of data in 'blocks', the last block not being AppendMode.

Not using the semi-colon causes appended data to start one line below
the existing data. Using the semi-colon causes any appended text to
start after the last character on the last line. Since the files I
write to always replace any existing, I use 'Kill' in the calling proc
as appropriate so the write proc is a reusable write-to-file utility. I
also do similar for writing binary data since the output data is rarely
the same length as the previous data in the file. (ergo, always Kill &
replace)

 

[GS]

Sorry.., the name of the routine I use for [large amounts of] text is

WriteTextToFile

WriteDataToTextFile is the proc I use to 'Put' binary data into a file.

 

[joeu2004]

You can suppress the "empty line" (actually, a trailing
newline character sequence) by affixing, not concatenating,
a semi-colon at the end of the last Print # statement.

Obviously, this thread is getting to long, and things are getting
overlooked.

Garry already made that suggestion. I already acknowledge it and
implemented it.

The point of my "ironic" statement above was: Garry subsequently suggested
the use of SaveAs, which I prefer myself, but which leaves the very empty
last line that you and he are working so hard to avoid. If Garry likes
SaveAs, he must not be as bothered by the empty last line as he seemed to
be. (But that's really up to him to decide.)

In any case, __I__ am not so bothered by the empty last line. I was willing
to acquiesce to avoiding it when I was do the Print#s myself; then, there
was no good reason not to avoid it. But I think the fact that Excel writes
it with the SaveAs method is reasonable justification not to worry about it
if I choose to use SaveAs for other reasons.

Again, this is a judgment call, and reasonable people can disagree
reasonably.

 

[joeu2004]

Not using the semi-colon causes appended data to start
one line below the existing data. Using the semi-colon
causes any appended text to start after the last character
on the last line.

Yes. And for that reason, for Jennifer's purposes, I would opt for SaveAs
or Print# without trailing semicolon.

I might have misunderstood what you and Rick were saying. But in fact,
SaveAs and Print# without trailing semicolon do not cause an extra blank
line at the end of the file, as I thought you were saying.

The following loop reads exactly the intended number of lines when they are
written using SaveAs and Print# without trailing semicolon:

n = 0
Do Until EOF(fdIn)
n = n + 1
Line Input #fdIn, myLine
myLine = Format(n, "0000 ") & myLine
Debug.Print myLine
Loop

Moreover, Open For Append Access Write appends the next Print# after the
last line, as I intended.

That loop also reads the intended number of lines when they are written
using Print# with trailing semicolon. However, as you say, Open For Append
Access Write appends the next Print# to end of the last line instead of
following the last line, as I would prefer.

Therefore, for my purposes, Print# with trailing semicolon is not what I
want. No telling what Jennifer wants.

 

[joeu2004]

Re-download the file at the same link,
http://www.box.net/shared/tgi09xdin8nr77jb36bd.

Jennifer, sorry for the incessant postings and file updates.

But I wanted to let you know that if you did re-download that file before
the posting of this message, it contains an unintended design change that
may or may not be what you want.

At issue is whether the last line of the new text file ends with a newline
or not. It might make no difference to you, but I intended for there to be
a newline.

(I only wanted to avoid an extra blank line at the end, which it turns was
never a problem.)

So I re-updated the file "copy to textfile.txt" at the link above so it is
consistent with "copy to textfile v2.txt" at
http://www.box.net/shared/3qu5oikbfgeoryn6prdy, which simply uses a
different (better?) method for writing the text file.

I apologize for any confusion this is causing.

 

[GS]

joeu2004 explained on 7/21/2011 :

Obviously, this thread is getting to long, and things are getting overlooked.

Garry already made that suggestion. I already acknowledge it and implemented
it.

The point of my "ironic" statement above was: Garry subsequently suggested
the use of SaveAs, which I prefer myself, but which leaves the very empty
last line that you and he are working so hard to avoid. If Garry likes
SaveAs, he must not be as bothered by the empty last line as he seemed to be.
(But that's really up to him to decide.)

In any case, __I__ am not so bothered by the empty last line. I was willing
to acquiesce to avoiding it when I was do the Print#s myself; then, there was
no good reason not to avoid it. But I think the fact that Excel writes it
with the SaveAs method is reasonable justification not to worry about it if I
choose to use SaveAs for other reasons.

Again, this is a judgment call, and reasonable people can disagree
reasonably.

Joe,
As I explained in my reply to Rick, I have no problem with an empty
last line in the text file. That's why I reposted the 2nd version of my
WriteTextToFile proc, which is (as I explained) the one I use for
writing large amounts of text in 'blocks'. It allows me to choose how
the file is written up to the last block of text.

Given that, as you say, the last line is always empty (ie: newline)
under normal circumstances with all known methods of writing text files
via VB[A], the discussion of how to avoid the line was raised in the
case that the empty line was wanted. (My first post did not write the
empty line) Because of the way I handle data stored in text files via
arrays, it's just my preference to not include the empty line. Upon
realizing this is NOT the way write methods work by default, it
occurred to me to point out that it can be done either way.

Also, as I explained to Rick, I can easily strip all empty lines from
an array using VB's Filter function. At the end of the day IMO it's a
matter of preference. If you have no choice then preference is stripped
away! I like having choices!<g>

 

[GS]

joeu2004 presented the following explanation :

Yes. And for that reason, for Jennifer's purposes, I would opt for SaveAs or
Print# without trailing semicolon.

I might have misunderstood what you and Rick were saying. But in fact,
SaveAs and Print# without trailing semicolon do not cause an extra blank line
at the end of the file, as I thought you were saying.

The following loop reads exactly the intended number of lines when they are
written using SaveAs and Print# without trailing semicolon:

n = 0
Do Until EOF(fdIn)
n = n + 1
Line Input #fdIn, myLine
myLine = Format(n, "0000 ") & myLine
Debug.Print myLine
Loop

Debug.Print does not behave like IO Print#. Try writeing to file both
ways and you'll see what we mean about the semi-colon.

Therefore, for my purposes, Print# with trailing semicolon is not what I
want. No telling what Jennifer wants.

And so is why I posted v2 of my WriteTextToFile proc.

 

[joeu2004]

joeu2004 presented the following explanation :

Debug.Print does not behave like IO Print#.

You don't seem to understand: I am reading back the file that was written.
I am demonstrating that there is __no__ empty line at the end the file when
it is created with SaveAs or written with Print# without a trailing
semicolon. The line counter "n" demonstrates that.

Try writeing to file both ways and you'll see what we mean about the
semi-colon.

I did! I reported the results. I wrote:

* The following loop reads exactly the intended number of lines when they
are written using SaveAs and Print# without trailing semicolon.

* Moreover, Open For Append Access Write appends the next Print# after the
last line, as I intended.

* That loop also reads the intended number of lines when they are written
using Print# with trailing semicolon.

* However, as you say, Open For Append Access Write appends the next Print#
to end of the last line instead of following the last line, as I would
prefer.

How do you think I determined all that if not by writing to the file "both"
(all 3) ways?

We're beating a dead horse. I just wanted to set the record straight for
posterity.

 

[GS]

Sorry, Joe. I did not look at your revised files. I was only speaking
to the results I got when using my WriteTextToFile proc (posted as
WriteTextFileContents) both ways. I don't know what SaveAs...txt does
because I don't use it. I will look at your code and redo my tests so
we're both on the same page.

For posterity, my code results as follows...
Write with semi-colon and keyboarding Ctrl+End puts the caret after the
last character of the last line of text.

Write without semi-colon and keyboarding Ctrl+End puts the caret on a
new line below the last line of text.

Can't say why you're getting different results until I get a chance to
review your code. Perhaps has something to do with your 'Open For...'
line, that it results different behavior than my code does. My
assertions were regarding the code I posted.

 

[GS]

Well, I still don't understand the confusion we seem to be having.
Here's my results after testing...

Without semi-colon:
Open Filename For Append As #iNum: Print #iNum, Text
AND
ActiveWorkbook.SaveAs Filename:=fName, FileFormat:=xlText

...both create a text file with a newline inserted below the last line
of text. This is what I stated about NOT using semi-colon with I/O
Print#!

This...
vData = _
Split(ReadTextFileContents("C:\data.txt"),vbCrLf)
Debug.Print UBound(vData)

...returns 5 (0 to 5) = 6 lines (5 with text, 1 empty line)


With semi-colon:
Open Filename For Output As #iNum: Print #iNum, Text;

...creates a text file with no newline after the last line of text. This
is what I stated about using a semi-colon with I/O Print#!


This...
vData = _
Split(ReadTextFileContents("C:\data.txt"),vbCrLf)
Debug.Print UBound(vData)

...returns 4 (0 to 4) = 5 lines (5 with text, no empty line)

 

[GS]

Working example...

Lets say the file containing the 5 lines of text is Customer data:

vData(0) = Fieldnames
vData(1) = 1st record
...
vData(4) = last record
vData(5) = empty line

Now let's say customer record in vData(3) needs the address info
updated. For this I could handle it in two ways, but I'll demonstrate
how I usually do this and write the changed data back to the file.

sOldText = "123 Old Street|Oldtown|12345-0012"
sNewText = "234 New Street|Newtown|12356-0012"

Replace(vData(3), sOldText, sNewText)
WriteTextFileContents Join(vData, vbCrLf), "C:\data.txt"

vData = _
Split(ReadTextFileContents("C:\data.txt"),vbCrLf)
Debug.Print UBound(vData)

...returns 6 (0 to 6) = 7 lines (5 with text, 2 empty lines)

...and so for each time the file gets rewritten another empty line is
added. Thus, extra processing is required to remove the blanks so we
have only the actual data. meanwhile, this extra processing could be
eliminated by simply using the semi-colon in the write process.

 

[Clif McIrvin]

[ ]>

I guess that's where we will have to agree to disagree. ;-) This is a
quick and dirty solution to a short-term problem. I'm willing to spend
a
little time writing decent code and learning new techniques, but this
is
not production code that will ever be used by anyone other than me, so
I
am not willing to bother with "best practices". If it works, I'm
happy.

From what I've seen of your posts over time, I'd like to disagree with
your conclusion above. In the immediate context, your conclucion is
completely understandable ... and is, in fact, how I began to begin
working with VBA a little over 2 years ago.

The problem is that now I have accumulated several thousand lines of
code that does what I originally wanted of it; but when (6 months, or 18
months, or ....) later my needs change it is a painful and time
consuming process -- whereas had I invested the time to learn "best
coding practices" as I went along I'd now have a working, re-useable
code base.

Reality: there will always be some element of "there isn't time to do
this right" ... combined with the inescable problems of coping with the
necessary learning curve which will apply pressure towards your concept
above. I'd just like to encourage you to always keep your eye open for
best practices ... as the sooner they make sense to you and you begin to
apply them the faster your proficiency will increase.

 

[Clif McIrvin]

news:[email protected]...

[ ]

Thanks. That is welcomed feedback right now. Some golem in another
forum characterized my motivations as just "to sound clever". Nothing
could be further from the truth. I do this first because it is fun,
second for my own edification, and third to help others -- although it
is a close tie among all three.

SECOND!! to Garry's comment! I've suspected reasons 1 and 2 for some
time, now <grin>.

 

[Jennifer Murphy]

[ ]>

I guess that's where we will have to agree to disagree. ;-) This is a
quick and dirty solution to a short-term problem. I'm willing to spend
a
little time writing decent code and learning new techniques, but this
is
not production code that will ever be used by anyone other than me, so
I
am not willing to bother with "best practices". If it works, I'm
happy.

From what I've seen of your posts over time, I'd like to disagree with
your conclusion above. In the immediate context, your conclucion is
completely understandable ... and is, in fact, how I began to begin
working with VBA a little over 2 years ago.

The problem is that now I have accumulated several thousand lines of
code that does what I originally wanted of it; but when (6 months, or 18
months, or ....) later my needs change it is a painful and time
consuming process -- whereas had I invested the time to learn "best
coding practices" as I went along I'd now have a working, re-useable
code base.

Reality: there will always be some element of "there isn't time to do
this right" ... combined with the inescable problems of coping with the
necessary learning curve which will apply pressure towards your concept
above. I'd just like to encourage you to always keep your eye open for
best practices ... as the sooner they make sense to you and you begin to
apply them the faster your proficiency will increase.

My mom would distill all that down into, "You never have time to do it
right, but you always have time to do it over.".

She (and you) are right within limits. I am not a professional
programmer and never will be. I use VBA to get mainly to get things done
quickly. Almost all (95+%) are one time projects that will never be used
again. If I took the time to learn "best practices" for each project,
that would increase the time by at least a factor of 3. I do usually put
in fairly good comments, so, for the 5% that I might use again, I can
fairly quickly see what I was trying to do. Even if I have do complete
redo it, 5% x 2 is far less that 95% * 3. (Sorry, mom)

 

[GS]

My mom would distill all that down into, "You never have time to do it

right, but you always have time to do it over.".

She (and you) are right within limits. I am not a professional
programmer and never will be. I use VBA to get mainly to get things done
quickly. Almost all (95+%) are one time projects that will never be used
again. If I took the time to learn "best practices" for each project,
that would increase the time by at least a factor of 3. I do usually put
in fairly good comments, so, for the 5% that I might use again, I can
fairly quickly see what I was trying to do. Even if I have do complete
redo it, 5% x 2 is far less that 95% * 3. (Sorry, mom)

<FWIW>
Well, I think what Clif is trying to convey is the value of adopting
'best practices' AS YOU GO. That does require taking extra time to
learn! It simply requires recognizing examples of 'best practices' and
making them part of your work habit. You don't have to become a
professional programmer, but if that happens over time would it be all
so bad?

If using VBA is part of your job requirement, then it just makes sense
to me to be as proficient as you can with your work tools. Adopting
best practices over time AS YOU GO will not increase the time it takes
to complete a project one iota. In fact, (and I'm sure Clif will
agree..) the completion time for projects will noticeably reduce as
your skills evolve. A lot of this happens as a result of reusable code,
which is one of the benefits we enjoy as a side effect of adopting best
practices.

You have absolutely nothing to lose by being receptive to growing your
skills/knowledge with best practices, and considerable benefits to
gain.

 

[GS]

GS used his keyboard to write :

That does require taking extra time to learn!

Typo...

That doesn't require taking extra time to learn!