Results 1 to 4 of 4

Thread: Find Next Empty Row in Excel and Populate

  1. #1

    Thread Starter
    New Member
    Join Date
    Sep 2013
    Posts
    1

    Find Next Empty Row in Excel and Populate

    I've been learning VB as I go along for this application but the scenario is this:

    I have a VB script running in a label printing program called Bartender. I currently have the script running at the time a user prints a label, it opens up a spreadsheet, updates information, saves then closes the spreadhseet with some code I managed to (just about) throw together as below:



    Set objExcel = CreateObject("Excel.Application")
    Set objWorkbook = objExcel.Workbooks.Open("\\dt01.local\files$\UserFolders\samc\My Documents\Booker Card Printer\Printed Docs.xlsx")
    objExcel.Application.Visible = True
    objWorkbook.WorkSheets(1).Activate
    objWorkbook.WorkSheets(1).Cells(1, 1).Value = "Test value2"

    objExcel.ActiveWorkbook.Save
    objExcel.ActiveWorkbook.Close


    objExcel.Application.Quit


    Using resources I could only find the code which would update a specified cell with the information given (1,1 for example). What I really need is for the code to move to the subsequent blank row below each time a label is printed to avoid overwriting information but I am stumped as to how to write this.

    At this stage only one item is needed to be written to the spreadsheet so it will be restricted to column A, this will be variable data instead of fixed text as the script shows but I am fairly certain that's an easy change I can make, I'd like to get the data sequencing right first.

    Any help would be appreciated

  2. #2
    Wall Poster TysonLPrice's Avatar
    Join Date
    Sep 2002
    Location
    Columbus, Ohio
    Posts
    3,609

    Re: Find Next Empty Row in Excel and Populate

    Someone may come behind me and have a better way and I know there are automated ways in Excel to find the last row but here is something that works. Note it does account for not finding a blank row. I'm curious so I'm going to research a better way to do this:

    Code:
    Imports Microsoft.Office.Interop
    
    Public Class Form1
        Private xl As Excel.Application
        Private xlWorkBook As Excel.Workbook
        Private xlWorksheet As Excel.Worksheet
        Private Sub btnBasicTesting_Click(sender As Object, e As EventArgs) Handles btnBasicTesting.Click
    
            Dim iRow As Integer
    
            xl = New Excel.Application
            xl.Application.Workbooks.Open("C:\TestIt.xlsx")
            xlWorksheet = xl.Sheets(1)
            xlWorksheet.Activate()
            xl.Visible = True
    
            iRow = 1
            With xl
                Do While .Cells(iRow, 1).value <> ""
                    .Cells(iRow, 1).activate()
                    iRow = iRow + 1
                Loop
                .Cells(iRow, 1).value = "Value in next available row\column"
            End With
    
            MsgBox(xl.Cells(iRow, 1).value)
    
            xl.Workbooks(1).Close(SaveChanges:=True)
    
            xl.Quit()
    
        End Sub
    End Class

  3. #3
    Hyperactive Member Vladamir's Avatar
    Join Date
    Feb 2012
    Location
    Miami, FL
    Posts
    486

    Re: Find Next Empty Row in Excel and Populate

    I've had success with this snippet in the past.

    Code:
    ' Find the last row of data on Sheet1 of the Excel File
            'With vExcelSheet1
            '    lRow = .Range("A" & .Rows.Count).End(Excel.XlDirection.xlUp).Row
            'End With

  4. #4
    Karen Payne MVP kareninstructor's Avatar
    Join Date
    Jun 2008
    Location
    Oregon
    Posts
    6,629

    Re: Find Next Empty Row in Excel and Populate

    Here is an example that does used rows and columns done in VS2012. There is an excel file in the app folder where I work with Sheet1, Sheet2, Sheet3 and demo what happens (gracefully) when we attempt to work on a non-existing worksheet. If you don't have VS2012 then the code below should get you rolling.


    Form code
    Code:
    Public Class Form1
        Dim FileName As String = IO.Path.Combine(Application.StartupPath, "W1.xlsx")
        ' Sheet4 does not exists, no blow ups as we assert if sheet exists
        Dim SheetNames As New List(Of String) From {"Sheet1", "Sheet2", "Sheet3", "Sheet4"}
        Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
            DataGridView1.DataSource = Nothing
            Dim Tally As New List(Of ExcelInfo)
            For Each sheet In SheetNames
                Tally.Add(UsedInformation(FileName, sheet))
            Next
            DataGridView1.DataSource = Tally
            DataGridView1.Columns("FileName").Visible = False
            DataGridView1.Columns("UsedRows").DefaultCellStyle.Alignment =
                DataGridViewContentAlignment.MiddleRight
            DataGridView1.Columns("UsedColumns").DefaultCellStyle.Alignment =
                DataGridViewContentAlignment.MiddleRight
        End Sub
    End Class
    Module1.vb
    MOTE: First function is just used rows while the second function does used rows and columns
    Code:
    Option Strict On
    Option Infer On
    
    Imports Excel = Microsoft.Office.Interop.Excel
    Imports Microsoft.Office
    Imports System.Runtime.InteropServices
    ''' <summary>
    ''' Basics for getting last row and column in a worksheet
    ''' </summary>
    ''' <remarks>
    ''' Two-Dot rule is followed
    ''' http://www.siddharthrout.com/2012/08/06/vb-net-two-dot-rule-when-working-with-office-applications-2/
    ''' </remarks>
    Module Module1
        ''' <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 TempRange As Excel.Range = xlCells.SpecialCells(Excel.XlCellType.xlCellTypeLastCell)
    
                        RowsUsed = TempRange.Row
                        Runtime.InteropServices.Marshal.FinalReleaseComObject(TempRange)
                        TempRange = Nothing
    
                        Runtime.InteropServices.Marshal.FinalReleaseComObject(xlCells)
                        xlCells = Nothing
    
                        Exit For
                    End If
    
                    Runtime.InteropServices.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
        Public Function UsedRows_Columns(ByVal FileName As String, ByVal SheetName As String) As Tuple(Of Int32, Int32)
    
            Dim RowsUsed As Integer = -1
            Dim ColsUsed 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 TempRange As Excel.Range = xlCells.SpecialCells(Excel.XlCellType.xlCellTypeLastCell)
    
                        RowsUsed = TempRange.Row
                        ColsUsed = TempRange.Column
    
                        Runtime.InteropServices.Marshal.FinalReleaseComObject(TempRange)
                        TempRange = Nothing
    
                        Runtime.InteropServices.Marshal.FinalReleaseComObject(xlCells)
                        xlCells = Nothing
    
    
                        Exit For
                    End If
    
                    Runtime.InteropServices.Marshal.FinalReleaseComObject(xlWorkSheet)
                    xlWorkSheet = Nothing
                Next
    
                xlWorkBook.Close()
                xlApp.UserControl = True
                xlApp.Quit()
    
                ReleaseComObject(xlWorkSheets)
                ReleaseComObject(xlWorkSheet)
                ReleaseComObject(xlWorkBook)
                ReleaseComObject(xlWorkBooks)
                ReleaseComObject(xlApp)
    
                Return New Tuple(Of Int32, Int32)(RowsUsed, ColsUsed)
    
            Else
                Throw New Exception("'" & FileName & "' not found.")
            End If
    
            Return New Tuple(Of Int32, Int32)(-1, -1)
    
        End Function
        Public Function UsedInformation(ByVal FileName As String, ByVal SheetName As String) As ExcelInfo
    
    
            Dim RowsUsed As Integer = -1
            Dim ColsUsed 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 TempRange As Excel.Range = xlCells.SpecialCells(Excel.XlCellType.xlCellTypeLastCell)
    
                        RowsUsed = TempRange.Row
                        ColsUsed = TempRange.Column
    
                        Runtime.InteropServices.Marshal.FinalReleaseComObject(TempRange)
                        TempRange = Nothing
    
                        Runtime.InteropServices.Marshal.FinalReleaseComObject(xlCells)
                        xlCells = Nothing
    
    
                        Exit For
                    End If
    
                    Runtime.InteropServices.Marshal.FinalReleaseComObject(xlWorkSheet)
                    xlWorkSheet = Nothing
                Next
    
                xlWorkBook.Close()
                xlApp.UserControl = True
                xlApp.Quit()
    
                ReleaseComObject(xlWorkSheets)
                ReleaseComObject(xlWorkSheet)
                ReleaseComObject(xlWorkBook)
                ReleaseComObject(xlWorkBooks)
                ReleaseComObject(xlApp)
    
                Return New ExcelInfo With {.FileName = FileName, .SheetName = SheetName, .UsedRows = RowsUsed, .UsedColumns = ColsUsed}
    
            Else
                Throw New Exception("'" & FileName & "' not found.")
            End If
    
            Return New ExcelInfo With {.FileName = FileName, .SheetName = SheetName, .UsedRows = -1, .UsedColumns = -1}
    
        End Function
        Public Sub ReleaseComObject(ByVal obj As Object)
            Try
                System.Runtime.InteropServices.Marshal.ReleaseComObject(obj)
                obj = Nothing
            Catch ex As Exception
                obj = Nothing
            End Try
        End Sub
    End Module
    ExcelInfo.vb
    Code:
    Public Class ExcelInfo
        Public Property FileName As String
        Public Property SheetName As String
        Public Property UsedRows As Int32
        Public Property UsedColumns As Int32
        Public Sub New()
        End Sub
    End Class
    Name:  AM.jpg
Views: 5288
Size:  32.4 KB
    Attached Files Attached Files

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width