Id like to start by saying, You all have been a God send for me and i am glad that i joined this fourm. Not only am i learning alot but im able to complete projects that have been thrown aside due to lack of knowledge. So here is my issue. I found this code below on another site and would like to use it or maybe someone has a better idea. I need to import text files into my active sheet.
The text files are created by a magnetic stripe encoder and look like this: "%Fname Lname address city st. zipcode telephone?;member number?"( minus the "" they are there for seperation factor only. % . ; ? are there just as you see them) so im sure the data will have to be trimed. Field names are as follows in this order:
Member Number, First Name, Last Name, Address 1, Address 2, City, St, Zipcode, Phone
What im wanting to do is:
1: read the card with a mag stripe reader into a "collection window" so to speak and have it input the data into the named fields OR
2: have a macro that can open a selection window so the user can choose the text file then have the macro read the text into the named fields.
Sub ImportTxt()
' Modify the constants below according to your import configuration
Const qName = "Pro_Co_Serv_Eval_NM" ' <-- Don't use space chars in name!
Const SheetImport = "ImportData"
Const CellImport = "A1"
Dim fName As Variant
' Display dialog for user to select a text file
fName = Application.GetOpenFilename("Text Files (*.txt), *.txt")
' If cancel then macro ends here
If fName = False Then MsgBox "Canceled": Exit Sub
' Import text file
With Sheets(SheetImport)
.Range(CellImport).CurrentRegion.ClearContents
With .QueryTables.Add(Connection:="TEXT;" & fName, Destination:=.Range(CellImport))
.Name = qName
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.TextFilePromptOnRefresh = False
.TextFilePlatform = 1252
.TextFileStartRow = 4
.TextFileParseType = xlDelimited
.TextFileTextQualifier = xlTextQualifierNone
.TextFileConsecutiveDelimiter = False
.TextFileTabDelimiter = False
.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = False
.TextFileSpaceDelimiter = False
.TextFileOtherDelimiter = "|"
.TextFileTrailingMinusNumbers = True
.Refresh BackgroundQuery:=False
.Delete ' <-- Close quire
End With
' Remove all names added by quire calls
For Each n In .Names
If InStr(1, n.Name, qName, 1) > 0 Then
n.Delete
End If
Next
End With
' Something else
' ...
End Sub
Display More