Solved

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

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

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
VB.Net Report Printing Issue 3 54
get column names from table in vb.net 8 49
ASP.NET (VB) return a record 2 37
vb.net convert long time to mm:ss 23 12
A while ago, I was working on a Windows Forms application and I needed a special label control with reflection (glass) effect to show some titles in a stylish way. I've always enjoyed working with graphics, but it's never too clever to re-invent …
Parsing a CSV file is a task that we are confronted with regularly, and although there are a vast number of means to do this, as a newbie, the field can be confusing and the tools can seem complex. A simple solution to parsing a customized CSV fi…
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 …

856 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