Solved

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

Posted on 2004-09-15
3
221 Views
Last Modified: 2010-04-23
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
Comment
Question by:drcyrus3d
  • 2
3 Comments
 
LVL 6

Expert Comment

by:KGreg
ID: 12068432
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
 
LVL 6

Accepted Solution

by:
KGreg earned 500 total points
ID: 12068444
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
 
LVL 1

Author Comment

by:drcyrus3d
ID: 12068548
thanks a lot for your solution.
0

Featured Post

Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Get Top Visible Row of Datagridview 6 33
VB.NET Repostiory Pattern 7 37
VB.Net - TypeInitializer Error 25 28
get column names from table in vb.net 8 30
Introduction When many people think of the WebBrowser (http://msdn.microsoft.com/en-us/library/2te2y1x6%28v=VS.85%29.aspx) control, they immediately think of a control which allows the viewing and navigation of web pages. While this is true, it's a…
Creating an analog clock UserControl seems fairly straight forward.  It is, after all, essentially just a circle with several lines in it!  Two common approaches for rendering an analog clock typically involve either manually calculating points with…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

809 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question