Find next empty row in excel sheet

[OPthorsager]

Hi, 

i am in the midst of creating an external rule which i'll be using to "manage" all of my projects for my business. since the amount of clients i have accumulated is increasing i have started to lose track of where i save my files and such. 

Earlier this year i made a macro enabled workbook for a client which is used to automate sales quotes. essentially my excel macro is what i want, but less complex, as i dont really need alot of the extra cross checking which i had implemented in my Excel macro, where it would first check if the quote number existed, if not, then it would write the number to the next empty row on the reference excel file. however if the number existed it would look at the revision number afterwards to check if the revision number on the quote existed. if it didnt exist, but the client mentioned in a third column was different than the client mentioned in the existing quote number, then it would show a dialog box saying something was off, and have the user change either the client or the quote number. the entire code itself is around 400lines or so, so i won't go into the details about it.

what i want to do similarly in the rule im working on in iLogic is basically have inventor read through all rows (only column A) and check if there is any values stored there. if there is a value it will go to the next line and check if that one has a value. this process should repeat itself until it finds a row in which column A  is empty. All the solutions i have found would use a "for x to x" to look up an array. however i dont want to have a specified "to", since the list could technically go on forever.

below is a snippet of the Excel macro mentioned a little further up in my post. There is alot more going on between my definitions and the actual code posted below, however this is not relevant to what im trying to achieve, so i have left it out. 

    Dim ws1 As Worksheet
    Dim wb1 As Workbook

    Dim cellC6 As Range
    Dim cellD6 As Range
    Dim cellB13 As Range
    Dim cellH2 As Range
    Dim lastRow As Long
    Dim newValue As Long
    Dim foundRow As Range
    Dim foundValue As Boolean
 
    Set wb1 = ThisWorkbook
    Set ws1 = ActiveWorkbook.Sheets("Sheet1")
Set cellC6 = ws1.Range("C6")
    
    ' get data from cell D6 in Workbook 1
    Set cellD6 = ws1.Range("D6")
    ' get data from cell B13 in Workbook 1
    Set cellB13 = ws1.Range("B13")
    ' get data from cell H2 in Workbook 1
    Set cellH2 = ws1.Range("H2")
UpdateValues_Restart:
    ' open workbook2 without updating links
    Application.AskToUpdateLinks = False
    On Error Resume Next
    Set wb2 = Workbooks.Open(SetRef, UpdateLinks:=0)

        ' Check if the value exists in column A of Workbook2
        Set foundRow = ws2.Columns("A").Find(what:=cellC6.Value, LookIn:=xlValues, lookat:=xlWhole)
        
        If Not foundRow Is Nothing Then
            ' Value exists in column A of Workbook2
            ' Check if the text in column B matches the text from cell D6 in Workbook1
            foundValue = False
                                If IsEmpty(ws1.Range("C6")) Then
                                lastRow = ws2.Cells(ws2.Rows.Count, "A").End(xlUp).Row
                                newValue = Application.WorksheetFunction.Max(ws2.Range("A1:A" & lastRow)) + 1
                                cellC6.Value = newValue
                                MsgBox ("No quote number set, getting next free number")
                                GoTo UpdateValues_Restart
                                End If
            
            
            For Each cell In ws2.Range("A" & foundRow.Row & ":A" & ws2.Cells(ws2.Rows.Count, "A").End(xlUp).Row)
                If cell.Value = cellC6.Value And ws2.Cells(cell.Row, 2).Value = cellD6.Value Then
                If Not ws2.Cells(cell.Row, 3).Value = cellB13.Value Then
                    foundValue = True
                    ElseIf ws2.Cells(cell.Row, 3).Value = cellB13.Value Then
                    MsgBox ("Quotenumber: " & cellC6.Value & cellD6 & "    " & cellB13.Value & vbCr & "This quote combination already exists for this project, please change letter indicator or change the project to update quote number")
                    wb2.Close SaveChanges:=False
                    Exit Sub
                    End If
                    
                    Exit For
                End If

            Next cell
            
              If foundValue Then


            
            
                ' Both value and text match, find the highest value and update C6 in Workbook1
                lastRow = ws2.Cells(ws2.Rows.Count, "A").End(xlUp).Row
                newValue = Application.WorksheetFunction.Max(ws2.Range("A1:A" & lastRow)) + 1

                'MsgBox IsEmpty(ws1.Range("C6"))
                
                ' Ask for confirmation to add a new row
                response = MsgBox("The QUOTE NUMBER already exists in the reference list with different LETTER INDICATOR. Do you want to add a new row?", vbYesNo + vbQuestion, "Confirmation")
                
                If response = vbYes Then
                    ' Update C6 in Workbook1
                    cellC6.Value = newValue
                    ' Add the new row to Workbook2
                    ws2.Cells(lastRow + 1, 1).Value = cellC6.Value
                    ws2.Cells(lastRow + 1, 2).Value = cellD6.Value
                    ws2.Cells(lastRow + 1, 3).Value = cellB13.Value
                    ws2.Cells(lastRow + 1, 4).Value = cellH2.Value
                End If
            

  As mentioned this is not the entire code i used for my Excel macro, some items might be left out. the posted code is more or less just to give you guys an idea of what i'm trying to achieve. 

below is the spreadsheet i made for my project. i have already sorted out the actual values that inventor has to put into the spreadsheet. however i cannot get it to loop until it finds an empty row and then use that specific row.

the data which is put into the spreadsheet is filled out in a global form, which shows up whenever you create a new part or assembly file. 

i hope my post wasnt too confusing, if you want more info dont hesitate to ask.

any ideas or help is VERY much appreciated, since i'm still somewhat a beginner to iLogic.

[Andrii_Humeniuk]

Hi @OPthorsager . I suggest using the Do - Loop statement and exiting it if the text is empty. Here is an example of using the operator:

Dim sTest() As String = {"123", "456", "789", ""}
Dim iRow As Integer = 0
Do While Not String.IsNullOrEmpty(sTest(iRow))
	MessageBox.Show(sTest(iRow))
	iRow += 1
Loop

 You can see that array 4 has empty text so we only get 3 messages.

[OPthorsager]

This makes sense! however how do i implement this so that it counts the rows in an excel sheet from inventor? 

from my understanding if i want something done within excel from inventor i have to use the GoExcel function. do you know if there is a way to return the row count using this? instead of using the string variable you showed i want this to be column A and count the rows until it hits an empty cell.

i tried implementing your method with below snippet, which resulted in the messagebox returning the current cell value letter by letter, instead of going to the next row to check if its empty.

Dim iRow As Integer = 1
Dim XlVal = GoExcel.CellValue(oExcelList, xlTab, "A" & iRow)
Do While Not String.IsNullOrEmpty(XlVal(iRow))
	MessageBox.Show(XlVal(iRow))
	iRow += 1
Loop

 once its finished spelling out the letters in the current row it returns an array range error

[Andrii_Humeniuk]

In my work, I use the following Excel fetch template. Your example will look like this:

Imports Microsoft.Office.Interop
AddReference "Microsoft.Office.Interop.Excel.dll"
Public Sub Main()
	Dim oExcel As Excel.Application = GetExcel()
	
	Dim oWB As Excel.Workbook = oExcel.Workbooks.Open(sExcelFile)
	Dim oWS As Excel.Worksheet = oWB.Worksheets(sNameSheet)
	
	Dim iRow As Integer = 2
	Do While Not String.IsNullOrEmpty(oWS.Range("A" & iRow).Value)
		MessageBox.Show(oWS.Range("A" & iRow).Value)
		iRow += 1
	Loop

	oWB.Close()
	oWS = Nothing
	oWB = Nothing
	oExcel = Nothing
End Sub

Property sExcelFile As String = "PathExcelFile"
Public sNameSheet As String = "NameSheet"

Private Function GetExcel(Optional bVisible As Boolean = False) As Excel.Application
	Dim oXL As Microsoft.Office.Interop.Excel.Application
	Dim oObj As Object = Nothing
	Try
		oObj = GetObject(, "Excel.Application")
		oXL = TryCast(oObj, Microsoft.Office.Interop.Excel.Application)
	Catch
		oObj = CreateObject("Excel.Application")
		oXL = TryCast(oObj, Microsoft.Office.Interop.Excel.Application)
	End Try
	oObj = Nothing
	If oXL IsNot Nothing Then oXL.Visible = bVisible
	Return oXL
End Function

You need to specify the path to the file and the name of the sheet in the book (lines 21-22).

[OPthorsager]

even after running your snippet it cant seem to actually get the excel file, even though i set the property to the full filepath.xlsx. the errorcode appears on line 6 if that helps

is this because i'm using iLogic directly and should be doing this in visual studio or? 

[Andrii_Humeniuk]

The code is intended for iLogic, it is fully functional, make sure to specify the full path and file name. The full path of the file can be copied using Ctrl+Shift+C.

[OPthorsager]

Hi again,

i have now tried using ur code as a 1:1 on a new part file. I didnt change anything apart from the path and sheetname on line 21 and 22.

this is the error i get. 

code used below:

Imports Microsoft.Office.Interop
AddReference "Microsoft.Office.Interop.Excel.dll"
Public Sub Main()
	Dim oExcel As Excel.Application = GetExcel()
	
	Dim oWB As Excel.Workbook = oExcel.Workbooks.Open(sExcelFile)
	Dim oWS As Excel.Worksheet = oWB.Worksheets(sNameSheet)
	
	Dim iRow As Integer = 2
	Do While Not String.IsNullOrEmpty(oWS.Range("A" & iRow).Value)
		MessageBox.Show(oWS.Range("A" & iRow).Value)
		iRow += 1
	Loop

	oWB.Close()
	oWS = Nothing
	oWB = Nothing
	oExcel = Nothing
End Sub

Property sExcelFile As String = "C:\Users\OP\Desktop\ProjektListe.xlsx"
Public sNameSheet As String = "Oversigt"

Private Function GetExcel(Optional bVisible As Boolean = False) As Excel.Application
	Dim oXL As Microsoft.Office.Interop.Excel.Application
	Dim oObj As Object = Nothing
	Try
		oObj = GetObject(, "Excel.Application")
		oXL = TryCast(oObj, Microsoft.Office.Interop.Excel.Application)
	Catch
		oObj = CreateObject("Excel.Application")
		oXL = TryCast(oObj, Microsoft.Office.Interop.Excel.Application)
	End Try
	oObj = Nothing
	If oXL IsNot Nothing Then oXL.Visible = bVisible
	Return oXL
End Function

[Andrii_Humeniuk]

I can suggest placing the file path in line 6 and, accordingly, the name of the sheet in line 7 of the direction. Also make sure the file type is .xlsx and not .xlsm.

Dim oWB As Excel.Workbook = oExcel.Workbooks.Open("C:\Users\OP\Desktop\ProjektListe.xlsx")
Dim oWS As Excel.Worksheet = oWB.Worksheets("Oversigt")

[OPthorsager]

i've tried this too, and i really cant figure out what the cause of the problem is...

the file is located directly on the desktop right now, but i have also tried other locations to see if it was an access issue. the file is .xlsx format aswell. i've also tried to have the sheet directly in the project file directory, but it still didnt want to go further than line 6

[WCrihfield]

Hi guys.  There is actually a property for this on Excel's side.  Check out the Worksheet.UsedRange property, which returns a Range object.  Once you have that, you can use the Range.Rows property or its Range.Columns property (which both also return Range object) and use the Count property to get the Index of the last one being used.  However, I believe this assumes that you have something in your first row and first column.  I have used that little trick for years and it has worked OK for me.

[OPthorsager]

That makes sense. however i am still getting the object reference error, which means inventor still doesnt get to open the excel file... i've tried everything i've been able to find online, i even went and put the 

"Microsoft.Office.Interop.Excel.dll"

file inside of the iLogic addin folder, since i read on some old forum that reference dll's should be put there. its either something in the function which isnt working as intended for me, or its just completely unable to access the microsoft.Office.Interop api´, which doesnt seem to be the case since iLogic seems to include the excel VBA syntax.

i really cannot seem to figure out what is causing this issue and how to fix it... 

i tried changing the

Imports Microsoft.Office.Interop 

 to

Imports Microsoft.Office.Interop.Excel

 Which gave me errors down in the function. which to my understanding means that the function actually works when using 

Imports Microsoft.Office.Interop

when using the code on a newly created part i actually get warnings from Excel asking if i trust this rule to run, which means it does do something within excel. it just cannot seem to find the actual file i want it to open...

Edit: 

I somehow managed to get the code to work after removing the try catch operation and just going straight for the CreateObject function (im guessing this just forces a launch on excel instead of checking to see if its running first) for some reason this solved the issue, but i cant really see why this was the fix...

the function now looks like this

Private Function GetExcel(Optional bVisible As Boolean = False) As Excel.Application
	Dim oXL As Microsoft.Office.Interop.Excel.Application
	Dim oObj As Object = Nothing

		oObj = CreateObject("Excel.Application")
		oXL = TryCast(oObj, Microsoft.Office.Interop.Excel.Application)

	oObj = Nothing
	If oXL IsNot Nothing Then oXL.Visible = bVisible
	Return oXL
End Function

[WCrihfield]

Interacting with another application (like Excel) from Inventor is often a little more complicated of an endeavor than it seems like it should be, and how well it works seems to change over time (with updates and new versions), and from one person to the next.  The 'GetObject' and 'CreateObject' method usually return different Types of objects for me, which can be confusing.  The actual Excel.Application object is an Interface, which we can not use the 'New' keyword to create a new instance of, but there is also an Excel.ApplicationClass Type, which we often get a reference to, which we can use 'New' keyword to get, but should not use directly (for internal use).  That's why we usually include the TryCast statements in there, trying to cast (or convert) the one Type to the other Type, that we can use.

On a side note, when going the UsedRange route, there is a 'method' of the resulting Range object that we can use called Range.SpecialCells.  The first thing it wants as input is a variation of the XLCellType Enum, which there is a variation of to specify the last cell in the used range (XlCellType.xlCellTypeLastCell).  That method returns a Range matching the input criteria, which in this case should be a sing cell.  Then you can use the resulting Range.Row or Range.Column properties, which both return an Integer, which represents the Index of that row or column, where Column A would be 1 (not zero), and so on.

And by the way, all my links have been to the documentation for accessing Excel via VB.NET, not via VBA, because I was assuming you would be using iLogic, which uses vb.net.  But I believe that most of the same objects and properties exist on the VBA side also, if you are planning on keeping your code in VBA macros, instead of iLogic rules.