Hi Friends, 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

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

or better still try select case. btw suspect this should be in vb6 section not vb.net

Here gridCSVToExcel is MSFlexGrid Component used to Show the records of the CSV File.

Dim file_name As String
Dim fnum As Integer
Dim whole_file As String
Dim lines As Variant
Dim one_line As Variant
Dim num_rows As Long
Dim num_cols As Long
Dim the_array() As String
Dim R As Long
Dim C As Long

file_name = App.Path & "\CSV Import\MyCSVFile.csv"
' Load the file.
fnum = FreeFile
Open file_name For Input As fnum
whole_file = Input$(LOF(fnum), #fnum)
Close fnum

' Break the file into lines.
lines = Split(whole_file, vbCrLf)

' Dimension the array.
num_rows = UBound(lines)
one_line = Split(lines(0), ",")
num_cols = UBound(one_line)
ReDim the_array(num_rows, num_cols)

gridCSVToExcel.Rows = num_rows + 1
gridCSVToExcel.Cols = num_cols + 1
For R = 0 To num_rows
If Len(lines(R)) > 0 Then
one_line = Split(lines(R), ",")
For C = 0 To num_cols
the_array(R, C) = one_line(C)
gridCSVToExcel.TextMatrix(R, C) = one_line(C)
Next C
End If
Next R
End Sub

In case pf any issue please feel free to contact me on
s.deepak150@gmail.com

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.