Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations IamaSherpa on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Text to Columns via VBA module 1

Status
Not open for further replies.
cpjeffm

cpjeffm

IS-IT--Management
Mar 9, 2015
73
US
I've got a VBA module built to import a text file but need some help with one thing. The problem is, field 7 of the text file can contain commas and if commas are present, it's splitting the text up instead of leaving the commas in field 7. I also need to leave the double quotes in the data if they are present in the text file. Here is an example of the text file.

"1234","0240","AF-483",47.20,0.00,"NE0004","000, NEW, (ID GMP08A)",0,"","","WT6A112","ma29",1,"99","W",3/3/16,"0000007021","A","",76034,0,"","0","","1",0,"","","","","","","","","","",,"",0,0.00,0.00,"","1097-ma29-76034","",0.00,"459D95A0","","",,"","","1097-ma29-VNE0004","",,"","",""

Here's my VBA code.

Sub ImportINVENT_CAP()
Dim fName As String, LastRow As Long

Sheets("INVENT.CAP").Select
Range("B14").Select

fName = Application.GetOpenFilename("Cap Files (*.cap), *.cap")
If fName = "False" Then Exit Sub

LastRow = Range("B" & Rows.Count).End(xlUp).Row + 1

With ActiveSheet.QueryTables.Add(Connection:="TEXT;" & fName, _
Destination:=Range("B" & LastRow))
.Name = "sample"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.TextFilePromptOnRefresh = False
.TextFilePlatform = 437
.TextFileStartRow = 1
.TextFileParseType = xlDelimited
.TextFileTextQualifier = xlTextQualifierNone
.TextFileConsecutiveDelimiter = False
.TextFileTabDelimiter = False
.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = True
.TextFileSpaceDelimiter = False
.TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, _
1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1)
.TextFileTrailingMinusNumbers = True
.Refresh BackgroundQuery:=False
End With

Range("Q14").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.NumberFormat = "mm/dd/yy;@"

Range("AX14").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.NumberFormat = "mm/dd/yy;@"

Range("BC14").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.NumberFormat = "mm/dd/yy;@"

Cells.Select
Selection.ColumnWidth = 250

Cells.EntireRow.AutoFit
Cells.EntireColumn.AutoFit

Range("B14").Select
End Sub

I tried changing the .TextFileTextQualifier = line to xlTextQualifierDoubleQuote and that fixed the issue with the commas in field 7 but now I'm missing the double quotes in the data.

Any help would be greatly appreciated.
 
Sort by date Sort by votes
Please [highlight #FCE94F]HIGHLIGHT[/highlight] the specific data you are referring to.

Skip,

[glasses]Just traded in my OLD subtlety...
for a NUance![tongue]
 
Upvote 0 Downvote
Code: 
"1234","0240","AF-
483",47.20,0.00,"NE0004","TEST1,TEST2",0,"","","WT6A112","ma29",1,"99",
"W",3/3/16,[highlight #FCAF3E]"0000007021"[/highlight],"A","",76034,0,
[highlight #FCAF3E]""
[/highlight],"0","","1",0,"","","","","","","","","","",,"",0,0.00,0.00
,"","1097-ma29-76034","",0.00,"459D95A0","","",,"","","1097-ma29-
VNE0004","",,"","",""

"2345","0719","AA-
85530C",695.00,0.00,"M70803","TEST",0,"","","W1C322","ma29",1,"246","W"
,9/19/17,[highlight #FCAF3E]"0000039482"[/highlight],"C","",212298,0,
[highlight #FCAF3E]""
[/highlight],"0","","1",0,"","","","","","","","","","",,"",0,0.00,0.00
,"","1097-ma29-212298","",0.00,"3D3EF391","","",,"","","1097-ma29-
VM70803","",,"","",""

"3456","0719","EB-
50928",65.00,0.00,"M70803","TEST",0,"","","WT5BIN4","ma29",1,"246","W",
9/19/17,[highlight #FCAF3E]"0000039479"[/highlight],"C","",212294,0,
[highlight #FCAF3E]""
[/highlight],"0","","1",0,"","","","","","","","","","",,"",0,0.00,0.00
,"","1097-ma29-212294","",0.00,"CCB415AB","","",,"","","1097-ma29-
VM70803","",,"","",""

"4567","0719","FA-
60047",125.00,0.00,"M70803","TEST",0,"","","WT6B313","ma29",1,"246","W"
,9/19/17,[highlight #FCAF3E]"0000039489"[/highlight],"A","",212292,0,
[highlight #FCAF3E]"W"
[/highlight],"0","","1",0,"","","","","","","","","","",,"",0,0.00,0.00
,"","1097-ma29-212292","",0.00,"00687953","","",,"","","1097-ma29-
VM70803","",,"","",""

"5678","0719","HA-
51546",125.00,0.00,"M70803","TEST",0,"","","WT5BIN2","ma29",1,"246","W"
,9/19/17,[highlight #FCAF3E]""[/highlight],"C","",212295,0,[highlight 
#FCAF3E]""
[/highlight],"0","","1",0,"","","","","","","","","","",,"",0,0.00,0.00
,"","1097-ma29-212295","",0.00,"7B4576D7","","",,"","","1097-ma29-
VM70803","",,"","",""

"6789","0719","LB-
51308",95.00,0.00,"M70803","TEST",0,"","","WT6B110","ma29",1,"246","W",
9/19/17,[highlight #FCAF3E]"0000039486"[/highlight],"A","",212289,0,
[highlight #FCAF3E]""
[/highlight],"0","","1",0,"","","","","","","","","","",,"",0,0.00,0.00
,"","1097-ma29-212289","",0.00,"3E3BE998","","",,"","","1097-ma29-
VM70803","",,"","",""

 
Upvote 0 Downvote
Hmmmmmmm?

Even though a column is TEXT, and TEXT columns get bracketed with QUOTES, yet if the TEXT column VALUE is NULL then no QUOTES.

So that would be the rule in columns 9 & 10 (if I counted correctly) as well?

If thats the case...
Code: 
For Each rng In Intersect(ActiveSheet.UsedRange, ActiveSheet.Rows(LastRow))
   If rng.NumberFormat = "@" Then
      For Each r In ActiveSheet.Range(ActiveSheet.Cells(LastRow, rng.Column), rng.End(xlDown))
         [b]If Len(r.Value) > 0 Then _
            r.Value = """" & r.Value & """"[/b]
      Next
   End If
Next

Skip,

[glasses]Just traded in my OLD subtlety...
for a NUance![tongue]
 
Upvote 0 Downvote
Ok that fixed the issue with field 22 but not with field 17. The first 4 lines got quotes but not the 6th line.
 
Upvote 0 Downvote




Well in your example, I see QUOTES on line 6, column 17
[highlight #FCAF3E]"0000039486"[/highlight]

Skip,

[glasses]Just traded in my OLD subtlety...
for a NUance![tongue]
 
Upvote 0 Downvote
If you’re still having problems, please prepare a sheet with the first 10 rows of IMPORT data that has no other processing, and UPLOAD, please.

Skip,

[glasses]Just traded in my OLD subtlety...
for a NUance![tongue]
 
Upvote 0 Downvote
I uploaded the macro enabled spreadsheet after importing the data below. The data contains quotes in field 17 for each line but notice the spreadsheet only contains quotes in field 17 for the first 7 lines. Field 17 in the 8th line doesn't contain any data and then field 17 in the 9th line doesn't have quotes but should.

Code: 
"0001","0240","AF-483",47.20,0.00,"NE0004","TEST, 
TEST",0,"","","WT6A112","ma29",1,"99","W",3/3/16,[highlight 
#FCE94F]"0000007021"
[/highlight],"A","",76034,0,"","0","","1",0,"","","","","","","","","",
"",,"",0,0.00,0.00,"","1097-ma29-
76034","",0.00,"459D95A0","","",,"","","1097-ma29-VNE0004","",,"","",""

"0002","0719","AA-
85530C",695.00,0.00,"M70803","TEST",0,"","","W1C322","ma29",1,"246","W"
,9/19/17,[highlight #FCE94F]"0000039482"
[/highlight],"C","",212298,0,"","0","","1",0,"","","","","","","","",""
,"",,"",0,0.00,0.00,"","1097-ma29-
212298","",0.00,"3D3EF391","","",,"","","1097-ma29-
VM70803","",,"","",""

"0003","0719","EB-
50928",65.00,0.00,"M70803","TEST",0,"","","WT5BIN4","ma29",1,"246","W",
9/19/17,[highlight #FCE94F]"0000039479"
[/highlight],"C","",212294,0,"","0","","1",0,"","","","","","","","",""
,"",,"",0,0.00,0.00,"","1097-ma29-
212294","",0.00,"CCB415AB","","",,"","","1097-ma29-
VM70803","",,"","",""

"0004","0719","FA-
60047",125.00,0.00,"M70803","TEST",0,"","","WT6B313","ma29",1,"246","W"
,9/19/17,[highlight #FCE94F]"0000039489"
[/highlight],"A","",212292,0,"","0","","1",0,"","","","","","","","",""
,"",,"",0,0.00,0.00,"","1097-ma29-
212292","",0.00,"00687953","","",,"","","1097-ma29-
VM70803","",,"","",""

"0005","0719","HA-
51546",125.00,0.00,"M70803","TEST",0,"","","WT5BIN2","ma29",1,"246","W"
,9/19/17,[highlight #FCE94F]"0000039480"
[/highlight],"C","",212295,0,"","0","","1",0,"","","","","","","","",""
,"",,"",0,0.00,0.00,"","1097-ma29-
212295","",0.00,"7B4576D7","","",,"","","1097-ma29-
VM70803","",,"","",""

"0006","0719","LB-
51308",95.00,0.00,"M70803","TEST",0,"","","WT6B110","ma29",1,"246","W",
9/19/17,[highlight #FCE94F]"0000039486"
[/highlight],"A","",212289,0,"","0","","1",0,"","","","","","","","",""
,"",,"",0,0.00,0.00,"","1097-ma29-
212289","",0.00,"3E3BE998","","",,"","","1097-ma29-
VM70803","",,"","",""

"0007","0719","LK-
51312",75.00,0.00,"M70803","TEST",0,"","","WT6B305","ma29",1,"246","W",
9/19/17,[highlight #FCE94F]"0000039485"
[/highlight],"A","",212311,0,"W","0","","1",0,"","","","","","","","","
","",,"",0,0.00,0.00,"","1097-ma29-
212311","",0.00,"0114AE98","","",,"","","1097-ma29-
VM70803","",,"","",""

"0008","0719","QC-
51529",195.00,0.00,"M70803","TEST",0,"","","W3A149","ma29",1,"246","W",
9/19/17,[highlight #FCE94F]""
[/highlight],"C","",212309,0,"","0","","1",0,"","","","","","","","",""
,"",,"",0,0.00,0.00,"","1097-ma29-
212309","",0.00,"E13894F3","","",,"","","1097-ma29-
VM70803","",,"","",""

"0009","0719","SB-
56525A",65.00,0.00,"M70803","TEST",0,"","","WT5BIN4","ma29",1,"246","W"
,9/19/17,[highlight #FCE94F]"0000039481"
[/highlight],"C","",212296,0,"","0","","1",0,"","","","","","","","",""
,"",,"",0,0.00,0.00,"","1097-ma29-
212296","",0.00,"A23039B0","","",,"","","1097-ma29-
VM70803","",,"","",""

"0010","0249","1C-
",75.00,0.00,"M80103","TEST",0,"","","F74","ma29",1,"24","Y",2/26/18,
[highlight #FCE94F]""
[/highlight],"A","",269719,0,"","0","","1",0,"","","","","","","","",""
,"",,"",0,0.00,0.00,"","1097-ma29-
269719","",0.00,"AD8A3A5F","","",,"","","1097-ma29-
VM80103","",,"","",""
 
 https://files.engineering.com/getfile.aspx?folder=ac5b3975-de62-4aa6-9d36-cd3f08694efc&file=TEST.xlsm
Upvote 0 Downvote
I think this will do it for you...
Code: 
'
    With ActiveSheet
        For Each rng In Intersect(.UsedRange, .Rows(LastRow))
           If rng.NumberFormat = "@" Then
              For Each r In .Range(.Cells(LastRow, rng.Column), .Cells(.UsedRange.Rows.Count, rng.Column))
                 If Len(r.Value) > 0 Then _
                    r.Value = """" & r.Value & """"
              Next
           End If
        Next
    End With

Skip,

[glasses]Just traded in my OLD subtlety...
for a NUance![tongue]
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

duytoi
  • Locked
  • Question
How to add a head to the search results in Excel 2016 using VBA
Replies
2
Views
2K
Andrzejek
Andrzejek
GPnew
  • Locked
  • Question
Power Pivot Filter out Rows where data is not in all columns
Replies
14
Views
736
combo
combo
EFFEY
  • Locked
  • Question
I have three columns in excel named 1
Replies
5
Views
290
EFFEY
EFFEY
OCM
  • Locked
  • Question
Compare two columns
Replies
2
Views
1K
OCM
OCM
Andrzejek
  • Locked
  • Question
Compare multiple columns in Excel 1
Replies
4
Views
2K
combo
combo

Part and Inventory Search

Sponsor

Back
Top