Solved

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

Posted on 2004-09-15
3
223 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

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

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

If you're writing a .NET application to connect to an Access .mdb database and use pre-existing queries that require parameters, you've come to the right place! Let's say the pre-existing query(qryCust) in Access takes a Date as a parameter and l…
1.0 - Introduction Converting Visual Basic 6.0 (VB6) to Visual Basic 2008+ (VB.NET). If ever there was a subject full of murkiness and bad decisions, it is this one!   The first problem seems to be that people considering this task of converting…
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…
How to Install VMware Tools in Red Hat Enterprise Linux 6.4 (RHEL 6.4) Step-by-Step Tutorial

756 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