error in running the ilogic

[khanubaid1]

I had developed an ilogic to get the flat pattern dimensions in a specific format exported to excel sheet for all the open part files. But i am getting an error saying Excel cell not found (bad cell address?): "A-93"  i understand the error but i am not able to troubleshoot the same.

 Code is as below: Please some could help regarding this

Sub Main

ThisApplication.CommandManager.ControlDefinitions.Item("AppZoomallCmd").Execute

For Each oDoc As Document In ThisApplication.Documents
        If TypeOf oDoc Is PartDocument Then
            Dim partDoc As PartDocument = oDoc
            If partDoc.ComponentDefinition.Type <> ObjectTypeEnum.kSheetMetalComponentDefinitionObject Then
                'MsgBox("A sheet metal document must be open.")
                Continue For
            End If

            currentStyle = SheetMetal.GetActiveStyle()

					If currentStyle = ("ALUM#11") Then
						ABC = "12002EB5NF"
						XYZ = ") .125 AL"
					End If

					If currentStyle = ("STEEL#11") Then
						ABC = "10101ACS60"
						XYZ = ") 11GA ST"
					End If

					Dim oDoc1 As PartDocument

					oDoc1 = ThisApplication.ActiveDocument

					Dim oCompDef As SheetMetalComponentDefinition

					oCompDef = oDoc1.ComponentDefinition

					If oCompDef.HasFlatPattern = False Then

						oCompDef.Unfold
						oCompDef.FlatPattern.ExitEdit

					End If

					filename2 = ThisDoc.PathAndFileName(False)

					index11 = InStrRev(filename2, "\")

					ActiveFileName = Mid(filename2,index11+1)

					ActiveFileName2 = ActiveFileName.split(".")(0)

					ActiveFileName2Length = Len(ActiveFileName2)

					'index12 = InStrRev(ActiveFileName2,"H")

					'ActiveFileName3 = Left(ActiveFileName2, index12 - 1)
							
					index13 = InStrRev(ActiveFileName2,"H")

					PartName = Right(ActiveFileName2, ActiveFileName2Length - index13 + 1)

					index3 = InStrRev(filename2,"\")

					folderPath=Left(filename2,index3)

					Dim oPath As String = folderPath

					index5 = InStrRev(filename2,"\")
									
					fullFileName = Mid(filename2,index5+1)

					newFileName1 = fullFileName.split(".")(0)

					index4 = InStrRev(newFileName1,"H")

					newFileName = Left(newFileName1,index4-1)


					'define Excel Application object
					excelApp = CreateObject("Excel.Application")
					'set Excel to run visibly, change to false if you want to run it invisibly
					excelApp.Visible = False
					'suppress prompts (such as the compatibility checker)
					excelApp.DisplayAlerts = False

					'define the file to create/open
					myXLS_File = (oPath & newFileName & ".xlsx") ' "C:\Temp\FlatExtent.xlsx"

					'check for existing file 
					If Not Dir(myXLS_File) <> "" Then
						'workbook does NOT exist, so create a new one
						excelWorkbook = excelApp.Workbooks.Add
						excelWorkbook.SaveAs (myXLS_File)
						'close the workbook and the Excel Application
						'uncomment if you want to close the xls file at the end
						excelWorkbook.Close
						excelApp.Quit
						excelApp = Nothing 
					End If

					' Open excel 
						
					Worksheet = myXLS_File
					Sheetno = ("Sheet1")

					GoExcel.Open(Worksheet, Sheetno)

					'If GoExcel.FindRow(myXLS_File,"Sheet1","ITEM",PartName) Then
					'	GoTo Notification
					'End If


					If String.IsNullOrEmpty(GoExcel.CellValue("A" & 1)) Then
						GoExcel.CellValue("A" & 1) = "ITEM"
						GoExcel.CellValue("B" & 1) = "SYM"
						GoExcel.CellValue("C" & 1) = "DESCRIPTION"
						GoExcel.CellValue("D" & 1) = "PART NUMBER"

					End If

					' Define Range
					RowStart = 3
					RowEnd = 100
										
					For count = RowStart To RowEnd
						' If it's blank count it
						If String.IsNullOrEmpty(GoExcel.CellValue("C" & count)) Then 
							i = i + 1
						End If
					Next

					' Next empty row is max rows minus blank rows plus one
					oRow = RowEnd - i + 1
					oRow1 = oRow + 1

					GoExcel.CellValue("A" & oRow) = PartName
					GoExcel.CellValue("C" & oRow) = Round(SheetMetal.FlatExtentsLength,3) & " X " & Round(SheetMetal.FlatExtentsWidth,3) & " " & iProperties.Value("Custom", "Title Line 3")
					GoExcel.CellValue("C" & oRow1) = ("LXW " & Round(SheetMetal.FlatExtentsLength,3) & " X " & Round(SheetMetal.FlatExtentsWidth,3)& XYZ)
					GoExcel.CellValue("D" & oRow) = ABC
					'add a small delay between text field updates
			End If
			Next
			
					Threading.Thread.Sleep(2000) 'in ms

					GoExcel.Save
					GoExcel.Close

					'define Excel Application object
					excelApp = CreateObject("Excel.Application")
					'set Excel to run visibly, change to false if you want to run it invisibly
					excelApp.Visible = False
					'suppress prompts (such as the compatibility checker)
					excelApp.DisplayAlerts = False

					'workbook exists, open it
					excelWorkbook = excelApp.Workbooks.Open(myXLS_File)
					ExcelSheet = ExcelWorkbook.Worksheets(1)

					'set all of the columns to autofit
					excelApp.Columns.AutoFit   

					excelWorkbook.Save

					'close the workbook and the Excel Application
					'uncomment if you want to close the xls file at the end
					excelWorkbook.Close
					excelApp.Quit
					excelApp = Nothing     

					GoExcel.Save
					GoExcel.Close
								
MsgBox("Flat Pattern Extents Copied !")
End Sub

[A.Acheson]

Your running the excel application and go excel in the same rule. It would be best to use one or the other. Otherwise you have lots of opening/closing using different methods resulting in more errors. 

For excel application you can write to a cell using this

Worksheets("Sheet1").Range("A1").Value

Last row with empty cell in column using this formula. You can search standard excel vba methods in stackoverflow 

Dim LastRow as Long
LastRow = Range("A" & Rows.Count).End(xlUp).Row + 1
Worksheets("Sheet1").Range("A" & LastRow).Value = "1234"

[khanubaid1]

thank you for the response.

Can you help with the below code. Actually the code runs but the it shows the same flat length extents for all the part instead of changing for part specific.

Dim oExcel As Object
Dim oBook As Object
Dim oSheet As Object

oExcel = CreateObject("Excel.Application")
oBook = oExcel.Workbooks.Add
oSheet = oBook.Worksheets(1)

oSheet.Cells(1, 1).Value = "Part Name"
oSheet.Cells(1, 2).Value = "Length"
oSheet.Cells(1, 3).Value = "Width"


For Each oDoc As Document In ThisApplication.Documents
        If TypeOf oDoc Is PartDocument Then
            Dim partDoc As PartDocument = oDoc
			
            If partDoc.ComponentDefinition.Type <> ObjectTypeEnum.kSheetMetalComponentDefinitionObject Then
                'MsgBox("A sheet metal document must be open.")
                Continue For
			End If
		 Dim oFlat As FlatPattern = oDoc.ComponentDefinition.FlatPattern



    oSheet.Cells(oSheet.UsedRange.Rows.Count + 1, 1).Value = oDoc.DisplayName
    oSheet.Cells(oSheet.UsedRange.Rows.Count, 2).Value = Round(SheetMetal.FlatExtentsLength,3) & " X " & Round(SheetMetal.FlatExtentsWidth,3) & " " & iProperties.Value("Custom", "Title Line 3")

    
  End If
Next

oExcel.Visible = True
oBook.SaveAs("C:\Users\E0571268\Desktop\MVB\MVB Orders\2023\Jan\MLR0011380_C_006_Shop_02_10_Ship_02_24_TGW_UK\Flats\FlatPatternDimensions.xlsx")

[A.Acheson · Accepted solution]

In this line your using an ilogic snippet that will target the first document you run the rule from but will not move to the next. 

 oSheet.Cells(oSheet.UsedRange.Rows.Count, 2).Value = Round(SheetMetal.FlatExtentsLength,3) & " X " & Round(SheetMetal.FlatExtentsWidth,3) & " " & iProperties.Value("Custom", "Title Line 3")

Just before this you get the flat pattern object but don't use it.

 Dim oFlat As FlatPattern = oDoc.ComponentDefinition.FlatPattern

You can pick up the width and length property of this object. See help file here.  

Syntax

FlatPattern.Length() As Double

Also your iproperty is an ilogic snippet and again linked  in this case is the first document.

. 

iProperties.Value("Custom", "Title Line 3")

iProperty from API Property sets .

oDoc.PropertySets.item(4).Item("Title Line 3").value