CODEWITHSUNDEEP
vbafilecsv
Eugen Draghici
Eugen Draghici

Reputation: 13

VBA: How read csv file with different structure line?

I want to read data from csv file, but just from line 3 to line 13, where line have same number of elements (6).

Line 1 have 1 element/line (date time) - no comma after first element.

Line 2 have 2 elements/line (number, number) - no comma after second element.

Line 3 to 13 have 6 elements/line (no.1, no.2, no.3....no.6).

Line 14 - to end of file is not interested for me. Because first 2 lines is different for next (3-13), I receive a error: "Run-time error '9': Subscript out of range."

I try to separate reading line, but program beak at line mark with **

What can I do for skip over to this?

This is code:

Sub OpenTextFile()
Dim FilePath As String
FilePath = Application.GetOpenFilename()
Open FilePath For Input As #1

For row_number = 0 To 10
   '     LineItems = Split(LineFromFile, ",")
    Line Input #1, LineFromFile
    If row_number = 0 Then
        LineItems = Split(LineFromFile, ",")
        ActiveCell.Offset(row_number, 0).Value = LineItems(0)
    End If
    If row_number = 1 Then
        LineItems = Split(LineFromFile, ",")
        ActiveCell.Offset(row_number, 0).Value = LineItems(0)
        ActiveCell.Offset(row_number, 1).Value = LineItems(1)
    End If
    If row_number >= 1 Then
        LineItems = Split(LineFromFile, ",")
        ActiveCell.Offset(row_number, 0).Value = LineItems(0)
        ActiveCell.Offset(row_number, 1).Value = LineItems(1)
      **ActiveCell.Offset(row_number, 2).Value = LineItems(2)** ' here break the program
        ActiveCell.Offset(row_number, 4).Value = LineItems(3)
        ActiveCell.Offset(row_number, 5).Value = LineItems(4)
        ActiveCell.Offset(row_number, 5).Value = LineItems(5)
    End If
Next row_number
Close #1
End Sub

and this is a csv file example:

2018/03/27 13:31:52
58.05372,240.2784
30.45,1115.922,88135,0.05049,24.85,33.85
61.9,1662.875,102416,0.5585,56.5,68.5
79.3,1434.352,117602.5,0.10008,72.5,87
93.25,2398.221,225121.5,0.19968,87,104
0,0,0,0,99,100
142.5,388.493,55185.5,0.05043,122.5,158
168.05,261.3842,29628,0.05035,156,175
222.9,305.9312,1955185,0.5479,206,238
226.55,29088.84,1957502,97.045,207,239
261.4,373.4588,36588,0.29764,250.6,267.6
275.15,1440.946,168334,1.1001,268.175,288.5
0
0
0
0

Upvotes: 1

Views: 664

Answers (1)

ashleedawg
ashleedawg

Reputation: 21639

Your code runs fine without the file - I made it print XXXX's in the cells it's trying to populate and got this:

img

I'm taking a bit of a shot in the dark here but...

Perhaps instead of this:

If row_number >= 1 Then

you meant to you use greater than?:

If row_number > 1 Then

?

But note that your code will start populating cells at whichever cell is highlighted (instead of using absolute references), for example if I select D10 and run your code, then D10 will be the top-left corner.

Also not all your variables are declared. Use Option Explicit a at the top of [every] module [forever] to help identify problems like undeclared variables and mishandled objects.

With those changes:

img

Other things to check out:

Upvotes: 1

Related Questions