Import text file

[beersa]

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]

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]

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

Thx.

 

[Gary Keramidas]

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

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

Thx.

 

[beersa]

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

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 Keramidas]

try this

ActiveWindow.Close SaveChanges:=False

 

[Guest]

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

 

[Dave Peterson]

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