'Option Explicit'Comment Out For Simplified code.
Sub SHimpGlifiedBasicMacroTextToExcel()
On Err GoTo TheEnd
DtaFleNm = Application.GetOpenFilename(FileFilter:="Hallo,*.txt,")
If DtaFleNm = False Then Exit Sub
Sp = Application.InputBox("Enter a separator character.", Type:=2)
If Sp = vbNullString Then Exit Sub
StCm = ActiveCell.Column
Rw = ActiveCell.Row
Open DtaFleNm For Input As 3
Do Until EOF(3)
Line Input #3, DtaFleLn
If Right(DtaFleLn, 1) <> Sp Then DtaFleLn = DtaFleLn & Sp
Cm = StCm
TxtPs = 1
SpPs = InStr(TxtPs, DtaFleLn, Sp)
While SpPs >= 1
TxtDta = Mid(DtaFleLn, TxtPs, SpPs - TxtPs)
ActiveSheet.Cells(Rw, Cm).Value = TxtDta
TxtPs = SpPs + 1
Cm = Cm + 1
SpPs = InStr(TxtPs, DtaFleLn, Sp)
Wend
Rw = Rw + 1
Loop
TheEnd:
Close 3
End Sub
'
'
'
Sub BasicMacroTextFileToExcel() ' http://www.mrexcel.com/forum/excel-questions/795675-run-macro-txt-file.html#post3917591
' Very Simply alternative to the Spreadsheet way using Wizzard, Opening as text File in Excel etc
'
'First Bit can be ommited if Fielnames are always known. It basically gets all the File info.
'Note text File And Excel File must not be in the same Directory as you are being asked for it here.
On Err GoTo TheEnd ' If anything goes wrong go to the end rather than crasching!
'
'First Bit: TextDataFileName and seperator input
Dim Sp As String ' Assign an initial location where further info about the string will be added later
Dim DtaFleNm As Variant 'The DataFilename itself is a string. But a Variant is specified as you may have a Boolean input in the Dialogue box if the user cancels
DtaFleNm = Application.GetOpenFilename(FileFilter:="Hallo,*.txt,") 'The Application Method GetOpenFilename Displays the standard Open dialog box. The optional arguments:= writes a name,limits type of files looked for
If DtaFleNm = False Then Exit Sub 'Stops Porgram if User Cancels (which gives the boolean False)
Let Sp = Application.InputBox("Enter a separator character.", Type:=2) 'prompts user for separator character
If Sp = vbNullString Then Exit Sub 'Stops Porgram if User Cancels (which gives an empty string)
'End First Bit TextDataFileName and seperator input
'
' 'If you want to skip the first bit, delete it or comment it out and type in the text File path and seperator in lines like this:
' Dim DtaFleNm As String
' Let DtaFleNm = ThisWorkbook.Path & "\TextDataFile.txt"
' Dim Sp As String
' Let Sp = "|"
' 'Note: If you do it this way you should put the Active Excel File and Data Text File in the same Directory
'
'Start Main Bit: Get data from text file line by line, then for each line go through every Column. As each data bit is found it is put in the approriate place in the Excel File
'Before you start You should select cell
Dim Rw As Long, StCm As Long, Cm As Long 'Whole Numbers for rows and columns where the Split data line from the Text file goes. Long is a Big whole Number limit (-2,147,483,648 to 2,147,483,647) If you need some sort of validation the value should only be within the range of a Byte/Integer otherwise there's no point using anything but Long.--upon/after 32-bit, Integers (Short) need converted internally anyways, so a Long is actually faster.
Let StCm = ActiveCell.Column 'where you want to start data to come in. Column is then reset back to left in loop
Let Rw = ActiveCell.Row 'Row increases downwards so the initial and loop count can be same variable
Dim DtaFleLn As String 'Using our method we bring in a line from the text File at a time
Dim TxtPs As Byte 'text character position in text file looking form left
Dim SpPs As Byte 'position of seperater lookinf from left
Dim TxtDta As String 'String used temporarily for each Text data value
Open DtaFleNm For Input As 3 ' Sort of opens up a data highway and gives it route number (here 3) to distinguisch it from any others currently open. (If you do not know how many Highways are already open and have a number, then use instead of a number someting like #HgWyNm here, and HgWyNm everyhwere where the number is. This gives it the next available number (Remember to Dim it at the start also.))
Do Until EOF(3) 'Do until going your at the end of the data highway
Line Input #3, DtaFleLn 'Bring in a line from text file(Automatically goes to next line or next part of highway after this command. So we need mo row count for the data file.)
If Right(DtaFleLn, 1) <> Sp Then DtaFleLn = DtaFleLn & Sp 'Glue a seperator on the end of the data file line if there is not one there
Let Cm = StCm 'go to first column
Let TxtPs = 1 ' Initial Data Text Position is at 1 (Ist Left poisition in text file line
Let SpPs = InStr(TxtPs, DtaFleLn, Sp) 'Find position of next seperator
While SpPs >= 1 'As long as a next Seperator position is found, we go througth columns looking for data
Let TxtDta = Mid(DtaFleLn, TxtPs, SpPs - TxtPs) 'this gives the next data value
Let ActiveSheet.Cells(Rw, Cm).NumberFormat = "@" 'This is not allways necerssary but can help avoid annoying problems with data turning into times or dates
Let ActiveSheet.Cells(Rw, Cm).Value = TxtDta 'Put Text data in excel cell
Let ActiveSheet.Cells(Rw, Cm).Value = Trim(ActiveSheet.Cells(Rw, Cm).Value) 'Most not necerssary but can help to clean up data if any extra spaces came in
Let TxtPs = SpPs + 1 'Start of next data is just after next seperator
Let Cm = Cm + 1 'Increase Excel Column number
Let SpPs = InStr(TxtPs, DtaFleLn, Sp) 'Get the next Seperator position by starting at the current data and going right until the next seperator is found
Wend 'go to next text data to the right in the data file Line
Let Rw = Rw + 1 'Increase row number for Excel(The tecxt file automatically goes to the next row after reading a line
Loop 'Go to the next Row in the Data Text file
TheEnd:
Close 3 'Very important to do this. Shuts highway Off. There can be strange errors if you do not do that. (It is here so that even after an error it will be done
End Sub 'BasicMacroTextFileToExcel()
LinkBack URL
About LinkBacks
emilx3 
Register To Reply
Originally Posted by jindon
Bookmarks