Thanks Thanks:  0
Likes Likes:  0
Results 1 to 6 of 6

Thread: Help with VBA / CSV code 97 -vs- 2000

  1. #1
    MrExcel MVP Anne Troy's Avatar
    Join Date
    Feb 2002
    Location
    Westwood NJ
    Posts
    2,582
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Someone asked me this directly at a tech site that is not a community like this one, where I can ask *everyone* for help. So, if you don't mind, I'll ask it, give him his answer, and then tell him where I got it!

    Here is his post; mostly corrected because he states he's from Italy. If I didn't understand what he meant, I didn't correct it:

    I had a excel sheet written in Excel 97, which contains a macro that, opens a CSV file with field separator as the ~ character.

    Normally, on an Office 97 PC, I modify the international setting so that the list separator is ~ and the CSV file opens corectly.

    Now, in Excel 2000, if I open the CSV file with the macro, the application cannot see the ~ separator. I use the woorkbook.open command.

    If I try to open the CSV file out of the macro directly from the menu bar, and set the file type to a text file, this converts the file correctly.

    I tried to use the Format =6 and specified the delimiter into "~", but it didn't work.

    I also tried to use the opentext function, but it didn't work.


    THANKS ALL!
    ~Anne Troy

  2. #2
    MrExcel MVP Mark O'Brien's Avatar
    Join Date
    Feb 2002
    Location
    Columbus, OH, USA
    Posts
    3,530
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    One of the things I'm not sure of is why the *.CSV file is Tilde (~) Separated Values instead of the Comma Separated Values as the extension suggests.

    What the guy is saying is that he can open this file correctly manually by opening the file and choosing tilde as the delimiter?

    Can he change the extension of the file to *.txt? That would be easier because then e could use code like this:


    Dim sFileName As String
    sFileName = "D:ProjectsBook2.txt"
    Workbooks.OpenText Filename:=sFileName, DataType:=xlDelimited, Other:=True, OtherChar:="~"


    (my test file was tilde delimited.)

  3. #3
    MrExcel MVP Mark O'Brien's Avatar
    Join Date
    Feb 2002
    Location
    Columbus, OH, USA
    Posts
    3,530
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Or, I can rename the file to *.txt, open it, then return it to the old *.csv name, see this fits his needs:


    Public Sub main()
    Dim sOldFileName As String
    Dim sNewFileName As String
    sOldFileName = "D:ProjectsBook2.csv"
    sNewFileName = "D:ProjectsBook2.txt"

    Name sOldFileName As sNewFileName
    Workbooks.OpenText Filename:=sNewFileName, DataType:=xlDelimited, Other:=True, OtherChar:="~"
    Name sNewFileName As sOldFileName
    End Sub


    HTH

  4. #4
    MrExcel MVP Anne Troy's Avatar
    Join Date
    Feb 2002
    Location
    Westwood NJ
    Posts
    2,582
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Thanks, Mark. I'm looking into it.
    ~Anne Troy

  5. #5
    MrExcel MVP Anne Troy's Avatar
    Join Date
    Feb 2002
    Location
    Westwood NJ
    Posts
    2,582
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    No response yet, Mark.


    ~Anne Troy

  6. #6
    MrExcel MVP Mark O'Brien's Avatar
    Join Date
    Feb 2002
    Location
    Columbus, OH, USA
    Posts
    3,530
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    any response yet DB?

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •