• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 228
  • Last Modified:

Problem with VB.Net excel sheet insertion!!! VERY URGENT!!! 500 POINTS!

Hy,

I found a code on how to generate and insert values into excel file.

This is the code:

 Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        Dim Ex As New Excel.Application
        Dim W As New Excel.Worksheet
        Dim i As Integer
        W = Ex.Workbooks.Add.Worksheets.Add
        For i = 1 To 20
            W.Cells(i, 1).value = "blah 1"
            W.Cells(i, 2).value = "blah 2"
            W.Cells(i, 3).value = "blah 3"
            W.Cells(i, 4).value = "blah 4"
        Next i
        W.SaveAs("e:\blah.XLS")
        Ex.Workbooks.Close()
    End Sub

I want to know how can i insert into the next line automaticly. So that i wont have to specify the "i" in w.cells line.
Like you would do into a file like new line. i want to insert in a new line and insert data into the first 4 colums of the new row.

Thanks a lot.
0
drcyrus3d
Asked:
drcyrus3d
  • 2
1 Solution
 
KGregCommented:
Cycle through the rows until you hit an empty row.. then add your data..

       i = 1

       While IsNothing(W.Cells(i, 1).Value) AND _
                    IsNothing(W.Cells(i, 2).Value) AND _
                    IsNothing(W.Cells(i, 3).Value) AND _
                    IsNothing(W.Cells(i, 4).Value)

                 i += 1

       End While

        For i = j To j + 20
            W.Cells(i, 1).value = "blah 1"
            W.Cells(i, 2).value = "blah 2"
            W.Cells(i, 3).value = "blah 3"
            W.Cells(i, 4).value = "blah 4"
        Next i


Hope this helps

KGreg
0
 
KGregCommented:
My mistake! WHILE CLAUSE WAS WRONG:


       i = 1

       While NOT (IsNothing(W.Cells(i, 1).Value) AND _
                    IsNothing(W.Cells(i, 2).Value) AND _
                    IsNothing(W.Cells(i, 3).Value) AND _
                    IsNothing(W.Cells(i, 4).Value))

                 i += 1

       End While

        For i = j To j + 20
            W.Cells(i, 1).value = "blah 1"
            W.Cells(i, 2).value = "blah 2"
            W.Cells(i, 3).value = "blah 3"
            W.Cells(i, 4).value = "blah 4"
        Next i


Hope this helps

KGreg
0
 
drcyrus3dAuthor Commented:
thanks a lot for your solution.
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now