Solved

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

Posted on 2004-09-15
3
224 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

Revamp Your Training Process

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action.

Question has a verified solution.

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

Article by: Kraeven
Introduction Remote Share is a simple remote sharing tool, enabling you to see, add and remove remote or local shares. The application is written in VB.NET targeting the .NET framework 2.0. The source code and the compiled programs have been in…
I think the Typed DataTable and Typed DataSet are very good options when working with data, but I don't like auto-generated code. First, I create an Abstract Class for my DataTables Common Code.  This class Inherits from DataTable. Also, it can …
In a recent question (https://www.experts-exchange.com/questions/29004105/Run-AutoHotkey-script-directly-from-Notepad.html) here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…

737 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