none
how to get excel new records can follow last record when reopen VB project since vb project get end(or exit) last time RRS feed

  • Question

  • hello VB masters

         my problem is that i try to make excel new records follow last record not recover last records from  the first record while i reopen my VB project since VB project got end last time.

       my vb project  is for questionare collection, i set option controls to collect people's options and put option results into excel to be saved . later records need to follow former records by the last one. but my vb project can not make it, everytime running vb project , people's options always recover last records from the first record in excel, im Racking your brains and trying all ways in vain. im here to ask your help and thanks lot.

    Tuesday, December 31, 2019 5:56 AM

Answers

  • Hello,

    For VB.NET here are methods to assist getting the last used row by sheet name or by sheet name and column.

    Option Strict On
    Option Infer Off
    
    Imports Excel = Microsoft.Office.Interop.Excel
    Imports Microsoft.Office
    Imports System.Runtime.InteropServices
    
    Module ExcelCode
    
        ''' <summary>
        ''' Get last used row in sheetname
        ''' </summary>
        ''' <param name="FileName">path and filename to excel file to work with</param>
        ''' <param name="SheetName">Worksheet name to get information</param>
        ''' <returns>-1 if issues else lasted used row</returns>
        ''' <remarks></remarks>
        Public Function UsedRows(ByVal FileName As String, ByVal SheetName As String) As Integer
    
            Dim RowsUsed As Integer = -1
    
            If IO.File.Exists(FileName) Then
                Dim xlApp As Excel.Application = Nothing
                Dim xlWorkBooks As Excel.Workbooks = Nothing
                Dim xlWorkBook As Excel.Workbook = Nothing
                Dim xlWorkSheet As Excel.Worksheet = Nothing
                Dim xlWorkSheets As Excel.Sheets = Nothing
    
                xlApp = New Excel.Application
                xlApp.DisplayAlerts = False
                xlWorkBooks = xlApp.Workbooks
                xlWorkBook = xlWorkBooks.Open(FileName)
    
                xlApp.Visible = False
    
                xlWorkSheets = xlWorkBook.Sheets
    
                For x As Integer = 1 To xlWorkSheets.Count
    
                    xlWorkSheet = CType(xlWorkSheets(x), Excel.Worksheet)
    
                    If xlWorkSheet.Name = SheetName Then
                        Dim xlCells As Excel.Range = Nothing
                        xlCells = xlWorkSheet.Cells
    
                        Dim thisRange As Excel.Range = xlCells.SpecialCells(Excel.XlCellType.xlCellTypeLastCell)
    
                        RowsUsed = thisRange.Row
                        Marshal.FinalReleaseComObject(thisRange)
                        thisRange = Nothing
    
                        Marshal.FinalReleaseComObject(xlCells)
                        xlCells = Nothing
    
                        Exit For
                    End If
    
                    Marshal.FinalReleaseComObject(xlWorkSheet)
                    xlWorkSheet = Nothing
    
                Next
    
                xlWorkBook.Close()
                xlApp.UserControl = True
                xlApp.Quit()
    
                ReleaseComObject(xlWorkSheets)
                ReleaseComObject(xlWorkSheet)
                ReleaseComObject(xlWorkBook)
                ReleaseComObject(xlWorkBooks)
                ReleaseComObject(xlApp)
            Else
                Throw New Exception("'" & FileName & "' not found.")
            End If
    
            Return RowsUsed
    
        End Function
        ''' <summary>
        ''' Get last used row for a single column
        ''' </summary>
        ''' <param name="FileName"></param>
        ''' <param name="SheetName"></param>
        ''' <param name="Column"></param>
        ''' <returns></returns>
        ''' <remarks></remarks>
        Public Function UseRowsdByColumn(ByVal FileName As String, ByVal SheetName As String, ByVal Column As String) As Integer
            Dim LastRowCount As Integer = 1
    
            Dim xlApp As Excel.Application = Nothing
            Dim xlWorkBooks As Excel.Workbooks = Nothing
            Dim xlWorkBook As Excel.Workbook = Nothing
            Dim xlWorkSheet As Excel.Worksheet = Nothing
            Dim xlWorkSheets As Excel.Sheets = Nothing
    
            xlApp = New Excel.Application
            xlApp.DisplayAlerts = False
            xlWorkBooks = xlApp.Workbooks
            xlWorkBook = xlWorkBooks.Open(FileName)
    
            xlApp.Visible = False
    
            xlWorkSheets = xlWorkBook.Sheets
    
            For x As Integer = 1 To xlWorkSheets.Count
    
                xlWorkSheet = CType(xlWorkSheets(x), Excel.Worksheet)
    
                If xlWorkSheet.Name = SheetName Then
    
                    Dim xlCells As Excel.Range = xlWorkSheet.Cells()
                    Dim xlTempRange1 As Excel.Range = xlCells.SpecialCells(Excel.XlCellType.xlCellTypeLastCell)
                    Dim xlTempRange2 As Excel.Range = xlWorkSheet.Rows
    
    
                    Dim xlTempRange3 As Excel.Range = xlWorkSheet.Range(Column.ToUpper & xlTempRange2.Count)
                    Dim xlTempRange4 As Excel.Range = xlTempRange3.End(Excel.XlDirection.xlUp)
    
                    LastRowCount = xlTempRange4.Row
    
                    Marshal.FinalReleaseComObject(xlTempRange4)
                    xlTempRange4 = Nothing
    
                    Marshal.FinalReleaseComObject(xlTempRange3)
                    xlTempRange3 = Nothing
    
                    Marshal.FinalReleaseComObject(xlTempRange2)
                    xlTempRange2 = Nothing
    
                    Marshal.FinalReleaseComObject(xlTempRange1)
                    xlTempRange1 = Nothing
    
                    Marshal.FinalReleaseComObject(xlCells)
                    xlCells = Nothing
    
                End If
    
                Marshal.FinalReleaseComObject(xlWorkSheet)
                xlWorkSheet = Nothing
    
            Next
    
            xlWorkBook.Close()
            xlApp.UserControl = True
            xlApp.Quit()
    
            ReleaseComObject(xlWorkSheets)
            ReleaseComObject(xlWorkSheet)
            ReleaseComObject(xlWorkBook)
            ReleaseComObject(xlWorkBooks)
            ReleaseComObject(xlApp)
    
            Return LastRowCount
    
        End Function
    
        Public Sub ReleaseComObject(ByVal obj As Object)
            Try
                If obj IsNot Nothing Then
                    Marshal.ReleaseComObject(obj)
                    obj = Nothing
                End If
            Catch ex As Exception
                obj = Nothing
            End Try
        End Sub
    
    End Module
    


    Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange

    • Marked as answer by jenniferhe66 Thursday, January 2, 2020 8:47 AM
    Tuesday, December 31, 2019 11:00 AM
    Moderator

All replies

  • Hi,

    Your question is more related to VBA, not VB.NET.

    And this forum is discussing and asking questions about the VB.NET.

    Ask in the following forum:

    https://social.msdn.microsoft.com/Forums/vstudio/en-US/home?forum=isvvba

    Thank you for your understanding.

    Thank you for participating in the forum activities.

    Best Regards,

    Julie


    MSDN Community Support Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Tuesday, December 31, 2019 6:34 AM
    Moderator
  • Hello,

    For VB.NET here are methods to assist getting the last used row by sheet name or by sheet name and column.

    Option Strict On
    Option Infer Off
    
    Imports Excel = Microsoft.Office.Interop.Excel
    Imports Microsoft.Office
    Imports System.Runtime.InteropServices
    
    Module ExcelCode
    
        ''' <summary>
        ''' Get last used row in sheetname
        ''' </summary>
        ''' <param name="FileName">path and filename to excel file to work with</param>
        ''' <param name="SheetName">Worksheet name to get information</param>
        ''' <returns>-1 if issues else lasted used row</returns>
        ''' <remarks></remarks>
        Public Function UsedRows(ByVal FileName As String, ByVal SheetName As String) As Integer
    
            Dim RowsUsed As Integer = -1
    
            If IO.File.Exists(FileName) Then
                Dim xlApp As Excel.Application = Nothing
                Dim xlWorkBooks As Excel.Workbooks = Nothing
                Dim xlWorkBook As Excel.Workbook = Nothing
                Dim xlWorkSheet As Excel.Worksheet = Nothing
                Dim xlWorkSheets As Excel.Sheets = Nothing
    
                xlApp = New Excel.Application
                xlApp.DisplayAlerts = False
                xlWorkBooks = xlApp.Workbooks
                xlWorkBook = xlWorkBooks.Open(FileName)
    
                xlApp.Visible = False
    
                xlWorkSheets = xlWorkBook.Sheets
    
                For x As Integer = 1 To xlWorkSheets.Count
    
                    xlWorkSheet = CType(xlWorkSheets(x), Excel.Worksheet)
    
                    If xlWorkSheet.Name = SheetName Then
                        Dim xlCells As Excel.Range = Nothing
                        xlCells = xlWorkSheet.Cells
    
                        Dim thisRange As Excel.Range = xlCells.SpecialCells(Excel.XlCellType.xlCellTypeLastCell)
    
                        RowsUsed = thisRange.Row
                        Marshal.FinalReleaseComObject(thisRange)
                        thisRange = Nothing
    
                        Marshal.FinalReleaseComObject(xlCells)
                        xlCells = Nothing
    
                        Exit For
                    End If
    
                    Marshal.FinalReleaseComObject(xlWorkSheet)
                    xlWorkSheet = Nothing
    
                Next
    
                xlWorkBook.Close()
                xlApp.UserControl = True
                xlApp.Quit()
    
                ReleaseComObject(xlWorkSheets)
                ReleaseComObject(xlWorkSheet)
                ReleaseComObject(xlWorkBook)
                ReleaseComObject(xlWorkBooks)
                ReleaseComObject(xlApp)
            Else
                Throw New Exception("'" & FileName & "' not found.")
            End If
    
            Return RowsUsed
    
        End Function
        ''' <summary>
        ''' Get last used row for a single column
        ''' </summary>
        ''' <param name="FileName"></param>
        ''' <param name="SheetName"></param>
        ''' <param name="Column"></param>
        ''' <returns></returns>
        ''' <remarks></remarks>
        Public Function UseRowsdByColumn(ByVal FileName As String, ByVal SheetName As String, ByVal Column As String) As Integer
            Dim LastRowCount As Integer = 1
    
            Dim xlApp As Excel.Application = Nothing
            Dim xlWorkBooks As Excel.Workbooks = Nothing
            Dim xlWorkBook As Excel.Workbook = Nothing
            Dim xlWorkSheet As Excel.Worksheet = Nothing
            Dim xlWorkSheets As Excel.Sheets = Nothing
    
            xlApp = New Excel.Application
            xlApp.DisplayAlerts = False
            xlWorkBooks = xlApp.Workbooks
            xlWorkBook = xlWorkBooks.Open(FileName)
    
            xlApp.Visible = False
    
            xlWorkSheets = xlWorkBook.Sheets
    
            For x As Integer = 1 To xlWorkSheets.Count
    
                xlWorkSheet = CType(xlWorkSheets(x), Excel.Worksheet)
    
                If xlWorkSheet.Name = SheetName Then
    
                    Dim xlCells As Excel.Range = xlWorkSheet.Cells()
                    Dim xlTempRange1 As Excel.Range = xlCells.SpecialCells(Excel.XlCellType.xlCellTypeLastCell)
                    Dim xlTempRange2 As Excel.Range = xlWorkSheet.Rows
    
    
                    Dim xlTempRange3 As Excel.Range = xlWorkSheet.Range(Column.ToUpper & xlTempRange2.Count)
                    Dim xlTempRange4 As Excel.Range = xlTempRange3.End(Excel.XlDirection.xlUp)
    
                    LastRowCount = xlTempRange4.Row
    
                    Marshal.FinalReleaseComObject(xlTempRange4)
                    xlTempRange4 = Nothing
    
                    Marshal.FinalReleaseComObject(xlTempRange3)
                    xlTempRange3 = Nothing
    
                    Marshal.FinalReleaseComObject(xlTempRange2)
                    xlTempRange2 = Nothing
    
                    Marshal.FinalReleaseComObject(xlTempRange1)
                    xlTempRange1 = Nothing
    
                    Marshal.FinalReleaseComObject(xlCells)
                    xlCells = Nothing
    
                End If
    
                Marshal.FinalReleaseComObject(xlWorkSheet)
                xlWorkSheet = Nothing
    
            Next
    
            xlWorkBook.Close()
            xlApp.UserControl = True
            xlApp.Quit()
    
            ReleaseComObject(xlWorkSheets)
            ReleaseComObject(xlWorkSheet)
            ReleaseComObject(xlWorkBook)
            ReleaseComObject(xlWorkBooks)
            ReleaseComObject(xlApp)
    
            Return LastRowCount
    
        End Function
    
        Public Sub ReleaseComObject(ByVal obj As Object)
            Try
                If obj IsNot Nothing Then
                    Marshal.ReleaseComObject(obj)
                    obj = Nothing
                End If
            Catch ex As Exception
                obj = Nothing
            End Try
        End Sub
    
    End Module
    


    Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange

    • Marked as answer by jenniferhe66 Thursday, January 2, 2020 8:47 AM
    Tuesday, December 31, 2019 11:00 AM
    Moderator
  • thanks lot, Karen.  im a chinese who learning VB , and have this problem for a long time without getting fixed. im reading your code and i will follow , hope it will work. thank you again
    Thursday, January 2, 2020 8:43 AM
  • no,im coding VB project,not VBA. i need Vb project get numbers from excel, not vb code in excel. thanks
    Thursday, January 2, 2020 8:51 AM