Link to home
Start Free TrialLog in
Avatar of tommym121
tommym121Flag for Canada

asked on

Open a csv files with delimiter rather than comma in VBA

How do I open a text file with  exclamation mark as me cell seperator?


        Set xwb = Workbooks.Open(filename)
        Set xWorkSheet = xwb.Sheets(1)

DO I use some other arguement for Workbooks.Open?
Avatar of wdosanjos
wdosanjos
Flag of United States of America image
It should be something like this:

    Workbooks.OpenText Filename:=filename, _
        StartRow:=1, DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, _
        ConsecutiveDelimiter:=False, Tab:=False, Semicolon:=False, Comma:=False, _
        Space:=False, Other:=True, OtherChar:="!", TrailingMinusNumbers:=True
    set swb = ActiveWorkbook
    set xWorkSheet = xwb.Sheets(1)

Open in new window


I hope this helps.
Avatar of tommym121
tommym121
Flag of Canada image

ASKER

Does not seems to work with this  file, I use ~ instead of ! for OtherChar:="~"
abc.csv
Avatar of wdosanjos
wdosanjos
Flag of United States of America image
Try changing the file extension to .txt and it should work.  (for example, abc.csv.txt)
Avatar of tommym121
tommym121
Flag of Canada image

ASKER


wdosanjos:

Thanks.  It works.  But would you mind tell me why adding the new extension will work.   Thanks.
ASKER CERTIFIED SOLUTION
Avatar of wdosanjos
wdosanjos
Flag of United States of America image
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of tommym121
tommym121
Flag of Canada image

ASKER

Thanks.