Importing csv by code

[SteM]

Hi there,
i wrote some vba code to open a csv file within Excel 2000.
Since the separator is ';' and not ',' i wrote:

Set wb = Workbooks.Open(FILENAME:=filename, ReadOnly:=True, Format:=4)

But the file is not imported correctly.
BTW: if i double-click on the file, Excel are not able to import it but if I
open the file within Excel, the file are ok.

Any idea?

 

[Dave Peterson]

First, I'd rename the .csv file to .txt. .CSV file means something special to
excel and you'll have trouble in your code if you leave the file named .csv.

Then instead of writing the code yourself, try recording a macro when you open
the .txt file manually. You'll be able to specify the separator you want.

 

[Joel]

Try this code. Change Default folder and delimiter.



Sub GetCSVData()

Const ForReading = 1, ForWriting = 2, ForAppending = 3
Const TristateUseDefault = -2, TristateTrue = -1, TristateFalse = 0
Const Delimiter = ","
Set fsread = CreateObject("Scripting.FileSystemObject")

'default folder
Folder = "C:\temp\test"
ChDir (Folder)

FName = Application.GetOpenFilename("CSV (*.csv),*.csv")


RowCount = LastRow + 1
If FName <> "" Then
'open files
Set fread = fsread.GetFile(FName)
Set tsread = fread.OpenAsTextStream(ForReading, TristateUseDefault)

Do While tsread.atendofstream = False

InputLine = tsread.ReadLine

'extract comma seperated data
ColumnCount = 1
Do While InputLine <> ""
DelimiterPosition = InStr(InputLine, Delimiter)
If DelimiterPosition > 0 Then
Data = Trim(Left(InputLine, DelimiterPosition - 1))
InputLine = Mid(InputLine, DelimiterPosition + 1)
Else
Data = Trim(InputLine)
InputLine = ""
End If

Cells(RowCount, ColumnCount) = Data
ColumnCount = ColumnCount + 1
Loop
RowCount = RowCount + 1
Loop

tsread.Close
End If
End Sub