|
Importing Large Text File Problem
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 |
Importing Large Text File Problem
You are not getting the full pathname of the file. Try the replacement below.
from: FileName = InputBox("Please enter the Text File's name, e.g. test.txt") to: FileName = Application _ .GetOpenFilename("Text Files (*.txt), *.txt") If FileName = False Then MsgBox("Cannot Open file - Exinting Macro") exit sub End If "chase" wrote: 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 |
Importing Large Text File Problem
Joel,
Thank you for your extremely speedy response. :) I replaced the code as you said, and it brings up the open file application where I can select the file I want to open, but after I select it I get this error: "Run-time error '13': Type mismatch" and it highlights this line: If FileName = False Then I don't know what to do, so I really appreciate your help in this. -Chase "Joel" wrote: You are not getting the full pathname of the file. Try the replacement below. from: FileName = InputBox("Please enter the Text File's name, e.g. test.txt") to: FileName = Application _ .GetOpenFilename("Text Files (*.txt), *.txt") If FileName = False Then MsgBox("Cannot Open file - Exinting Macro") exit sub End If "chase" wrote: 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 |
Importing Large Text File Problem
If you hit cancel on the dialog window you will get false instead of a
string. make this change. from Dim FileName As String to Dim FileName As Variant "chase" wrote: Joel, Thank you for your extremely speedy response. :) I replaced the code as you said, and it brings up the open file application where I can select the file I want to open, but after I select it I get this error: "Run-time error '13': Type mismatch" and it highlights this line: If FileName = False Then I don't know what to do, so I really appreciate your help in this. -Chase "Joel" wrote: You are not getting the full pathname of the file. Try the replacement below. from: FileName = InputBox("Please enter the Text File's name, e.g. test.txt") to: FileName = Application _ .GetOpenFilename("Text Files (*.txt), *.txt") If FileName = False Then MsgBox("Cannot Open file - Exinting Macro") exit sub End If "chase" wrote: 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 |
Importing Large Text File Problem
Change:
Dim FileName As String to Dim FileName As Variant (it can be a string for the filename or the boolean value False if the user hit cancel.) If you still have trouble, you may want to include your current code in your next reply. chase wrote: Joel, Thank you for your extremely speedy response. :) I replaced the code as you said, and it brings up the open file application where I can select the file I want to open, but after I select it I get this error: "Run-time error '13': Type mismatch" and it highlights this line: If FileName = False Then I don't know what to do, so I really appreciate your help in this. -Chase "Joel" wrote: You are not getting the full pathname of the file. Try the replacement below. from: FileName = InputBox("Please enter the Text File's name, e.g. test.txt") to: FileName = Application _ .GetOpenFilename("Text Files (*.txt), *.txt") If FileName = False Then MsgBox("Cannot Open file - Exinting Macro") exit sub End If "chase" wrote: 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 -- Dave Peterson |
Importing Large Text File Problem
change the Dim FileName as String to as Variant fixed that bug but I am
getting another one now on the *** line below. The error message reads Run-time error '52' Bad file name of number I really appreciate your help thank you very much. Sub LargeFileImport() 'Dimension Variables Dim ResultStr As String Dim FileName As Variant 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 FileName = Application _ .GetOpenFilename("Text Files (*.txt), *.txt") If FileName = False Then MsgBox ("Cannot Open file - Exiting Macro") End If '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 "Dave Peterson" wrote: Change: Dim FileName As String to Dim FileName As Variant (it can be a string for the filename or the boolean value False if the user hit cancel.) If you still have trouble, you may want to include your current code in your next reply. chase wrote: Joel, Thank you for your extremely speedy response. :) I replaced the code as you said, and it brings up the open file application where I can select the file I want to open, but after I select it I get this error: "Run-time error '13': Type mismatch" and it highlights this line: If FileName = False Then I don't know what to do, so I really appreciate your help in this. -Chase "Joel" wrote: You are not getting the full pathname of the file. Try the replacement below. from: FileName = InputBox("Please enter the Text File's name, e.g. test.txt") to: FileName = Application _ .GetOpenFilename("Text Files (*.txt), *.txt") If FileName = False Then MsgBox("Cannot Open file - Exinting Macro") exit sub End If "chase" wrote: 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 -- Dave Peterson |
Importing Large Text File Problem
Just because you got the filename, doesn't mean that you opened the file.
Option Explicit Sub LargeFileImport() 'Dimension Variables Dim ResultStr As String Dim FileName As Variant 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 FileName = Application _ .GetOpenFilename("Text Files (*.txt), *.txt") If FileName = False Then MsgBox ("Cannot Open file - Exiting Macro") End If '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 Close FileNum 'just in case it's open Open FileName For Input As FileNum Do While Not EOF(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 chase wrote: change the Dim FileName as String to as Variant fixed that bug but I am getting another one now on the *** line below. The error message reads Run-time error '52' Bad file name of number I really appreciate your help thank you very much. Sub LargeFileImport() 'Dimension Variables Dim ResultStr As String Dim FileName As Variant 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 FileName = Application _ .GetOpenFilename("Text Files (*.txt), *.txt") If FileName = False Then MsgBox ("Cannot Open file - Exiting Macro") End If '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 "Dave Peterson" wrote: Change: Dim FileName As String to Dim FileName As Variant (it can be a string for the filename or the boolean value False if the user hit cancel.) If you still have trouble, you may want to include your current code in your next reply. chase wrote: Joel, Thank you for your extremely speedy response. :) I replaced the code as you said, and it brings up the open file application where I can select the file I want to open, but after I select it I get this error: "Run-time error '13': Type mismatch" and it highlights this line: If FileName = False Then I don't know what to do, so I really appreciate your help in this. -Chase "Joel" wrote: You are not getting the full pathname of the file. Try the replacement below. from: FileName = InputBox("Please enter the Text File's name, e.g. test.txt") to: FileName = Application _ .GetOpenFilename("Text Files (*.txt), *.txt") If FileName = False Then MsgBox("Cannot Open file - Exinting Macro") exit sub End If "chase" wrote: 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 -- Dave Peterson -- Dave Peterson |
Importing Large Text File Problem
Thank you very much for all your help. Dave especially. I added a line to
actually open the text file and the code works perfectly every time. My final code is as follows for anyone else experiencing the same problem (FYI I set the code to only import the text to 65535 rows instead of 65536 so that I can insert a row after it's done) Anyway here is the code, enjoy and thank you: Sub LargeFileImport() 'Dimension Variables Dim ResultStr As String Dim FileName As Variant 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 FileName = Application _ .GetOpenFilename("Text Files (*.txt), *.txt") If FileName = False Then MsgBox ("Cannot Open file - Exiting Macro") End If 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 "Dave Peterson" wrote: Just because you got the filename, doesn't mean that you opened the file. Option Explicit Sub LargeFileImport() 'Dimension Variables Dim ResultStr As String Dim FileName As Variant 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 FileName = Application _ .GetOpenFilename("Text Files (*.txt), *.txt") If FileName = False Then MsgBox ("Cannot Open file - Exiting Macro") End If '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 Close FileNum 'just in case it's open Open FileName For Input As FileNum Do While Not EOF(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 chase wrote: change the Dim FileName as String to as Variant fixed that bug but I am getting another one now on the *** line below. The error message reads Run-time error '52' Bad file name of number I really appreciate your help thank you very much. Sub LargeFileImport() 'Dimension Variables Dim ResultStr As String Dim FileName As Variant 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 FileName = Application _ .GetOpenFilename("Text Files (*.txt), *.txt") If FileName = False Then MsgBox ("Cannot Open file - Exiting Macro") End If '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 "Dave Peterson" wrote: Change: Dim FileName As String to Dim FileName As Variant (it can be a string for the filename or the boolean value False if the user hit cancel.) If you still have trouble, you may want to include your current code in your next reply. chase wrote: Joel, Thank you for your extremely speedy response. :) I replaced the code as you said, and it brings up the open file application where I can select the file I want to open, but after I select it I get this error: "Run-time error '13': Type mismatch" and it highlights this line: If FileName = False Then I don't know what to do, so I really appreciate your help in this. -Chase "Joel" wrote: You are not getting the full pathname of the file. Try the replacement below. from: FileName = InputBox("Please enter the Text File's name, e.g. test.txt") to: FileName = Application _ .GetOpenFilename("Text Files (*.txt), *.txt") If FileName = False Then MsgBox("Cannot Open file - Exinting Macro") exit sub End If "chase" wrote: 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 -- Dave Peterson -- Dave Peterson |
| All times are GMT +1. The time now is 12:15 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com