To the next row in excel

Question

0.00/5 (No votes)

See more:

I have a question,

With this code i write some text to a excel sheet en print it out.

Works fine

But i want to save the file , and ad some more data to the sheet in the next empty row

Now it is going to row 1 cel a en b

Next time is has to go to row 2 a en b and so on

The next empty row.

VB

Imports Microsoft.Office.Core
Imports Microsoft.Office.Interop

Public Class Form1
    Private Worksheets As Object

    Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        InitializeComponent()
        Dim AppExcel As Object
        Dim workBook As Object
        AppExcel = CreateObject("Excel.Application")


        workBook = AppExcel.Workbooks.Open(My.Application.Info.DirectoryPath & "\test.xls")
        AppExcel.Visible = False
        Worksheets = workBook.worksheets

        AppExcel.Visible = True


        Worksheets("blad1").Cells(1, 1).value = Form2.TextBox1.Text
         Worksheets("blad1").Cells(1, 2).value = Form2.TextBox2.Text
        
     

        Worksheets("blad1").PrintOut()
       



        Dim oXL As Application
        Dim oWB As Excel.Workbook
        Dim oSheet As Excel.Workbook
        Dim oRng As Excel.Range


        workBook.Saved = False


        ' Make sure that you release object references.
        oRng = Nothing
        oSheet = Nothing
        oWB = Nothing
        AppExcel.Quit()
        oXL = Nothing

        GC.Collect()
       
        Me.Close()

    End Sub
End Class

Posted 22-Aug-11 7:48am

BASSIES

Updated 22-Aug-11 8:44am

TheyCallMeMrJames

v2

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Maciej Los · Answer 1 · 2011-08-22T09:54:00

VBA Excel:

VB

Function FirstEmptyRow(oWsh As Worksheet, sCol As String, startRow As Long) As Long
    FirstEmptyRow = oWsh.Range(sCol & startRow).End(xlDown).Row
End Function

You can translate it to VB.NET:

VB

Function FirstEmptyRow(oWsh As Object, sCol As String, startRow As Long) As Long
    Return oWsh.Range(sCol & startRow.ToString).End(-4121).Row
End Function

Usage:

VB

Sub Adsasada()
MsgBox FirstEmptyRow(oWb.Worksheets("blad1"), "A", 1)
End Sub

It works fine, if you got some data... but if you don't, function returns last row, so... you need something like this:

VB

Function FirstEmptyRow(oWsh As Object, sCol As String) As Long
    Return oWsh.Range(sCol & oWsh.Rows.Count).End(-4162).Row +1
End Function

No matter of rows count in a worksheet (MS97-2003 -> 65536 rows, 2007-2010 - much more ;)), function will return proper value