Append Records to a Text File

[Randy]

I'm writing code that creates quite a few thousand records and
ultimately saves them into a TXT file. In some scenarios, my record
count exceeds the Excel row limit of 65,536. In these cases, of
course, I need to dump some of the records out of Excel into the text
file so that I don't overflow.

I have no problem saving the first set of records into the text file.
What I am wondering is if anybody has any ideas as to how I can append
the second set of records onto the same text file. Let's say that my
procedure creates 70,000 records. My algorithm might look like this:

For x = 1 to 3500
'create 1st record set
Next
ActiveWorkbook.SaveAs Filename:=OutputFile, FileFormat:=xlText
For x = 35001 to 70000
'create 2nd record set
Next
'Append data to first .txt file - This is where I am stuck

Is there a way to write these records into the text file without
bringing it back into Excel? I tried using Word, but it was
prohibitively slow and I don't know much about VBA in Word. I could
use Access, but nobody in our office besides me has any experience in
Access and this will be a shared app, so I'm trying to keep it as
simple as I can.

Thanks,
Randy Eastland

[Dick Kusleika]

Randy: Where is data coming from? Maybe it never needs to end up on an
Excel spreadsheet and just passes through VBA. As to your specific
question, you can write to a text file without the SaveAs method. See

http://www.dicks-blog.com/archives/2004/11/09/roll-your-own-csv/

for examples of using the Open, Print, and Close keywords to write to a text
file (it doesn't have to be CSV) . To append lines, you'll need a line like

Open sFname For Append As lFnum

That will add whatever you want to the end of the file. If you need more
specific help, post back.

--
Dick Kusleika
MVP-Excel
www.dailydoseofexcel.com

[deano]

http://groups.google.com/group/microsoft.public.excel.programming/browse_frm/thread/e42ef0ed8fe538fa/faee765000368b26#faee765000368b26

Randy, txt files can obviously be as long as you need them. Any chance
you can just use print statement to write one record at a time as
below:

Sub records_txtfile()
Dim ff As Integer
ff = FreeFile()
Open "C:\textfile.txt" For Output As ff
For r = Firstrecord To Lastrecord
' generate your record here
For c = FirstCol To lastcol
Print #ff, Cells(r, c);
Next c
Print #ff, 'blank line
Next r
Close ff
End Sub

[Patrick Molloy]

I'd suggest

Open "C:\textfile.txt" For Append As ff

since he may want to add data to an existing file

[Randy]

Thanks. I think that your suggestion is on the right track. However,
I've never worked with this type of code, so I have some questions, if
you don't mind.

1. Can you explain what the line ff = FreeFile() does? I searched the
group but can't figure this out.
2. The line "Open "C:\textfile.txt" For Output As ff ". Does this
file have to already exist or will this code create a new file if it
doesn't?
3. By the time that I would get to this subroutine, my records will
have already been concatenated into a single cell on each row. How
would the nested for-next loop change in this scenario?
4. Finally, will the line "Close ff" save the file or do I need to do
that in a separate command?

Thanks very much. Sorry for the ignorance.
Randy

[Dick Kusleika]

Randy wrote:
> 1. Can you explain what the line ff = FreeFile() does? I searched
> the group but can't figure this out.

Freefile is a function that gets the next available file handle. You can
specify the file handle yourself like

ff=1

but by using FreeFile you don't have to worry if 1 is already reserved.

> 2. The line "Open "C:\textfile.txt" For Output As ff ". Does this
> file have to already exist or will this code create a new file if it
> doesn't?

It will create a new file if it doesn't exist. It will erase the contents
of a file if it does exist. If you want to add to an existing file, you
need to use Append instead of Output.

> 3. By the time that I would get to this subroutine, my records will
> have already been concatenated into a single cell on each row. How
> would the nested for-next loop change in this scenario?

Dim rCell As Range
Dim rConcatCells As Range

rConcatCells = Sheet1.Range("D1:D20")

For Each rCell In rConcatCells.Cells
Print #ff, rCell.Value
Next rCell

> 4. Finally, will the line "Close ff" save the file or do I need to do
> that in a separate command?

The file is "saved" in real time. The Close command simply releases the
file handle so another app can open it, like notepad. No need to do a
separate save.

[Randy]

Excellent, Excellent, Excellent! This works perfectly. And it runs
fairly quickly, too.

Thanks to everyone, particularly Deano and Dick. I was really stuck
for a good solution.

Randy