Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Reading CSV file from Excel (VBA)
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Reading CSV file from Excel (VBA)
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Reading CSV file from Excel (VBA)
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.) Alex wrote: 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 -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
VBA macro for reading text file into Excel | Excel Discussion (Misc queries) | |||
While reading excel file i am getting null values | Excel Programming | |||
While reading excel file i am getting null values | Excel Programming | |||
Reading an excel file in SPSS | Excel Programming | |||
Reading an Excel file after upload | Excel Programming |