Reading .CSV format in Excel Object
Code:
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
Re: Reading .CSV format in Excel Object
two things
When you open a cvs
1) you use Workbooks.OpenText instead of Workbooks.Open
something like
vb Code:
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 :)
Re: Reading .CSV format in Excel Object
if a file is named .csv, but not an actual csv file it will not open correctly
Re: Reading .CSV format in Excel Object
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
Re: Reading .CSV format in Excel Object
Code:
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
Re: Reading .CSV format in Excel Object
I tried it and it works fine...
vb Code:
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?
Re: Reading .CSV format in Excel Object
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
Re: Reading .CSV format in Excel Object
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
Re: Reading .CSV format in Excel Object
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
Re: Reading .CSV format in Excel Object
Quote:
(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
Re: Reading .CSV format in Excel Object
but i thought when opening with excel works, it would be possible to open it by code.. guess not?
Re: Reading .CSV format in Excel Object
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
Re: Reading .CSV format in Excel Object
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?
Re: Reading .CSV format in Excel Object
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...
Re: Reading .CSV format in Excel Object
i fixed it with Local:=true
Other:=False, FieldInfo:=Array(1, 1), Local:=True, TrailingMinusNumbers:=True