Import text file
 

I am using MsOffice 97.
The following is my code to import text file into the worksheet. The
text file contains 7000 rows.
i = 2
Open "c:\cost_centre.txt" For Input As #1
Do While Not EOF(1)
Line Input #1, inline
If Not IsEmpty(inline) Then
strCC = Mid(inline, 6, 8)
strSS = Mid(inline, 28, 1)
strCG = Mid(inline, 32, 4)
Sheets(currSheet).Range("a" & i) = strCC
Sheets(currSheet).Range("b" & i) = strSS
Sheets(currSheet).Range("c" & i) = strCG
i = i + 1
End If
Loop
Close #1

I have performance problem with above code. It took 5 mins to copy into
the worksheet. What is the fastest way to open a text file?
Thanks.

Import text file
 

I'd try recording a macro when I opened the text file via file|open.

You'll the text to columns wizard popup.

You'll be able to choose fixed width, adjust the fields to what you
want--skipping the fields you don't want, too.

Then just copy that imported range and paste to its real home.

(and close the text workbook without saving)

I bet it'll be faster.

beersa wrote:

I am using MsOffice 97.
The following is my code to import text file into the worksheet. The
text file contains 7000 rows.
i = 2
Open "c:\cost_centre.txt" For Input As #1
Do While Not EOF(1)
Line Input #1, inline
If Not IsEmpty(inline) Then
strCC = Mid(inline, 6, 8)
strSS = Mid(inline, 28, 1)
strCG = Mid(inline, 32, 4)
Sheets(currSheet).Range("a" & i) = strCC
Sheets(currSheet).Range("b" & i) = strSS
Sheets(currSheet).Range("c" & i) = strCG
i = i + 1
End If
Loop
Close #1

I have performance problem with above code. It took 5 mins to copy into
the worksheet. What is the fastest way to open a text file?
Thanks.

--

Dave Peterson

Import text file
 

It is faster than the for looping.
How do you close the text workbook without warning message "want to
save?".

Thx.

Dave Peterson wrote:
I'd try recording a macro when I opened the text file via file|open.

You'll the text to columns wizard popup.

You'll be able to choose fixed width, adjust the fields to what you
want--skipping the fields you don't want, too.

Then just copy that imported range and paste to its real home.

(and close the text workbook without saving)

I bet it'll be faster.

beersa wrote:

I am using MsOffice 97.
The following is my code to import text file into the worksheet. The
text file contains 7000 rows.
i = 2
Open "c:\cost_centre.txt" For Input As #1
Do While Not EOF(1)
Line Input #1, inline
If Not IsEmpty(inline) Then
strCC = Mid(inline, 6, 8)
strSS = Mid(inline, 28, 1)
strCG = Mid(inline, 32, 4)
Sheets(currSheet).Range("a" & i) = strCC
Sheets(currSheet).Range("b" & i) = strSS
Sheets(currSheet).Range("c" & i) = strCG
i = i + 1
End If
Loop
Close #1

I have performance problem with above code. It took 5 mins to copy into
the worksheet. What is the fastest way to open a text file?
Thanks.

--

Dave Peterson

Import text file
 

i use a similar routine to import information from email files and close #1
doesn't prompt my user to save.

there's only 1 line in each email, so a couple hundred emails only takes 2 or 3
seconds.
--


Gary


"beersa" wrote in message
oups.com...
It is faster than the for looping.
How do you close the text workbook without warning message "want to
save?".

Thx.

Dave Peterson wrote:
I'd try recording a macro when I opened the text file via file|open.

You'll the text to columns wizard popup.

You'll be able to choose fixed width, adjust the fields to what you
want--skipping the fields you don't want, too.

Then just copy that imported range and paste to its real home.

(and close the text workbook without saving)

I bet it'll be faster.

beersa wrote:

I am using MsOffice 97.
The following is my code to import text file into the worksheet. The
text file contains 7000 rows.
i = 2
Open "c:\cost_centre.txt" For Input As #1
Do While Not EOF(1)
Line Input #1, inline
If Not IsEmpty(inline) Then
strCC = Mid(inline, 6, 8)
strSS = Mid(inline, 28, 1)
strCG = Mid(inline, 32, 4)
Sheets(currSheet).Range("a" & i) = strCC
Sheets(currSheet).Range("b" & i) = strSS
Sheets(currSheet).Range("c" & i) = strCG
i = i + 1
End If
Loop
Close #1

I have performance problem with above code. It took 5 mins to copy into
the worksheet. What is the fastest way to open a text file?
Thanks.

--

Dave Peterson

Import text file
 

It wont prompt if use close#1. But I open the file by:
Workbooks.OpenText Filename:="C:\account_code.txt", Origin:= _
xlWindows, StartRow:=1, DataType:=xlDelimited, TextQualifier:=
_
xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False,
Semicolon:=False _
, Comma:=True, Space:=False, Other:=False,
FieldInfo:=Array(Array(1, 1), _
Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1), Array(6,
1))


and close the file by:
Windows("account_code.txt").Activate
ActiveWindow.Close


Gary Keramidas wrote:
i use a similar routine to import information from email files and close #1
doesn't prompt my user to save.

there's only 1 line in each email, so a couple hundred emails only takes 2 or 3
seconds.
--


Gary


"beersa" wrote in message
oups.com...
It is faster than the for looping.
How do you close the text workbook without warning message "want to
save?".

Thx.

Dave Peterson wrote:
I'd try recording a macro when I opened the text file via file|open.

You'll the text to columns wizard popup.

You'll be able to choose fixed width, adjust the fields to what you
want--skipping the fields you don't want, too.

Then just copy that imported range and paste to its real home.

(and close the text workbook without saving)

I bet it'll be faster.

beersa wrote:

I am using MsOffice 97.
The following is my code to import text file into the worksheet. The
text file contains 7000 rows.
i = 2
Open "c:\cost_centre.txt" For Input As #1
Do While Not EOF(1)
Line Input #1, inline
If Not IsEmpty(inline) Then
strCC = Mid(inline, 6, 8)
strSS = Mid(inline, 28, 1)
strCG = Mid(inline, 32, 4)
Sheets(currSheet).Range("a" & i) = strCC
Sheets(currSheet).Range("b" & i) = strSS
Sheets(currSheet).Range("c" & i) = strCG
i = i + 1
End If
Loop
Close #1

I have performance problem with above code. It took 5 mins to copy into
the worksheet. What is the fastest way to open a text file?
Thanks.

--

Dave Peterson

Import text file
 

try this

ActiveWindow.Close SaveChanges:=False

--


Gary


"beersa" wrote in message
ups.com...
It wont prompt if use close#1. But I open the file by:
Workbooks.OpenText Filename:="C:\account_code.txt", Origin:= _
xlWindows, StartRow:=1, DataType:=xlDelimited, TextQualifier:=
_
xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False,
Semicolon:=False _
, Comma:=True, Space:=False, Other:=False,
FieldInfo:=Array(Array(1, 1), _
Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1), Array(6,
1))


and close the file by:
Windows("account_code.txt").Activate
ActiveWindow.Close


Gary Keramidas wrote:
i use a similar routine to import information from email files and close #1
doesn't prompt my user to save.

there's only 1 line in each email, so a couple hundred emails only takes 2 or
3
seconds.
--


Gary


"beersa" wrote in message
oups.com...
It is faster than the for looping.
How do you close the text workbook without warning message "want to
save?".

Thx.

Dave Peterson wrote:
I'd try recording a macro when I opened the text file via file|open.

You'll the text to columns wizard popup.

You'll be able to choose fixed width, adjust the fields to what you
want--skipping the fields you don't want, too.

Then just copy that imported range and paste to its real home.

(and close the text workbook without saving)

I bet it'll be faster.

beersa wrote:

I am using MsOffice 97.
The following is my code to import text file into the worksheet. The
text file contains 7000 rows.
i = 2
Open "c:\cost_centre.txt" For Input As #1
Do While Not EOF(1)
Line Input #1, inline
If Not IsEmpty(inline) Then
strCC = Mid(inline, 6, 8)
strSS = Mid(inline, 28, 1)
strCG = Mid(inline, 32, 4)
Sheets(currSheet).Range("a" & i) = strCC
Sheets(currSheet).Range("b" & i) = strSS
Sheets(currSheet).Range("c" & i) = strCG
i = i + 1
End If
Loop
Close #1

I have performance problem with above code. It took 5 mins to copy into
the worksheet. What is the fastest way to open a text file?
Thanks.

--

Dave Peterson

Import text file
 

Your loop does a significant amount of writing to the sheet, which requires
constant screen updating, page break recalculations, etc. You could add this
code around your loop to speed it up... You could also experiment with
loading the text file into an array as well.

Application.ScreenUpdating =False
Application.Calculation =xlCalculationManual
ActiveSheet.DisplayPageBreaks = False

your looping code here

Application.ScreenUpdating =True
Application.Calculation = xlCalculationAutomatic

Roy Wagner

"beersa" wrote:

I am using MsOffice 97.
The following is my code to import text file into the worksheet. The
text file contains 7000 rows.
i = 2
Open "c:\cost_centre.txt" For Input As #1
Do While Not EOF(1)
Line Input #1, inline
If Not IsEmpty(inline) Then
strCC = Mid(inline, 6, 8)
strSS = Mid(inline, 28, 1)
strCG = Mid(inline, 32, 4)
Sheets(currSheet).Range("a" & i) = strCC
Sheets(currSheet).Range("b" & i) = strSS
Sheets(currSheet).Range("c" & i) = strCG
i = i + 1
End If
Loop
Close #1

I have performance problem with above code. It took 5 mins to copy into
the worksheet. What is the fastest way to open a text file?
Thanks.

Import text file
 

I'd use something like this:

Option Explicit
Sub testme01()

Dim DestCell As Range
Dim TextWks As Worksheet

Set DestCell = ActiveSheet.Range("a2")

Workbooks.OpenText Filename:="c:\cost_centre.txt", _
'fill in with the rest of your recorded macro

Set TextWks = ActiveSheet

TextWks.UsedRange.Copy _
Destination:=DestCell

TextWks.Parent.Close savechanges:=False

End Sub

beersa wrote:

It is faster than the for looping.
How do you close the text workbook without warning message "want to
save?".

Thx.

Dave Peterson wrote:
I'd try recording a macro when I opened the text file via file|open.

You'll the text to columns wizard popup.

You'll be able to choose fixed width, adjust the fields to what you
want--skipping the fields you don't want, too.

Then just copy that imported range and paste to its real home.

(and close the text workbook without saving)

I bet it'll be faster.

beersa wrote:

I am using MsOffice 97.
The following is my code to import text file into the worksheet. The
text file contains 7000 rows.
i = 2
Open "c:\cost_centre.txt" For Input As #1
Do While Not EOF(1)
Line Input #1, inline
If Not IsEmpty(inline) Then
strCC = Mid(inline, 6, 8)
strSS = Mid(inline, 28, 1)
strCG = Mid(inline, 32, 4)
Sheets(currSheet).Range("a" & i) = strCC
Sheets(currSheet).Range("b" & i) = strSS
Sheets(currSheet).Range("c" & i) = strCG
i = i + 1
End If
Loop
Close #1

I have performance problem with above code. It took 5 mins to copy into
the worksheet. What is the fastest way to open a text file?
Thanks.

--

Dave Peterson

--

Dave Peterson