0

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

3
Contributors
2
Replies
5
Views
6 Years
Discussion Span
Last Post by Deepak Shitole
0
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

0

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

This article has been dead for over six months. Start a new discussion instead.
Take the time to help us to help you. Please be thoughtful and detailed and be sure to adhere to our posting rules.