C
chase
Hello,
I am importing a very large text file into Excel. I found the MS VBA code
that seperates the txt file into multiple worksheets and it works when I copy
the code to a new worksheet and run it. However, once I save the file and
close it and reopen it again at a later date to run the same macro I am not
able to open the txt file and the macro incurs an error that reads
"Run-time error '53'
File not Found"
I am using the exact same code and the exact same txt file as I did when the
code ran successfuly. The only thing that is different is that I did not
paste the code to the worksheet at that time, I merely ran the saved code.
Below is the code I used that worked the first time but not after I saved
it. I have indicated where the "Run-time error '53'" occurred by surrounding
it with '***':
Sub LargeFileImport()
'Dimension Variables
Dim ResultStr As String
Dim FileName As String
Dim FileNum As Integer
Dim Counter As Double
'Ask User for File's Name
FileName = InputBox("Please enter the Text File's name, e.g. test.txt")
'Check for no entry
If FileName = "" Then End
'Get Next Available File Handle Number
FileNum = FreeFile()
'Open Text File For Input
***Open FileName For Input As #FileNum***
'Turn Screen Updating Off
Application.ScreenUpdating = False
'Create A New WorkBook With One Worksheet In It
Workbooks.Add template:=xlWorksheet
'Set The Counter to 1
Counter = 1
'Loop Until the End Of File Is Reached
Do While Seek(FileNum) <= LOF(FileNum)
'Display Importing Row Number On Status Bar
Application.StatusBar = "Importing Row " & _
Counter & " of text file " & FileName
'Store One Line Of Text From File To Variable
Line Input #FileNum, ResultStr
'Store Variable Data Into Active Cell
If Left(ResultStr, 1) = "=" Then
ActiveCell.Value = "'" & ResultStr
Else
ActiveCell.Value = ResultStr
End If
'For Excel versions before Excel 97, change 65536 to 16384
If ActiveCell.Row = 65535 Then
'If On The Last Row Then Add A New Sheet
ActiveWorkbook.Sheets.Add
Else
'If Not The Last Row Then Go One Cell Down
ActiveCell.Offset(1, 0).Select
End If
'Increment the Counter By 1
Counter = Counter + 1
'Start Again At Top Of 'Do While' Statement
Loop
'Close The Open Text File
Close
'Remove Message From Status Bar
Application.StatusBar = False
End Sub
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
End Sub
I am importing a very large text file into Excel. I found the MS VBA code
that seperates the txt file into multiple worksheets and it works when I copy
the code to a new worksheet and run it. However, once I save the file and
close it and reopen it again at a later date to run the same macro I am not
able to open the txt file and the macro incurs an error that reads
"Run-time error '53'
File not Found"
I am using the exact same code and the exact same txt file as I did when the
code ran successfuly. The only thing that is different is that I did not
paste the code to the worksheet at that time, I merely ran the saved code.
Below is the code I used that worked the first time but not after I saved
it. I have indicated where the "Run-time error '53'" occurred by surrounding
it with '***':
Sub LargeFileImport()
'Dimension Variables
Dim ResultStr As String
Dim FileName As String
Dim FileNum As Integer
Dim Counter As Double
'Ask User for File's Name
FileName = InputBox("Please enter the Text File's name, e.g. test.txt")
'Check for no entry
If FileName = "" Then End
'Get Next Available File Handle Number
FileNum = FreeFile()
'Open Text File For Input
***Open FileName For Input As #FileNum***
'Turn Screen Updating Off
Application.ScreenUpdating = False
'Create A New WorkBook With One Worksheet In It
Workbooks.Add template:=xlWorksheet
'Set The Counter to 1
Counter = 1
'Loop Until the End Of File Is Reached
Do While Seek(FileNum) <= LOF(FileNum)
'Display Importing Row Number On Status Bar
Application.StatusBar = "Importing Row " & _
Counter & " of text file " & FileName
'Store One Line Of Text From File To Variable
Line Input #FileNum, ResultStr
'Store Variable Data Into Active Cell
If Left(ResultStr, 1) = "=" Then
ActiveCell.Value = "'" & ResultStr
Else
ActiveCell.Value = ResultStr
End If
'For Excel versions before Excel 97, change 65536 to 16384
If ActiveCell.Row = 65535 Then
'If On The Last Row Then Add A New Sheet
ActiveWorkbook.Sheets.Add
Else
'If Not The Last Row Then Go One Cell Down
ActiveCell.Offset(1, 0).Select
End If
'Increment the Counter By 1
Counter = Counter + 1
'Start Again At Top Of 'Do While' Statement
Loop
'Close The Open Text File
Close
'Remove Message From Status Bar
Application.StatusBar = False
End Sub
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
End Sub