Hi,

I have the following VBA code which just is some simple For Loops and can
get the data to be written to Excel cells. But how do I get it to write to a
text editor, such as WordPad or NotePad? I need to do this rather than output
the number to Excel as there insufficient rows in Excel to hold all the data.

Sub counter()

i = 0

For a = 1 To 18
For b = 1 To 18
For c = 1 To 18
For d = 1 To 18
For e = 1 To 18
For f = 1 To 18
i = i + 1
Worksheets("Sheet1").Cells(i, 1) = a
Worksheets("Sheet1").Cells(i, 2) = b
Worksheets("Sheet1").Cells(i, 3) = c
Worksheets("Sheet1").Cells(i, 4) = d
Worksheets("Sheet1").Cells(i, 5) = e
Worksheets("Sheet1").Cells(i, 6) = f
Worksheets("Sheet1").Cells(i, 8) = i
Next
Next
Next
Next
Next
Next

End Sub

Thanks.

Stuart

Hi Stuart,

In my experience I have always gone around collecting copies in word of
information on coding VBA I think might be useful.

You will find the piece below which deals with I/O with VBA.

Hope its useful

Rob

The first thing that you may want to do with a file is to open it. This
operation is performed with the Open command:

Open <filename For <mode As <filenumber

filename can be any valid path to the file. All of these are valid paths:

"myfile.dat"
"mydir\myfile.txt"
"c:\mydir\myfile.bin"
"..\myfile.bmp"

<mode specifies the method by which you want to work with this file. There
are 5 file modes:

-Input: This mode allows you to read text item by item from a text file.
This mode is useful exclusively for plain text (human-readable) files.
-Output: This mode allows you to write text item by item to a text file. The
resulting file is readable in the Input mode. The file is erased when opened
in this mode.
-Append: This mode is the same as Output, but the file is not erased.
Rather, writing starts at the end of the file. Useful for log files, among
other things.
-Binary: Binary access will read however many bytes is necessary to fill
the provided variable. It can read and write to any arbitrary byte. This is
useful for reading some data files, such as image files. You specify
your position in the file in Bytes.
-Random: Is similar to Binary, but only allows reading and writing to record
boundaries. The size of the record is specified in the open statement, or
defaults to 128 bytes if not specified. You specify your position in the
file by record number.

<filenumber is an identifying number for the file. This is passed to
subsequent statements to tell them which file to perform their operation on.
This also means that you can have many files open at once. When dealing with
a lot of files, it may not be easy to know what filenumbers are in use. To
help with this, we have the FreeFile() function. By storing the return of
this function, we get the lowest unused file number. Here is an example of
opening a file using a call to FreeFile():

sub FreeFile()
Dim FF As Integer
FF=FreeFile()
Open "myfile.txt" For Input As FF
End Sub

Reading & Writing

Text files are accessed with the Input and Print statements. In older
versions of BASIC, these statements included a # sign; this convention has
been dropped. The Input and Output statements have this syntax:

Input <filenumber,<variable
Print <filenumber,<expression

In both statements, <filenumber is the number of the open file. If there is
no open file with the number passed, an error will result.

Input reads from the file into the variable passed as <variable. Strings
are read separated by commas or grouped by quotes. If you read a numeric
data type (integer, single, etc) VB will start reading from the end of white
space (newlines, tabs, and spaces) until it finds a non-digit. Thus, if
there are characters instead of digits, 0 will be read. This also means that
more than 1 number can be on a line. User defined types and classes cannot
be read from text files, as there is no way for VB to figure out how they
are read.

Print writes the result of <expression on a new line in the file. An
expression is a literal ("word", 99, 1.2, etc), a variable, or a function
(2+2, string1 & string2, etc). The following are all valid expressions:

1+2*3
myvar+2
str$(myinteger) & mystring
"this is a literal"
1002

Sometimes we want to read a particular number of characters from a file into
a string, regardless of how they are broken among lines. This is useful for
reading an entire file at once, among other things. The Input function is
used to do this:

Input(<number,<filenumber)

<number specifies how may characters to get, and <filenumber is the index
that you assigned to the file when you opened it. This is a function, so you
assign its return to a variable.

Another statement for writing to text files is Write:

This statement works in the same way as Print, except that you can pass
multiple things as <output, and certain formatting changes are made when
the data is written to the file. These changes can be summarized as follows:

-Individual pieces of data are separated by commas
-Strings are enclosed in quotation marks
-Dates and booleans are converted to a format readable in any locale (other
languages and data formats)

These are some example Write statements, and what they write to a file:

Write FF,99,100,75,"foo","bar",23.4
'99,100,75,"foo","bar",23.4
Write FF,True,MyString,45.3
'#TRUE#,"contents of MyString",45.3
Write FF,True,12.4,False
'#TRUE#,12.4,#FALSE#

So, the principle difference between Print and Write, is that Print is best
suited for creating a report, or some other document that someone would read
with an editor or print out. Write was made for saving data and reading it
back into the computer, not producing a document. Of
course, you can use print to save data and read it back, but depending
on the data, can be more complicated. If you had 3 strings that made up an
entry in an address book for instance, it is easier to put those three
strings on a single line in your file, using Write:

Write FF, String1, String2, String3

In that example, if one of your strings had a comma in it, then the line
written with Print would not read back in correctly since Input would see
more than 3 strings in the line. The line written using Write would read
back correctly because the strings will have double quotes around it. And if
perchance one of the Strings had a double quote in the string, the Write
will still work because it will put "" (two double quotes) in the string
when written to the file (two double quotes together is how you designate a
quote character within a string).

It should be mentioned that when using Print, just like it does when
printing to a form, or printing to a picturebox, a comma will space the
printed data into columns and a semicolon will suppress the CRLf, so that
further prints are on the same line.

Closing the File

While VB will close all files when your program ends, it is a good idea to
close a file when you are done with it (you can always open it again later).
One reason for this is that file numbers are global. You can pass a file
number as an argument to other modules and classes, and they can operate on
that file as though that module had opened it. Another reason is that should
your program close in an uncontrolled manner (sudden process death, hard
reboot, etc) damage may occur to the file. The Close statement works as
follows:

Close <filenumber

If <filenumber isn't the number of an open file, no error will be thrown.
Thus, there is never a danger of getting an error while closing a file.
If you don't specify a filenumber, then all open files are closed. You can
also specify multiple filenumbers to close multiple files, but not
necessarily all.

Stuart,

The main problem is NotePad, WordPad and Word cannot read a file as large as
the one you are creating. Your a-f for/next loops generate 18^6 or a little
over 34 million records. In my testing, just running empty loops took 2-3
seconds, just loading the variables, took almost 3 minutes, and writing a
random access file with the 34+ million records took about 10 minutes.

The next problem is reading the file you just created. I tried the above and
even debug from the dos shell. None work due to file size. You will have to
construct a process to read from the random file to display the data as you
want it. This could involve viewing it in a user form, or just selecting and
loading segments of the file directly onto the sheet. Reading from the file
is not much more difficult than creating it in the first place. The code
below is crude and does not check for an existing file and simply overwrites
what is there. Not sure what your needs are. Give it a shot.

Also, I added progress indication in the upper corner of sheet 1. You may or
may not want to keep this. I like to have a visual sense that something is
going on. I added a remark after each line dedictaed to monitoring progress.

I added the 3 lines below to the click event of a command button on sheet 1.

Cells(2, 1).Value = "Start Time = " & Now() 'progress
BuildFile
Cells(3, 1).Value = "Finish Time = " & Now() 'progress

'and then created a new module and added the code below. Just paste this
into the new module.

Type Record ' this must go in the general section, not in the procedure
RecA As String * 2
RecB As String * 2
RecC As String * 2
RecD As String * 2
RecE As String * 2
RecF As String * 2
RecI As String * 8
End Type

Sub BuildFile()

Dim a As Integer, b As Integer, c As Integer, d As Integer
Dim e As Integer, f As Integer, i As Long
Dim MyRecord As Record
Dim TotalRecords As Long, PercentComplete As Double

i = 0
TotalRecords = 18 ^ 6 ' progess
PercentComplete = i / TotalRecords 'progress
Cells(1, 1).Value = PercentComplete 'progress

Open "C:\Test.txt" For Random As 1 Len = Len(MyRecord)
'put wherever you want it

For a = 1 To 18
For b = 1 To 18
For c = 1 To 18
For d = 1 To 18
For e = 1 To 18
For f = 1 To 18
i = i + 1
MyRecord.RecA = Format(a)
MyRecord.RecB = Format(b)
MyRecord.RecC = Format(c)
MyRecord.RecD = Format(d)
MyRecord.RecE = Format(e)
MyRecord.RecF = Format(f)
MyRecord.RecI = Format(g)
Put #1, i, MyRecord
Next
Next
Next
Next
PercentComplete = i / TotalRecords ' progress - change which loop it
is
Cells(1, 1).Value = PercentComplete 'progress - in to alter update
freq.
Cells(4, 1).Value = i 'progress - not too deep, it will really slow
it down
Next
Next

Close 1

End Sub

'Roy

--
(delete .nospam)




"stuart" wrote:

Hi,

I have the following VBA code which just is some simple For Loops and can
get the data to be written to Excel cells. But how do I get it to write to a
text editor, such as WordPad or NotePad? I need to do this rather than output
the number to Excel as there insufficient rows in Excel to hold all the data.

Sub counter()

i = 0

For a = 1 To 18
For b = 1 To 18
For c = 1 To 18
For d = 1 To 18
For e = 1 To 18
For f = 1 To 18
i = i + 1
Worksheets("Sheet1").Cells(i, 1) = a
Worksheets("Sheet1").Cells(i, 2) = b
Worksheets("Sheet1").Cells(i, 3) = c
Worksheets("Sheet1").Cells(i, 4) = d
Worksheets("Sheet1").Cells(i, 5) = e
Worksheets("Sheet1").Cells(i, 6) = f
Worksheets("Sheet1").Cells(i, 8) = i
Next
Next
Next
Next
Next
Next

End Sub

Thanks.

Stuart

Why put all that in a text file ?

Since it is just a regular nested loop the output is completely
predictable, so whatever could be done with the file could also be
done with the generating code....

Tim.


"stuart" wrote in message
...
Hi,

I have the following VBA code which just is some simple For Loops
and can
get the data to be written to Excel cells. But how do I get it to
write to a
text editor, such as WordPad or NotePad? I need to do this rather
than output
the number to Excel as there insufficient rows in Excel to hold all
the data.

Sub counter()

i = 0

For a = 1 To 18
For b = 1 To 18
For c = 1 To 18
For d = 1 To 18
For e = 1 To 18
For f = 1 To 18
i = i + 1
Worksheets("Sheet1").Cells(i, 1) = a
Worksheets("Sheet1").Cells(i, 2) = b
Worksheets("Sheet1").Cells(i, 3) = c
Worksheets("Sheet1").Cells(i, 4) = d
Worksheets("Sheet1").Cells(i, 5) = e
Worksheets("Sheet1").Cells(i, 6) = f
Worksheets("Sheet1").Cells(i, 8) = i
Next
Next
Next
Next
Next
Next

End Sub

Thanks.

Stuart