CODEWITHSUNDEEP
excelvba
Anand
Anand

Reputation: 1691

Conversion of a pipe delimited text file to xls

I am trying to convert a text file to xls format but the conversion does not seem to work. Although the filename is .csv, the data in the file is actually pipe delimited When opened in Excel, the converted file shows text like in 1 column with delimiters not removed.

Private Sub Workbook_Open()

    Folder = ActiveWorkbook.Path
    Application.DisplayAlerts = False
    CurrentDate = Format(Date, "MM-dd-yyyy")

    Workbooks.Open Filename:=Folder & "\marginRiskSummary631_" & CurrentDate & ".csv", Delimiter:="|", Format:=6
    ActiveWorkbook.SaveAs Filename:=Folder & "\GS_marginRiskSummary631.xls", FileFormat:=56, CreateBackup:=False, AccessMode:=xlExclusive, ConflictResolution:=Excel.XlSaveConflictResolution.xlLocalSessionChanges
    ActiveWorkbook.Close (True)
End Sub

Can someone please help me to do the conversion properly.

Upvotes: 0

Views: 3232

Answers (1)

Dan
Dan

Reputation: 163

I faced the same issue some times ago. After searching I came to the following solution

When I can, I rename the file and change its extension to ".txt" and then it works with the code you have posted. If I cannot rename, then I use the below code to do the conversion (just after the workbook.open method)

ActiveWorkbook.Worksheets(1).Range("A:A").Select
Selection.TextToColumns Destination:=Range("A1"), DataType:=xlDelimited, _
    TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
    Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar _
    :="|"

It does the job usually. Hope this helps.

Upvotes: 1

Related Questions