Hi Arvi,

Couldn't you rename the file a .txt file? If you then manually open it you get a wizard in which you can specify date formats ans
separators. You might be able to record that in a macro and use that as a start for your own code.

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

"Arvi Laanemets" wrote in message ...
| Hi
|
| A Firm leasing various IT devices has a web page, where customers can see
| various info about their leased devices. There is a possibility to save this
| info as a CSV-file.
|
| When I open such CSV-file directly from Excel, then it's all OK - all data
| is in right columns (there is 22 of them).
| When I open the same CSV-file from VBA (I need to generate rental payments
| table from it), then all data are messed up hopeless. The code which opens
| the CSV-file is:
| ...
| ' Asking for source file
| varFileTitle = "Open source file!"
| varFileFilter = "Templates (*.CSV), *.CSV"
| varSourceFile = Application.GetOpenFilename(Title:=varFileTitle,
| FileFilter:=varFileFilter)
| If varSourceFile = "False" Then
| MsgBox ("Data import was stopped because no source file was
| selected!")
| Exit Sub
| End If
|
| ' Opening source workbook
| Workbooks.Open Filename:=varSourceFile
| ...
|
| The reason is, that the CSV-file is using Estonian settings - semicolon is
| used as data delimiter. But when CSV-file is opened from VBA, comma is used
| as data delimiter instead. (In Estonian settings, comma is used as decimal
| delimiter, and additionally in CSV-table in some columns with text data
| [and in some column headers] the comma is simply used for punctuation).
|
| How to force Excel VBA to use semicolons as delimiters when opening a
| CSV-file. When there doesn't exist a way to do this, I have to open the
| CSV-file as an ASCII file, and to use some heavy programming to read all
| data into Excel - I hope I can avoid this.
|
|
| --
| Arvi Laanemets
| ( My real mail address: arvi.laanemets<attarkon.ee )
|
|