Help with VBA / CSV code 97 -vs- 2000

[Anne Troy]

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!

[Mark O'Brien]

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.)

[Mark O'Brien]

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

[Anne Troy]

Thanks, Mark. I'm looking into it.

[Anne Troy]

No response yet, Mark.

[Mark O'Brien]

any response yet DB?