Reading .CSV format in Excel Object

[Amien]

Set exlApp = New Excel.Application
Set exlSheet = exlApp.Workbooks.Open("filename.xls").Worksheets(1)

irow = 1

While exlSheet.Cells(irow, 1) <> ""
      TextBox2.Text = TextBox2.Text & exlSheet.Cells(irow, 4) & vbCrLf
      irow = irow + 1
Wend

This works fine if filename is an excel file. But what if i want to use a .CSV file. If i change the filename to filename.csv and then i use exlSheet.Cells(irow, 4), i dont get the right information.

Thans in advanced

[Siddharth Rout]

two things

When you open a cvs

1) you use Workbooks.OpenText instead of Workbooks.Open

something like

Workbooks.OpenText Filename:="P:\sid.csv", Origin:=xlMSDOS, StartRow:=1, _
        DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter _
        :=False, Tab:=True, Semicolon:=False, Comma:=False, Space:=False, _
        Other:=False, FieldInfo:=Array(1, 1), TrailingMinusNumbers:=True

2) CSV file doesn't have multiple worksheets but just one sheet

Try these changes and if you still have questions, post them

[westconn1]

if a file is named .csv, but not an actual csv file it will not open correctly

[anhn]

When use .OpenText with *.csv file, it seems to be all other arguments have no effect : Excel treats *.csv file as a native file type.
To use other arguments, *.csv file must be renamed to *.txt

[Amien]

Set exlApp = New Excel.Application
exlApp.Workbooks.OpenText FileName:="c:\08.CSV", Origin:=xlMSDOS, StartRow:=1, DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo:=Array(1, 1), TrailingMinusNumbers:=True

MsgBox (exlApp.Cells(2, 1))

This will give me a complete line line "1;test;test2;test3;etc" .. i dont get the right cell that i want

tried also Semicolon:=True

thanks for the input so far

[Siddharth Rout]

I tried it and it works fine...

Private Sub CommandButton1_Click()
Set exlApp = New Excel.Application
exlApp.Visible = True
exlApp.Workbooks.OpenText Filename:="p:\Sid.CSV", _
Origin:=xlMSDOS, StartRow:=1, DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, _
Tab:=True, Semicolon:=False, Comma:=False, Space:=False, _
Other:=False, FieldInfo:=Array(1, 1), TrailingMinusNumbers:=True
 
MsgBox (exlApp.Cells(2, 1))
End Sub

Can you upload your csv file?

[Amien]

http://home.wanadoo.nl/martijnolivie...of%20Book1.csv

not the actually .csv file (cant post that one) .. but this file is also not imported correctly.
Tested with your script

[westconn1]

csv file must be separated by comma "," (csv = comma separated values)
yours are separated by semicolon ";"
excel will not work correctly with it, you need to either change the separators in your file or rename your file from .csv to .txt or some other appropriate extension

[Amien]

renaming the .csv to .txt and changing Semicolon:= to True works.
not renaming and changing all ; to , works also.

Why is this? Why cant i keep the .csv extention and the semicolon? when i load the csv file (with semicolon) in Excel it shows just fine

[westconn1]

(csv = comma separated values)

excel treats csv file as exactly that, so if they are not formatted correctly they will not work right when opened by code

[Amien]

but i thought when opening with excel works, it would be possible to open it by code.. guess not?

[Amien]

exlApp.Workbooks.OpenText FileName:=TextBox1.Text, _
Origin:=xlMSDOS, StartRow:=1, DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, _
Tab:=True, Semicolon:=True, Comma:=False, Space:=False, _
Other:=False, FieldInfo:=Array(1, 1), TrailingMinusNumbers:=True

i use this code to read a .text which is really a .csv file .. one of the fields contains a date. That date is a europian date. Then i import the file using the above code and then i use

ExpApp.Cells(1,5) i get the date but only in american type .. so the date is in the original text file 7 january 2008 .. in the ExpApp.Cells(1,5) it will be an American date

how can i fixed this .. very ugent

[si_the_geek]

Right-click on one of the cells and select "Format Cell", then "Number".

Which item is currently selected in the list?

If you change it to Date, and the format you want, does the preview work correctly?

[Siddharth Rout]

Hi Amien

If you want to achieve the same in code then you can use the format() to suit your needs

for example if cell A1 = 7 january 2008 (which is of "dd-mmmm-yyyy" format) and you want a dd/mm/yyyy format then

MsgBox Format("7 january 2008", "dd/mm/yyyy") or
MsgBox Format(Range("A1").Value, "dd/mm/yyyy")

will give you

07/01/2008

Hope this helps...

[Amien]

i fixed it with Local:=true

Other:=False, FieldInfo:=Array(1, 1), Local:=True, TrailingMinusNumbers:=True