转到excel中的下一行 [英] To the next row in excel

问题描述

我有一个问题，

使用此代码，我将一些文本写入Excel工作表，然后将其打印出来.

效果很好

但是我想保存文件，并在下一个空行中向工作表添加更多数据

现在它将进入第1行cel a en b

下次必须转到第2 a en b行，依此类推

下一个空行.

 导入 Microsoft.Office.Core
导入 Microsoft.Office.Interop

公共 类 Form1
    私有工作表 As  对象

    私人 子 Form1_Load( ByVal 发​​件人 As 系统.对象， ByVal  e  As  System.EventArgs)句柄  MyBase  .Load
        InitializeComponent()
         Dim  AppExcel  As  对象
         Dim 工作簿 As  对象
        AppExcel = CreateObject(" )


        workBook = AppExcel.Workbooks.Open(My.Application.Info.DirectoryPath& " )
        AppExcel.Visible = 错误
        工作表= workBook.worksheets

        AppExcel.Visible = 真实


        工作表(" ).Cells( 1 ， 1 ).value = Form2.TextBox1.Text
         工作表(" ).Cells( 1 ， 2 ).value = Form2.TextBox2.Text
        
     

        工作表(" ).PrintOut()
       



         Dim  oXL  As 应用
         Dim  oWB  As  Excel.Workbook
         Dim  oSheet  As  Excel.Workbook
         Dim 或 As  Excel.Range


        workBook.Saved = 错误


        ' 确保释放对象引用.
        oRng = 什么都没有
        oSheet = 没什么
        oWB = 没什么
        AppExcel.Quit()
        oXL = 没什么

        GC.Collect()
       
        我 .Close()

    结束 子
结束 类 

解决方案

VBA Excel:

 函数 FirstEmptyRow(oWsh  As 工作表，sCol 字符串，startRow 长) As  长
    FirstEmptyRow = oWsh.Range(sCol& startRow).结束(xlDown).Row
结束 功能 

您可以将其翻译为VB.NET:

 函数 FirstEmptyRow(oWsh  As   Object ，sCol  As  字符串，startRow  As  长) As  长
    返回 oWsh.Range(sCol& startRow.ToString).结束(-4121).行
结束 功能 

用法:

  Sub  Adsasada()
MsgBox FirstEmptyRow(oWb.Worksheets(" )， " ， 1 )
结束 子 

它工作正常，如果您有一些数据...但是，如果没有，函数将返回最后一行，因此...您需要这样的内容:

 函数 FirstEmptyRow(oWsh  As   Object ，sCol  As   String ) As  长
    返回 oWsh.Range(sCol& oWsh.Rows.Count).结束(-4162).行+1
结束 功能 

无论工作表中的行数是多少(MS97-2003-> 65536行，2007-2010-等等；))，函数都将返回正确的值

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.

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

解决方案

VBA Excel:

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:

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:

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:

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

这篇关于转到excel中的下一行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！