VBA macro to extract text file with both Pipe and comma

[trianna]

Hello everyone,

I need assistance with VBA code that will extract a text file with both Pipe and Comma delimiters without leaving bland columns.

This is what I have so far. Unfortunately, I can get it to do either the Pipe or the Comma but not both without leaving empty columns for the Comma.

Any help is greatly appreciated. Thank you

Sub LoadPipeDelimitedFilesNAME()

Dim idx As Integer

Dim fpath As String

Dim fname As String


idx = 0

fpath = "c:\Users\LOCATION\"

fname = Dir(fpath & "*.txt")

While (Len(fname) > 0)

idx = idx + 1

Sheets.Add.Name = fname

With ActiveSheet.QueryTables.Add(Connection:="TEXT;" _

& fpath & fname, Destination:=Range("A1"))

.Name = "a" & idx

.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 = xlTextQualifierDoubleQuote

.TextFileConsecutiveDelimiter = False

.TextFileTabDelimiter = False

.TextFileSemicolonDelimiter = False

.TextFileCommaDelimiter = True

.TextFileSpaceDelimiter = False

.TextFileOtherDelimiter = "|"

.TextFileColumnDataTypes = Array(1, 1, 1)

.TextFileTrailingMinusNumbers = True

.Refresh BackgroundQuery:=False

fname = Dir

End With

Wend

End Sub

 

[Marc L]

Hi !

As you must see just above the reply frame or just before to log in :
​

Anyway, try TextToColumns method …
​