Help with VBA / CSV code 97 -vs- 2000

Anne Troy

MrExcel MVP
Joined
Feb 18, 2002
Messages
2,632
Office Version
  1. 365
Platform
  1. Windows
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!
 

Excel Facts

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.
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:

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

(my test file was tilde delimited.)
 
Upvote 0
Or, I can rename the file to *.txt, open it, then return it to the old *.csv name, see this fits his needs:

<pre>
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</pre>

HTH
 
Upvote 0

Forum statistics

Threads
1,213,482
Messages
6,113,913
Members
448,532
Latest member
9Kimo3

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top