Large .txt file Import Macro for Excel 2003 not working...Runtime Error '53'

mjp155

New Member
Joined
Oct 29, 2010
Messages
29
Hello,

I have excel 2003 and I am trying to import a HUGE text file into a spreadsheet. I found a macro that will import the data and carry over to a new worksheet once the row limit on the current tab is reached. However, when I try to run the macro I get an error that says "Run-time error '53' : File not Found "

Below is the macro and the text highlighted in red is what the debugger goes to when the error occurs...anyone know why this is happening?
Code:
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
      [COLOR=#ff0000]Open FileName For Input As #FileNum[/COLOR]
      '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 = 65536 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
 

Some videos you may like

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.

mjp155

New Member
Joined
Oct 29, 2010
Messages
29
Thanks for responding Rajan... the file name is "Text File.txt"...when I type that exact file name into the input box, the error comes up. When I first tested it, it worked fine, but now it no longer works!
 

Rajan_Verma

New Member
Joined
Jul 5, 2012
Messages
13
Thanks for responding Rajan... the file name is "Text File.txt"...when I type that exact file name into the input box, the error comes up. When I first tested it, it worked fine, but now it no longer works!

is data in text file delimeted by any delemeter e.g space , Comma??
 

mjp155

New Member
Joined
Oct 29, 2010
Messages
29

ADVERTISEMENT

is data in text file delimeted by any delemeter e.g space , Comma??

It is not...when it first worked for me, all the data went into one column.
 

hypnotoad

New Member
Joined
Feb 7, 2012
Messages
47
How are you setting the path of the file? Is your excel sheet in the same directory? You might have to set it anyway.
 

tigeravatar

Well-known Member
Joined
Aug 12, 2011
Messages
760
When using the Open "File" For Input As #, the "File" string has to be the complete path. So if your text file is stored in C:\Temp, then it should be:
Code:
Open "C:\Temp\Test File.txt" For Input As #FileNum


So using an Inputbox isn't going to work so well. Instead, try using the Application.GetOpenFileName method:
Code:
    Dim FileName As String
    FileName = Application.GetOpenFilename("Text Files, *.txt")
    If FileName = "False" Then Exit Sub 'Pressed cancel
    
    MsgBox FileName
 

Watch MrExcel Video

Forum statistics

Threads
1,122,705
Messages
5,597,668
Members
414,162
Latest member
jborjal1967

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top