I have developed a macro that opens large text files (each has a list of
about 50,000 lines, almost all single numbers), reads them one at a time, and
places each one in a successive column.

The method I used is to open a text file, copy column A, paste it into the
next available column in the main sheet, close the text file, and repeat for
all text files in a folder (there could be up to 100 of them).

I am finding it could take about a minute to run this macro (for 35 files).

My question: will I gain speed by using the command "Open FileName For Input
As #FileNum"? (I would try it myself; since there's a learning curve for me,
I'm hoping to get somebody's input first.)

Thanks.

Maybe this function can help you. It will put the text in a 1-D array.
You can specify the last row higher than the reality, the On Error Resume
Next
will take care of that. Once you have your data in the array you can do
whatever you
want with it and it will be much faster.

Function OpenTextFileToArray(ByVal txtFile As String, _
ByRef arr As Variant, _
ByVal LBRow As Long, _
ByVal UBRow As Long) As Variant

Dim hFile As Long
Dim r As Long

hFile = FreeFile

Open txtFile For Input As #hFile

On Error Resume Next

For r = LBRow To UBRow
Input #hFile, arr(r)
Next r

Close #hFile

OpenTextFileToArray = arr

End Function


RBS



"hmm" wrote in message
...
I have developed a macro that opens large text files (each has a list of
about 50,000 lines, almost all single numbers), reads them one at a time,
and
places each one in a successive column.

The method I used is to open a text file, copy column A, paste it into the
next available column in the main sheet, close the text file, and repeat
for
all text files in a folder (there could be up to 100 of them).

I am finding it could take about a minute to run this macro (for 35
files).

My question: will I gain speed by using the command "Open FileName For
Input
As #FileNum"? (I would try it myself; since there's a learning curve for
me,
I'm hoping to get somebody's input first.)

Thanks.

Sounds interesting. What is "arr" passed to the function?

"RB Smissaert" wrote:

Maybe this function can help you. It will put the text in a 1-D array.
You can specify the last row higher than the reality, the On Error Resume
Next
will take care of that. Once you have your data in the array you can do
whatever you
want with it and it will be much faster.

Function OpenTextFileToArray(ByVal txtFile As String, _
ByRef arr As Variant, _
ByVal LBRow As Long, _
ByVal UBRow As Long) As Variant

Dim hFile As Long
Dim r As Long

hFile = FreeFile

Open txtFile For Input As #hFile

On Error Resume Next

For r = LBRow To UBRow
Input #hFile, arr(r)
Next r

Close #hFile

OpenTextFileToArray = arr

End Function


RBS



"hmm" wrote in message
...
I have developed a macro that opens large text files (each has a list of
about 50,000 lines, almost all single numbers), reads them one at a time,
and
places each one in a successive column.

The method I used is to open a text file, copy column A, paste it into the
next available column in the main sheet, close the text file, and repeat
for
all text files in a folder (there could be up to 100 of them).

I am finding it could take about a minute to run this macro (for 35
files).

My question: will I gain speed by using the command "Open FileName For
Input
As #FileNum"? (I would try it myself; since there's a learning curve for
me,
I'm hoping to get somebody's input first.)

Thanks.

Look at this example:

Sub Test()

Dim i As Long
Dim arr(1 To 10)

OpenTextFileToArray "C:\TestFile2.txt", arr, 1, 10

For i = 1 To 10
MsgBox arr(i)
Next

End Sub


Just try it out on file. You may have to adjust it according to your target
file.


RBS


"hmm" wrote in message
...
Sounds interesting. What is "arr" passed to the function?

"RB Smissaert" wrote:

Maybe this function can help you. It will put the text in a 1-D array.
You can specify the last row higher than the reality, the On Error Resume
Next
will take care of that. Once you have your data in the array you can do
whatever you
want with it and it will be much faster.

Function OpenTextFileToArray(ByVal txtFile As String, _
ByRef arr As Variant, _
ByVal LBRow As Long, _
ByVal UBRow As Long) As Variant

Dim hFile As Long
Dim r As Long

hFile = FreeFile

Open txtFile For Input As #hFile

On Error Resume Next

For r = LBRow To UBRow
Input #hFile, arr(r)
Next r

Close #hFile

OpenTextFileToArray = arr

End Function


RBS



"hmm" wrote in message
...
I have developed a macro that opens large text files (each has a list of
about 50,000 lines, almost all single numbers), reads them one at a
time,
and
places each one in a successive column.

The method I used is to open a text file, copy column A, paste it into
the
next available column in the main sheet, close the text file, and
repeat
for
all text files in a folder (there could be up to 100 of them).

I am finding it could take about a minute to run this macro (for 35
files).

My question: will I gain speed by using the command "Open FileName For
Input
As #FileNum"? (I would try it myself; since there's a learning curve
for
me,
I'm hoping to get somebody's input first.)

Thanks.

Thanks RB.

As I understand it, arr is an array of type Variant, which the function
OpenTextFileToArray is populating with the contents of the named text file.
But is it not an array in the PROGRAM? If so, how do I transfer this array
to a COLUMN in the WORKSHEET? That is my goal, to transfer a series of text
files into successive rows of a worksheet. I'm sorry if I did not say so
clearly.

"RB Smissaert" wrote:

Look at this example:

Sub Test()

Dim i As Long
Dim arr(1 To 10)

OpenTextFileToArray "C:\TestFile2.txt", arr, 1, 10

For i = 1 To 10
MsgBox arr(i)
Next

End Sub


Just try it out on file. You may have to adjust it according to your target
file.


RBS


"hmm" wrote in message
...
Sounds interesting. What is "arr" passed to the function?

"RB Smissaert" wrote:

Maybe this function can help you. It will put the text in a 1-D array.
You can specify the last row higher than the reality, the On Error Resume
Next
will take care of that. Once you have your data in the array you can do
whatever you
want with it and it will be much faster.

Function OpenTextFileToArray(ByVal txtFile As String, _
ByRef arr As Variant, _
ByVal LBRow As Long, _
ByVal UBRow As Long) As Variant

Dim hFile As Long
Dim r As Long

hFile = FreeFile

Open txtFile For Input As #hFile

On Error Resume Next

For r = LBRow To UBRow
Input #hFile, arr(r)
Next r

Close #hFile

OpenTextFileToArray = arr

End Function


RBS



"hmm" wrote in message
...
I have developed a macro that opens large text files (each has a list of
about 50,000 lines, almost all single numbers), reads them one at a
time,
and
places each one in a successive column.

The method I used is to open a text file, copy column A, paste it into
the
next available column in the main sheet, close the text file, and
repeat
for
all text files in a folder (there could be up to 100 of them).

I am finding it could take about a minute to run this macro (for 35
files).

My question: will I gain speed by using the command "Open FileName For
Input
As #FileNum"? (I would try it myself; since there's a learning curve
for
me,
I'm hoping to get somebody's input first.)

Thanks.

OK, then you will have to alter it slightly.
Give the function a 2-D array and populate that.
So you will get something like this (aircode):

In the test sub:

Dim arr(1 to 10, 1 to 1)

OpenTextFileToArray "C:\TestFile2.txt", arr, 1, 10

range(cells(1), cells(10,1)) = arr


In the function:

For r = LBRow To UBRow
Input #hFile, arr(r,1)
Next r


RBS


"hmm" wrote in message
...
Thanks RB.

As I understand it, arr is an array of type Variant, which the function
OpenTextFileToArray is populating with the contents of the named text
file.
But is it not an array in the PROGRAM? If so, how do I transfer this
array
to a COLUMN in the WORKSHEET? That is my goal, to transfer a series of
text
files into successive rows of a worksheet. I'm sorry if I did not say so
clearly.

"RB Smissaert" wrote:

Look at this example:

Sub Test()

Dim i As Long
Dim arr(1 To 10)

OpenTextFileToArray "C:\TestFile2.txt", arr, 1, 10

For i = 1 To 10
MsgBox arr(i)
Next

End Sub


Just try it out on file. You may have to adjust it according to your
target
file.


RBS


"hmm" wrote in message
...
Sounds interesting. What is "arr" passed to the function?

"RB Smissaert" wrote:

Maybe this function can help you. It will put the text in a 1-D array.
You can specify the last row higher than the reality, the On Error
Resume
Next
will take care of that. Once you have your data in the array you can
do
whatever you
want with it and it will be much faster.

Function OpenTextFileToArray(ByVal txtFile As String, _
ByRef arr As Variant, _
ByVal LBRow As Long, _
ByVal UBRow As Long) As Variant

Dim hFile As Long
Dim r As Long

hFile = FreeFile

Open txtFile For Input As #hFile

On Error Resume Next

For r = LBRow To UBRow
Input #hFile, arr(r)
Next r

Close #hFile

OpenTextFileToArray = arr

End Function


RBS



"hmm" wrote in message
...
I have developed a macro that opens large text files (each has a list
of
about 50,000 lines, almost all single numbers), reads them one at a
time,
and
places each one in a successive column.

The method I used is to open a text file, copy column A, paste it
into
the
next available column in the main sheet, close the text file, and
repeat
for
all text files in a folder (there could be up to 100 of them).

I am finding it could take about a minute to run this macro (for 35
files).

My question: will I gain speed by using the command "Open FileName
For
Input
As #FileNum"? (I would try it myself; since there's a learning
curve
for
me,
I'm hoping to get somebody's input first.)

Thanks.