L
larrydave
Hello; I apologize if this is a duplicate post, but I posted over an hour ago
and it has not shown up yet.
I have a macro that imports a large text file into every fourth column.
Last week it ran fairly fast; this week it crawls. I estimate today that it
would take nearly 7 hours to import this file; last week to took 10 - 15
seconds. I haven't made any code changes, so I do not know what is wrong.
The size of the file keeps, growing - maybe that is the problem. I have even
tried using a better pc - still the same issue. Here is my code; I would
appreciate if someone who knows what they are doing would see if there is
something that could be changed to speed things up.
Sub Autpen()
MsgBox "Hello"
Columns("a:ir").EntireColumn.Clear
Dim FileName As String
Dim ResultStr As String
Dim FileNum As Integer
Dim Counter As Double
FileName = Application.GetOpenFilename
Application.ScreenUpdating = False
If FileName = "False" Then End
FileNum = FreeFile()
Open FileName For Input As #FileNum
Sheet1.Cells(2, 1).Select
Counter = 1
ColCounter = 4
Do While Seek(FileNum) <= LOF(FileNum)
If EOF(FileNum) Then End
Application.StatusBar = "Reading Row " & Counter & " of text file " &
FileName
Line Input #FileNum, ResultStr
ActiveCell.Value = ResultStr
If ActiveCell.Row = 65536 Then
ColCounter = ColCounter + 1
Sheet1.Cells(2, ColCounter).Select
Else
ActiveCell.Offset(1, 0).Select
End If
Counter = Counter + 1
Loop
Close
Application.ScreenUpdating = True
Application.StatusBar = False
End Sub
When I end the macro, the debugger stops at this line:
If ActiveCell.Row = 65536 Then
Any help and ideas would be greatly appreciated.
Thank you.
and it has not shown up yet.
I have a macro that imports a large text file into every fourth column.
Last week it ran fairly fast; this week it crawls. I estimate today that it
would take nearly 7 hours to import this file; last week to took 10 - 15
seconds. I haven't made any code changes, so I do not know what is wrong.
The size of the file keeps, growing - maybe that is the problem. I have even
tried using a better pc - still the same issue. Here is my code; I would
appreciate if someone who knows what they are doing would see if there is
something that could be changed to speed things up.
Sub Autpen()
MsgBox "Hello"
Columns("a:ir").EntireColumn.Clear
Dim FileName As String
Dim ResultStr As String
Dim FileNum As Integer
Dim Counter As Double
FileName = Application.GetOpenFilename
Application.ScreenUpdating = False
If FileName = "False" Then End
FileNum = FreeFile()
Open FileName For Input As #FileNum
Sheet1.Cells(2, 1).Select
Counter = 1
ColCounter = 4
Do While Seek(FileNum) <= LOF(FileNum)
If EOF(FileNum) Then End
Application.StatusBar = "Reading Row " & Counter & " of text file " &
FileName
Line Input #FileNum, ResultStr
ActiveCell.Value = ResultStr
If ActiveCell.Row = 65536 Then
ColCounter = ColCounter + 1
Sheet1.Cells(2, ColCounter).Select
Else
ActiveCell.Offset(1, 0).Select
End If
Counter = Counter + 1
Loop
Close
Application.ScreenUpdating = True
Application.StatusBar = False
End Sub
When I end the macro, the debugger stops at this line:
If ActiveCell.Row = 65536 Then
Any help and ideas would be greatly appreciated.
Thank you.