Good Afternoon,
I'm brand new here and it's been a long time since I programmed in VBA.
I'm trying to import a text file where one of the fields contains commas as well as carraige return within the field but the fields have double qoutes around them.
Example of the data:
Name, ID, message
"John Smith","123456","Dear Mr. Smith, you are missing a, b, and C. ^p please reach out to us ^p. Thank you, Student Services^p"|
"Tom Smith","123333","Dear Mr. Smith, you are missing X, and. ^p please reach out to us ^p. Thank you, Student Services^p"|
"Jack Miller","145333","Dear Mr. Miller, you are missing G. ^pPlease reach out to us ^p. Thank you, Student Services^p"|
I managed to make the entire row delimited with a pipe to help matters
I have some code that's probably a discrace to anyone who know what they're doing. I'd greatly appreciate if someone can help me out. I am assuming there are 2000 rows in the file.
=============================================
Sub Import2()
Dim Sh As Worksheet
Dim FileName As String
Dim FileNum As Integer
Dim Data As String
Dim Arr As Variant
Set Sh = Worksheets("Sheet1")
FileName = "C:\temp\MIL Data\Friday\MILDataFile.txt"
FileNum = FreeFile
Open FileName For Input As #FileNum
Cells.ClearContents
For i = 1 To 2000
Line Input #FileNum, Data
Arr = WorksheetFunction.Transpose(Split(Data, ","))
Sh.Cells(1, i).Resize(UBound(Arr)).Value = Arr
Columns("A:A").TextToColumns Destination:=Range("A1"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar _
:="|"
Next i
Close #FileNum
====================================
Greatly appreciate any help.
Thank you,
Mike
Bookmarks