Excel Issues with iLogic

[rdiamond113]

Hey Everyone,

I'm having an issue with my Excel spreadsheet. I am using a spreadsheet(workbook) that contains all the info for my iProperties for my assembly and I have a rule setup to import that data to my assembly and to also extract data from my assembly to populate cut-lists and buy-out lists in my workbook. The rule is set to run after i save the changes to my assembly. The issues I'm having is when the rule runs, I get a message that the spreadsheet is already open and asks me if i want to reopen it and sometimes I get that pop-up box twice and I'm not sure what I need to do to correct this.

Here is the code for that rule:

GoExcel.Open("E SERIES 12' NO REV MASTER.xlsx")

iProperties.Value("Custom", "PROJECT NAME")=GoExcel.CellValue("F2")
iProperties.Value("Custom", "QUOTE NUMBER")=GoExcel.CellValue("F3")
iProperties.Value("Custom", "REVISION NUMBER:")=GoExcel.CellValue("F5")
iProperties.Value("Custom", "P:NAME")=GoExcel.CellValue("F7")
iProperties.Value("Custom", "ADDRESS")=GoExcel.CellValue("F8")
iProperties.Value("Custom", "CITY, STATE, ZIP")=GoExcel.CellValue("F9")
iProperties.Value("Custom", "PHONE")=GoExcel.CellValue("F10")
iProperties.Value("Custom", "FAX")=GoExcel.CellValue("F11")
iProperties.Value("Custom", "P:ATTENTION")=GoExcel.CellValue("F12")
iProperties.Value("Custom", "S:ATTENTION")=GoExcel.CellValue("F19")
iProperties.Value("Custom", "DEPARTMENT:")=GoExcel.CellValue("F20")
iProperties.Value("Custom", "EMAIL")=GoExcel.CellValue("F21")
iProperties.Value("Custom", "CARBON COPY")=GoExcel.CellValue("F22")
iProperties.Value("Custom", "REV:")=GoExcel.CellValue("I8")
iProperties.Value("Custom", "PROJECT COORDINATOR")=GoExcel.CellValue("I11")
iProperties.Value("Custom", "JOB NUMBER")=GoExcel.CellValue("I15")
iProperties.Value("Custom", "BY:")=GoExcel.CellValue("I16")
iProperties.Value("Custom", "ISSUED:")=GoExcel.CellValue("I17")
iProperties.Value("Custom", "JOB NAME:")=GoExcel.CellValue("I18")
iProperties.Value("Custom", "CUSTOMER:")=GoExcel.CellValue("I19")
iProperties.Value("Custom", "MATERIAL TYPE:")=GoExcel.CellValue("F26")
iProperties.Value("Custom", "FINISH:")=GoExcel.CellValue("I22")
iProperties.Value("Custom", "COLOR:")=GoExcel.CellValue("I23")
iProperties.Value("Custom", "COMM:")=GoExcel.CellValue("I24")
iProperties.Value("Custom", "SPECIAL PROCESS:")=GoExcel.CellValue("I25")
iProperties.Value("Custom", "NO. BKS:")=GoExcel.CellValue("K25")
iProperties.Value("Custom", "B/D:")=GoExcel.CellValue("K26")
iProperties.Value("Custom", "SEAM TYPE:")=GoExcel.CellValue("F27")
iProperties.Value("Custom", "TOP REVEAL:")=GoExcel.CellValue("F28")
iProperties.Value("Custom", "MID REVEAL:")=GoExcel.CellValue("F29")
iProperties.Value("Custom", "BASE REVEAL:")=GoExcel.CellValue("F30")
iProperties.Value("Custom", "STACK JOINT:")=GoExcel.CellValue("F31")
iProperties.Value("Custom", "SKIN FINISH:")=GoExcel.CellValue("F33")
iProperties.Value("Custom", "SKIN COLOR:")=GoExcel.CellValue("F34")
iProperties.Value("Custom", "REVEAL FINISH:")=GoExcel.CellValue("F35")
iProperties.Value("Custom", "REVEAL COLOR:")=GoExcel.CellValue("F36")
iProperties.Value("Custom", "DATE:")=GoExcel.CellValue("F4")


'this forces to the spreadsheet the iprop values like "PART NUMBER", "DESCRIPTION"GoExcel.CellValue("E SERIES 12' NO REV MASTER.xlsx", "BUYOUT ITEMS", "E6") = 
iProperties.Value("PF-1:1","Project","Part Number")
GoExcel.CellValue("E SERIES 12' NO REV MASTER.xlsx", "BUYOUT ITEMS", "A6") = 
iProperties.Value("PF-1:1","Project","DESCRIPTION")
GoExcel.CellValue("E SERIES 12' NO REV MASTER.xlsx", "BUYOUT ITEMS", "A7") = 
iProperties.Value("C1:1","Project","DESCRIPTION")
GoExcel.CellValue("E SERIES 12' NO REV MASTER.xlsx", "BUYOUT ITEMS", "E7") = 
iProperties.Value("C1:1","Project","PART NUMBER")
GoExcel.CellValue("E SERIES 12' NO REV MASTER.xlsx", "BUYOUT ITEMS", "A8") = 
iProperties.Value("DUCT LINER:1","Project","DESCRIPTION")
GoExcel.CellValue("E SERIES 12' NO REV MASTER.xlsx", "BUYOUT ITEMS", "E8") = 
iProperties.Value("DUCT LINER:1","Project","PART NUMBER")
GoExcel.CellValue("E SERIES 12' NO REV MASTER.xlsx", "BUYOUT ITEMS", "H8") = 
iProperties.Area("DUCT LINER:1")
GoExcel.CellValue("E SERIES 12' NO REV MASTER.xlsx", "BUYOUT ITEMS", "A9") = 
iProperties.Value("P1:1","Project","DESCRIPTION")
GoExcel.CellValue("E SERIES 12' NO REV MASTER.xlsx", "BUYOUT ITEMS", "E9") = 
iProperties.Value("P1:1","Project","PART NUMBER")
GoExcel.CellValue("E SERIES 12' NO REV MASTER.xlsx", "BUYOUT ITEMS", "D9") = 
Parameter("P1.ipt.EPOST_LENGTH")

'******************************************************************************************************************************************

'this forces the quantities of parts into the user created parameter "quantity" and then forces to spreadsheetS1_quantity = ThisBOM.CalculateQuantity("Model Data", "S1")
GoExcel.CellValue("E SERIES 12' NO REV MASTER.xlsx", "CUTLIST", "B6") = 
Parameter("S1_quantity")

pf1_quantity = ThisBOM.CalculateQuantity("Model Data", "PF-1")
GoExcel.CellValue("E SERIES 12' NO REV MASTER.xlsx", "BUYOUT ITEMS", "B6") = 
Parameter("pf1_quantity")

C1_quantity = ThisBOM.CalculateQuantity("Model Data", "C1")
GoExcel.CellValue("E SERIES 12' NO REV MASTER.xlsx", "BUYOUT ITEMS", "B7") = 
Parameter("C1_quantity")

DUCTLINER_quantity = ThisBOM.CalculateQuantity("Model Data", "DUCT LINER")
GoExcel.CellValue("E SERIES 12' NO REV MASTER.xlsx", "BUYOUT ITEMS", "B8") = 
Parameter("DUCTLINER_quantity")

EPOST_quantity = ThisBOM.CalculateQuantity("Model Data", "P1")
GoExcel.CellValue("E SERIES 12' NO REV MASTER.xlsx", "BUYOUT ITEMS", "B9") = 
Parameter("EPOST_quantity")

'***********************************************************************************************************************************
'this takes the above stretchout info and forces to spreadsheetGoExcel.CellValue("E SERIES 12' NO REV MASTER.xlsx", "CUTLIST", "C6") = 
Parameter("S1_extents_width")
GoExcel.CellValue("E SERIES 12' NO REV MASTER.xlsx", "CUTLIST", "D6") = 
Parameter("S1_extents_length")

'*****************************************************************************************************************************
'this grabs the skin part numbner and puts it into the user parameters slot we created then forces info into excelskin_part_number = iProperties.Value("SKIN-1:1","Project", "Part Number")
GoExcel.CellValue("E SERIES 12' NO REV MASTER.xlsx", "CUTLIST", "E6") = 
Parameter("skin_part_number")


'this grabs the DUCT LINER square footage and  puts it into the user parameters slot we created then forces info into excel....and...DUCTLINER_SQFT = Parameter("DUCT LINER.ipt.DUCTLINER_CONVERT_TO_FEET")
'.....this takes the DUCT LINER square footage dims and converts to 2 decimal placesDUCTLINER_SQFT = Round(DUCTLINER_SQFT,2)
GoExcel.CellValue("E SERIES 12' NO REV MASTER.xlsx", "BUYOUT ITEMS", "H8") = 
Parameter("DUCTLINER_SQFT")


 Dim doc as AssemblyDocument = TryCast(ThisDoc.Document, AssemblyDocument)
 If (doc Is Nothing) Then Return
     
 Dim controlDef as ControlDefinition = ThisApplication.CommandManager.ControlDefinitions.Item("AssemblyRebuildAllCmd")
 controlDef.Execute2(True)
GoExcel.Save

MessageBox.Show("iProperties successfully copied")




My theory is that I might be telling it to open the Excel file too many times.

Any help would be greatly appreciated!!!

[Vladimir.Ananyev]

The simplest solution is to check if the file "E SERIES 12' NO REV MASTER.xlsx" is already opened.  And if yes then save and close it.  After that you may run your existing code that assumes that excel file is not opened.

'excel file name
Dim Filename As String = "c:\temp\GGGGG.xlsx"

'Step 1.  Close workbook if it is already opened
Dim excelApp As Object 
Try 
    excelApp = GetObject(, "Excel.Application")
    For Each wb As Object In excelApp.workbooks
        If UCase(wb.fullname) = UCase(filename) Then
	   'workbook is already opened: Save and close it
	   wb.Save
	   wb.Close   
	   Exit For
        End If
    Next
Catch
Finally
    excelApp = Nothing
End Try

'Step 2.  Open file from disk and fill with iProperties
GoExcel.Open(Filename)
GoExcel.CellValue("A2")=iProperties.Value("Project", "Part Number")		
GoExcel.CellValue("A3")=iProperties.Value("Project", "Description")		
GoExcel.Save
GoExcel.Close

Beep
MsgBox("FILE IS UPDATED")

 Hope this helps.