Read Data from csv file using VB

Question

This is the code i wrote in order to First open a csv file as excel, then find the required three columns, n then read data from them n save the data into another variables showing them in textbox. As about the csv file, it contains many columns out of which my focus is on only 3 columns under title ID, L, Lg.

Problem is Excel doesnt actually open but Excel.exe process runs in task manager. But by this point its not the compile error; Compile error comes at 'Next' Statement. It says Compile Error: Next without For!!!!

I am Confused with this one. Please help me with this one, Thanks in Advance.

Private Sub cmdFind_Click()

Dim xlApp As Excel.Application
Set xlApp = New Excel.Application

Dim X As Double, Y As Double, FleetID As String
Dim F As String, FCol As Integer, LCol As Integer, LgCol As Integer, Srno As Integer, I As Integer


Dim xlWbook As Workbook
Dim xlSht As Excel.Worksheet
Set xlWbook = xlApp.Workbooks.Open("C:\Users\saurabhvyas\Desktop\test VB2\testfile.csv")
xlApp.Visible = True
Set xlSht = xlWbook.Worksheets("sheet1")


For I = 1 To 8 Step 1
    If xlSht.Cells(I, 1).Value = "ID" Then
        FCol = I
    Else
    If xlSht.Cells(I, 1).Value = "L" Then
        LCol = I
    Else
    If xlSht.Cells(I, 1).Value = "Lg" Then
        LgCol = I
    End If
Next I


Set Srno = 2
Do
    If xlSht.Cells(FCol, Srno).Value = Str$(txtF.Text) Then
        Set X = xlSht.Cells(LCol, Srno).Value
        Set Y = xlSht.Cells(LgCol, Srno).Value
    End If
    Srno = Srno + 1
Loop While xlSht.Cells(FCol, Srno).Value = vbNullString 


txtL.Text = Str$(X)
txtLg.Text = Str$(Y)

xlWbook.Close
xlApp.Quit
Excel.Application.Close
Set xlSht = Nothing
Set xlWbook = Nothing
Set xlApp = Nothing

End Sub

Bob77 · Accepted Answer · 2011-02-12 11:52:14Z

1

You can open CSV format text files and operate on them using ADO with the Jet Provider's Text IISAM. Much less clunky than automating Excel. Or you can read the lines as text and Split() them on commas.

What you're doing does open Excel, but you haven't asked Excel to be visible... though I have no idea why you'd want that.

What are you really trying to do?

answered Feb 12, 2011 at 11:52

Bob77

13.3k1 gold badge31 silver badges37 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

ramu Over a year ago

yes,if it is simply to read particular columns,then Text IISAM is suficient.

Saurabh Vyas Over a year ago

Hi, Thanx 4 compile error. L is Latitude, Lg is Longitude. Which i m trying to read from a csv file respective to one among many IDs. My purpose is to read these values.

Saurabh Vyas Over a year ago

Hi, after the compile error for next was gone but it says Compile error for Object required and points to the first line Private Sub CmdFind_Click()

ssarabando · Accepted Answer · 2011-02-12 15:49:51Z

1

As for your compile error, that's because you are missing some End Ifs. Write it as:

For I = 1 To 8 Step 1
    If xlSht.Cells(I, 1).Value = "ID" Then
        FCol = I
    Else
        If xlSht.Cells(I, 1).Value = "L" Then
            LCol = I
        Else
            If xlSht.Cells(I, 1).Value = "Lg" Then
                LgCol = I
            End If
        End If
    End If
Next I

Or as:

For I = 1 To 8 Step 1
    If xlSht.Cells(I, 1).Value = "ID" Then
        FCol = I
    ElseIf xlSht.Cells(I, 1).Value = "L" Then
        LCol = I
    ElseIf xlSht.Cells(I, 1).Value = "Lg" Then
        LgCol = I
    End If
Next I

answered Feb 12, 2011 at 15:49

ssarabando

3,5272 gold badges40 silver badges44 bronze badges

2 Comments

Saurabh Vyas Over a year ago

Hi, Thanx 4 compile error. L is Latitude, Lg is Longitude. Which i m trying to read from a csv file respective to one among many IDs. My purpose is to read these values. But after the compile error for next was gone, it says Compile error for Object required and points to the first line Private Sub CmdFind_Click() .

ssarabando Over a year ago

Do you have the reference to Excel in your project references? If you go to Project > References, do you have a checked entry for Microsoft Excel xx Object Library? (xx is a version).

Collectives™ on Stack Overflow

Read Data from csv file using VB

2 Answers 2

3 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related