• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 318
  • Last Modified:

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?
0
tommym121
Asked:
tommym121
  • 3
  • 3
1 Solution
 
wdosanjosCommented:
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.
0
 
tommym121Author Commented:
Does not seems to work with this  file, I use ~ instead of ! for OtherChar:="~"
abc.csv
0
 
wdosanjosCommented:
Try changing the file extension to .txt and it should work.  (for example, abc.csv.txt)
0
Cloud Class® Course: CompTIA Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

 
tommym121Author Commented:

wdosanjos:

Thanks.  It works.  But would you mind tell me why adding the new extension will work.   Thanks.
0
 
wdosanjosCommented:
It seems that Excel has a tight control over its extensions, so for those (.csv, .xlsx, .xls, etc) we cannot override their behavior.
0
 
tommym121Author Commented:
Thanks.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now