Solved

Open a csv files with delimiter rather than comma in VBA

Posted on 2011-02-12
6
291 Views
Last Modified: 2012-05-11
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
Comment
Question by:tommym121
  • 3
  • 3
6 Comments
 
LVL 23

Expert Comment

by:wdosanjos
ID: 34881382
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
 

Author Comment

by:tommym121
ID: 34881428
Does not seems to work with this  file, I use ~ instead of ! for OtherChar:="~"
abc.csv
0
 
LVL 23

Expert Comment

by:wdosanjos
ID: 34881514
Try changing the file extension to .txt and it should work.  (for example, abc.csv.txt)
0
Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 

Author Comment

by:tommym121
ID: 34883936

wdosanjos:

Thanks.  It works.  But would you mind tell me why adding the new extension will work.   Thanks.
0
 
LVL 23

Accepted Solution

by:
wdosanjos earned 500 total points
ID: 34884039
It seems that Excel has a tight control over its extensions, so for those (.csv, .xlsx, .xls, etc) we cannot override their behavior.
0
 

Author Closing Comment

by:tommym121
ID: 34928393
Thanks.
0

Featured Post

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

How to quickly and accurately populate Word documents with Excel data, charts and images (including Automated Bookmark generation) David Miller (dlmille) Synopsis In this article you’ll learn how to use ExcelToWord! to copy data,charts, shapes …
This article describes some techniques which will make your VBA or Visual Basic Classic code easier to understand and maintain, whether by you, your replacement, or another Experts-Exchange expert.
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

747 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now