CODEWITHSUNDEEP
vb6csv
Saurabh Vyas
Saurabh Vyas

Reputation: 35

Read Data from csv file using VB

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

Upvotes: 1

Views: 3407

Answers (2)

ssarabando
ssarabando

Reputation: 3517

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

Upvotes: 1

Bob77
Bob77

Reputation: 13267

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?

Upvotes: 1

Related Questions