Reading CSV file from Excel (VBA)

[Alex]

Hi to all,

dont know if somebody can help me.
I have an excel sheet that is saved from xls to csv.

the sheet as this format :
MSC1 MSCA
MSC2 MSCB
MSC3 MSCC
MSC4 MSCD

each value in a column

When reading the file with the below code, it's seems
that it is skipping a line for each line it reads
ex : would read MSC2 then MSC4 and (with excel 2003 it
would be MSC1 then MSC4...)

Dim arrFileLines()
i = 0

Set objFSO = CreateObject("Scripting.FileSystemObject")
'Name of file saved from excel xls --> CSV
'To be outputed to another file name....
Set objFile = objFSO.OpenTextFile("C:\Temp\TempCN.csv", 1)

Do Until objFile.AtEndOfStream
Redim Preserve arrFileLines(i)
arrFileLines(i) = objFile.ReadLine
i = i + 1
Loop

objFile.Close

For l = Ubound(arrFileLines) to LBound(arrFileLines) Step
1
' writting line to file
Next

Did anybody come in contact with the same problem and how
did you fix it or is there a better way to do this?

Thank you in advance.

Alex

 

[OJ]

Hi Alex,
What are you trying to do here? This will open the file and load each
line into an array using VBs file access system...

Sub ReadFile()
Dim temp() As Variant, lngInc As Long
Open strFile For Input As #1
While Not EOF(1)
ReDim Preserve temp(lngInc)
Line Input #1, temp(lngInc)
Let lngInc = lngInc + 1
Wend
Close #1
End Sub

OJ

 

[Dave Peterson]

Your code worked ok for me in xl2003/winXP.

I added some dim statements and change your loop to step by -1.


Option Explicit
Sub testme()
Dim i As Long
Dim arrFileLines()
Dim objFSO As Object
Dim objFile As Object
Dim l As Long

i = 0

Set objFSO = CreateObject("Scripting.FileSystemObject")
'Name of file saved from excel xls --> CSV
'To be outputed to another file name....
Set objFile = objFSO.OpenTextFile("C:\my documents\excel\book2.csv", 1)

Do Until objFile.AtEndOfStream
ReDim Preserve arrFileLines(i)
arrFileLines(i) = objFile.ReadLine
i = i + 1
Loop

objFile.Close

For l = UBound(arrFileLines) To LBound(arrFileLines) Step -1
Debug.Print l & "--" & arrFileLines(l)
Next

End Sub

And got this in the immediate window.

3--MSC4,MSCD
2--MSC3,MSCC
1--MSC2,MSCB
0--MSC1,MSCA

====
You may want to look at the .csv file in Notepad. Maybe it's not what you
think.

I created an excel file with your data and saved as a .csv. I opened that file
in Notepad and saw this:

MSC1,MSCA
MSC2,MSCB
MSC3,MSCC
MSC4,MSCD

(So I don't think it's the file|saveas that's causing the error.)