Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 307
  • 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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
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

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

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