Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Importing Text file
Hi All,
I'm using this code (extracted from Microsoft site), this working fine, but this importing my text file in one column, so my text file has 7 columns Field1 - length 26 Field2 - length 15 Field3 - length 14 Field4 - length 21 Field5 - length 18 Field6 - length 3 Field7 - length 17 So I need these columns separated Thanks for your help Here the code: Sub LargeFileImport() 'Dimension Variables Dim ResultStr As String Dim FileName As String Dim FileNum As Integer Dim Counter As Double Dim Filt As String Dim FilterIndex As Integer Dim Titre As String Filt = "Text File (*.txt),*.txt," & _ "Fichiers CSV (*.csv),*.csv," 'Ask User for File's Name FileName = Application.GetOpenFilename _ (FileFilter:=Filt, _ FilterIndex:=FilterIndex, _ Title:=Titre) '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 = 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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Importing Text file
You simply need to add RowtoColumns in 2 places in the code. Also Rows.Count
will give you the corrrect number of rows depending on the version of Excel you are using. Columns("A:A").TextToColumns _ Destination:=Range("A1"), _ DataType:=xlDelimited, _ ConsecutiveDelimiter:=False, _ Comma:=True Sub LargeFileImport() 'Dimension Variables Dim ResultStr As String Dim FileName As String Dim FileNum As Integer Dim Counter As Double Dim Filt As String Dim FilterIndex As Integer Dim Titre As String Filt = "Text File (*.txt),*.txt," & _ "Fichiers CSV (*.csv),*.csv," 'Ask User for File's Name FileName = Application.GetOpenFilename _ (FileFilter:=Filt, _ FilterIndex:=FilterIndex, _ Title:=Titre) '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 If ActiveCell.Row = Rows.Count Then 'If On The Last Row Then Add A New Sheet Columns("A:A").TextToColumns _ Destination:=Range("A1"), _ DataType:=xlDelimited, _ ConsecutiveDelimiter:=False, _ Comma:=True 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 Columns("A:A").TextToColumns _ Destination:=Range("A1"), _ DataType:=xlDelimited, _ ConsecutiveDelimiter:=False, _ Comma:=True 'Close The Open Text File Close 'Remove Message From Status Bar Application.StatusBar = False End Sub "Dante Huapaya" wrote: Hi All, I'm using this code (extracted from Microsoft site), this working fine, but this importing my text file in one column, so my text file has 7 columns Field1 - length 26 Field2 - length 15 Field3 - length 14 Field4 - length 21 Field5 - length 18 Field6 - length 3 Field7 - length 17 So I need these columns separated Thanks for your help Here the code: Sub LargeFileImport() 'Dimension Variables Dim ResultStr As String Dim FileName As String Dim FileNum As Integer Dim Counter As Double Dim Filt As String Dim FilterIndex As Integer Dim Titre As String Filt = "Text File (*.txt),*.txt," & _ "Fichiers CSV (*.csv),*.csv," 'Ask User for File's Name FileName = Application.GetOpenFilename _ (FileFilter:=Filt, _ FilterIndex:=FilterIndex, _ Title:=Titre) '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 = 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Importing Text file
I'm sorry, but where exactly?
I dont have so much experience with macros Thanks Dante "Joel" wrote: You simply need to add RowtoColumns in 2 places in the code. Also Rows.Count will give you the corrrect number of rows depending on the version of Excel you are using. Columns("A:A").TextToColumns _ Destination:=Range("A1"), _ DataType:=xlDelimited, _ ConsecutiveDelimiter:=False, _ Comma:=True Sub LargeFileImport() 'Dimension Variables Dim ResultStr As String Dim FileName As String Dim FileNum As Integer Dim Counter As Double Dim Filt As String Dim FilterIndex As Integer Dim Titre As String Filt = "Text File (*.txt),*.txt," & _ "Fichiers CSV (*.csv),*.csv," 'Ask User for File's Name FileName = Application.GetOpenFilename _ (FileFilter:=Filt, _ FilterIndex:=FilterIndex, _ Title:=Titre) '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 If ActiveCell.Row = Rows.Count Then 'If On The Last Row Then Add A New Sheet Columns("A:A").TextToColumns _ Destination:=Range("A1"), _ DataType:=xlDelimited, _ ConsecutiveDelimiter:=False, _ Comma:=True 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 Columns("A:A").TextToColumns _ Destination:=Range("A1"), _ DataType:=xlDelimited, _ ConsecutiveDelimiter:=False, _ Comma:=True 'Close The Open Text File Close 'Remove Message From Status Bar Application.StatusBar = False End Sub "Dante Huapaya" wrote: Hi All, I'm using this code (extracted from Microsoft site), this working fine, but this importing my text file in one column, so my text file has 7 columns Field1 - length 26 Field2 - length 15 Field3 - length 14 Field4 - length 21 Field5 - length 18 Field6 - length 3 Field7 - length 17 So I need these columns separated Thanks for your help Here the code: Sub LargeFileImport() 'Dimension Variables Dim ResultStr As String Dim FileName As String Dim FileNum As Integer Dim Counter As Double Dim Filt As String Dim FilterIndex As Integer Dim Titre As String Filt = "Text File (*.txt),*.txt," & _ "Fichiers CSV (*.csv),*.csv," 'Ask User for File's Name FileName = Application.GetOpenFilename _ (FileFilter:=Filt, _ FilterIndex:=FilterIndex, _ Title:=Titre) '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 = 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Importing Text file
I already put it in the code. I included the code on top so you would
easily see the new code that I added. "Dante Huapaya" wrote: I'm sorry, but where exactly? I dont have so much experience with macros Thanks Dante "Joel" wrote: You simply need to add RowtoColumns in 2 places in the code. Also Rows.Count will give you the corrrect number of rows depending on the version of Excel you are using. Columns("A:A").TextToColumns _ Destination:=Range("A1"), _ DataType:=xlDelimited, _ ConsecutiveDelimiter:=False, _ Comma:=True Sub LargeFileImport() 'Dimension Variables Dim ResultStr As String Dim FileName As String Dim FileNum As Integer Dim Counter As Double Dim Filt As String Dim FilterIndex As Integer Dim Titre As String Filt = "Text File (*.txt),*.txt," & _ "Fichiers CSV (*.csv),*.csv," 'Ask User for File's Name FileName = Application.GetOpenFilename _ (FileFilter:=Filt, _ FilterIndex:=FilterIndex, _ Title:=Titre) '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 If ActiveCell.Row = Rows.Count Then 'If On The Last Row Then Add A New Sheet Columns("A:A").TextToColumns _ Destination:=Range("A1"), _ DataType:=xlDelimited, _ ConsecutiveDelimiter:=False, _ Comma:=True 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 Columns("A:A").TextToColumns _ Destination:=Range("A1"), _ DataType:=xlDelimited, _ ConsecutiveDelimiter:=False, _ Comma:=True 'Close The Open Text File Close 'Remove Message From Status Bar Application.StatusBar = False End Sub "Dante Huapaya" wrote: Hi All, I'm using this code (extracted from Microsoft site), this working fine, but this importing my text file in one column, so my text file has 7 columns Field1 - length 26 Field2 - length 15 Field3 - length 14 Field4 - length 21 Field5 - length 18 Field6 - length 3 Field7 - length 17 So I need these columns separated Thanks for your help Here the code: Sub LargeFileImport() 'Dimension Variables Dim ResultStr As String Dim FileName As String Dim FileNum As Integer Dim Counter As Double Dim Filt As String Dim FilterIndex As Integer Dim Titre As String Filt = "Text File (*.txt),*.txt," & _ "Fichiers CSV (*.csv),*.csv," 'Ask User for File's Name FileName = Application.GetOpenFilename _ (FileFilter:=Filt, _ FilterIndex:=FilterIndex, _ Title:=Titre) '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 = 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Importing Text file
Thanks so much for your Help Joel. very Appreciated.
Dante "Joel" wrote: I already put it in the code. I included the code on top so you would easily see the new code that I added. "Dante Huapaya" wrote: I'm sorry, but where exactly? I dont have so much experience with macros Thanks Dante "Joel" wrote: You simply need to add RowtoColumns in 2 places in the code. Also Rows.Count will give you the corrrect number of rows depending on the version of Excel you are using. Columns("A:A").TextToColumns _ Destination:=Range("A1"), _ DataType:=xlDelimited, _ ConsecutiveDelimiter:=False, _ Comma:=True Sub LargeFileImport() 'Dimension Variables Dim ResultStr As String Dim FileName As String Dim FileNum As Integer Dim Counter As Double Dim Filt As String Dim FilterIndex As Integer Dim Titre As String Filt = "Text File (*.txt),*.txt," & _ "Fichiers CSV (*.csv),*.csv," 'Ask User for File's Name FileName = Application.GetOpenFilename _ (FileFilter:=Filt, _ FilterIndex:=FilterIndex, _ Title:=Titre) '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 If ActiveCell.Row = Rows.Count Then 'If On The Last Row Then Add A New Sheet Columns("A:A").TextToColumns _ Destination:=Range("A1"), _ DataType:=xlDelimited, _ ConsecutiveDelimiter:=False, _ Comma:=True 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 Columns("A:A").TextToColumns _ Destination:=Range("A1"), _ DataType:=xlDelimited, _ ConsecutiveDelimiter:=False, _ Comma:=True 'Close The Open Text File Close 'Remove Message From Status Bar Application.StatusBar = False End Sub "Dante Huapaya" wrote: Hi All, I'm using this code (extracted from Microsoft site), this working fine, but this importing my text file in one column, so my text file has 7 columns Field1 - length 26 Field2 - length 15 Field3 - length 14 Field4 - length 21 Field5 - length 18 Field6 - length 3 Field7 - length 17 So I need these columns separated Thanks for your help Here the code: Sub LargeFileImport() 'Dimension Variables Dim ResultStr As String Dim FileName As String Dim FileNum As Integer Dim Counter As Double Dim Filt As String Dim FilterIndex As Integer Dim Titre As String Filt = "Text File (*.txt),*.txt," & _ "Fichiers CSV (*.csv),*.csv," 'Ask User for File's Name FileName = Application.GetOpenFilename _ (FileFilter:=Filt, _ FilterIndex:=FilterIndex, _ Title:=Titre) '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 = 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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Importing Text file
Again Me,
When try to imported a large file, the data in the second sheet is imported in one column, Here the final code: Thanks Sub LargeFileImport() 'Dimension Variables Dim ResultStr As String Dim FileName As String Dim FileNum As Integer Dim Counter As Double Dim Filt As String Dim FilterIndex As Integer Dim Titre As String Filt = "Text File (*.txt),*.txt," & _ "Fichiers CSV (*.csv),*.csv," 'Ask User for File's Name FileName = Application.GetOpenFilename _ (FileFilter:=Filt, _ FilterIndex:=FilterIndex, _ Title:=Titre) '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 If ActiveCell.Row = Rows.Count Then 'If On The Last Row Then Add A New Sheet Columns("A:A").TextToColumns _ Destination:=Range("A1"), _ DataType:=xlFixedWidth, _ ConsecutiveDelimiter:=False, _ Comma:=False 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 "Joel" wrote: I already put it in the code. I included the code on top so you would easily see the new code that I added. "Dante Huapaya" wrote: I'm sorry, but where exactly? I dont have so much experience with macros Thanks Dante "Joel" wrote: You simply need to add RowtoColumns in 2 places in the code. Also Rows.Count will give you the corrrect number of rows depending on the version of Excel you are using. Columns("A:A").TextToColumns _ Destination:=Range("A1"), _ DataType:=xlDelimited, _ ConsecutiveDelimiter:=False, _ Comma:=True Sub LargeFileImport() 'Dimension Variables Dim ResultStr As String Dim FileName As String Dim FileNum As Integer Dim Counter As Double Dim Filt As String Dim FilterIndex As Integer Dim Titre As String Filt = "Text File (*.txt),*.txt," & _ "Fichiers CSV (*.csv),*.csv," 'Ask User for File's Name FileName = Application.GetOpenFilename _ (FileFilter:=Filt, _ FilterIndex:=FilterIndex, _ Title:=Titre) '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 If ActiveCell.Row = Rows.Count Then 'If On The Last Row Then Add A New Sheet Columns("A:A").TextToColumns _ Destination:=Range("A1"), _ DataType:=xlDelimited, _ ConsecutiveDelimiter:=False, _ Comma:=True 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 Columns("A:A").TextToColumns _ Destination:=Range("A1"), _ DataType:=xlDelimited, _ ConsecutiveDelimiter:=False, _ Comma:=True 'Close The Open Text File Close 'Remove Message From Status Bar Application.StatusBar = False End Sub "Dante Huapaya" wrote: Hi All, I'm using this code (extracted from Microsoft site), this working fine, but this importing my text file in one column, so my text file has 7 columns Field1 - length 26 Field2 - length 15 Field3 - length 14 Field4 - length 21 Field5 - length 18 Field6 - length 3 Field7 - length 17 So I need these columns separated Thanks for your help Here the code: Sub LargeFileImport() 'Dimension Variables Dim ResultStr As String Dim FileName As String Dim FileNum As Integer Dim Counter As Double Dim Filt As String Dim FilterIndex As Integer Dim Titre As String Filt = "Text File (*.txt),*.txt," & _ "Fichiers CSV (*.csv),*.csv," 'Ask User for File's Name FileName = Application.GetOpenFilename _ (FileFilter:=Filt, _ FilterIndex:=FilterIndex, _ Title:=Titre) '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 = 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Importing CSV file (saved as Text) into XL as Text -- over 60 colu | Excel Discussion (Misc queries) | |||
Importing Text File | Excel Programming | |||
Importing text file, only option to edit existing file | Excel Discussion (Misc queries) | |||
Importing text file | Excel Programming | |||
importing text file, removing data and outputting new text file | Excel Programming |