Help speeding up the synching of an iPart table and an external excel sheet

[jlcasasHPUG6]

As the title says, I have an external table where we store the data of several thousands of our parts. This data is created based on the input from other programs and sheets, and it cannot be stored within our vault server. I have built a complete structure to be able to create parts automatically as needed in an "idle" machine, and all the files we need  in the factory for the CNC machines, so the technical drawers can do other tasks. However this whole system has a horrible bottleneck with the synching of  the external excel sheet and the ipart tables. 

That synching takes a huge time, basically as i understand the ipart table updates every single time any single cell/parameter changes its value. Thus if we need to create 20 new parts, each one with 10 parameters in the table, it updates 200 times. When the parts are large, the ipart tables have several thousand members, this is process is horribly slow, taking more time probably than the creation of several pdfs, step files, dwg files, uploading dummmies to the vault, and everything else we need. I believe the speed i am getting is around 1-2 minutes to create a new member and copy the parameter information, which is stored normally in around 4-10 cells. 

I have tried disabling updates, using transactions and other stuff, but nothing seems to affect the speed of this process. Does anyone have suggestions? When there are no new members to be added the whole system works like a charm, however, having a few dozen new members slows the system to a slog.

Thanks in advance!

[C_Haines_ENG]

How are you syncing this data from Excel? Do you have a program creating rows in an iPart from the excel sheet?

[jlcasasHPUG6]

Hei C_Haines, 

Yes, it is actually an external irule. Some parts are synched to a excel sheet every time it is run. It was built as a workaround to the fact that the excel sheet has to stored outside the vault and accessed by several users and programs. It can synch new members in both directions and works well enough, the main problem being speed. 

After several tests I concluded the ipart table updates via ilogic will always be slow, with the table being updated every time any value is changed with the only possible improvement being reading a value before writing it in case the value is the same. GoExcel is much faster, however it cannot be used with ipart tables. The only solution would be to use a VBA script within inventor but VBA would need to be installed in all computers and everything tested again... someting to do when/if i have some days between projects, I guess.

[C_Haines_ENG]

You can access the Excel application object through iLogic. I use this code to read and set values because I hate goExcel:

Imports Microsoft.Office.Interop
Imports Microsoft.Office.Interop.Excel 'To use excel
AddReference "Microsoft.Office.Interop.Excel" 'To use excel 
Sub Main

		Dim oPath As String = "C:\New folder\"
		Dim oFileName As String = "Test.xlsx"
		
		Dim WB As Workbook
		Dim WS As Worksheet

		If System.IO.File.Exists(oPath & oFileName)
			'// GET DOCUMENT IF IT EXSISTS
			WB = GetObject(oPath & oFileName)
			WS = WB.Worksheets(1)
		End If

		'// WRITE TO CELL
		WS.Cells(1, 1) = "This is Value in A1"
		
		'// WRITE To RANGE
		WS.Range("A2").Value = "This is Value in A2"
		
		MsgBox("A : " & WS.Cells(1,1).Value & vbLf & "B : " & WS.Range("A2").Value )
		
End Sub

Its a fidgety process as getting excel through the "GetObject" method isn't always dependable but under certain circumstances (The Excel file existing, isn't actively being edited, and you aren't forcing it to show you the application) it works every time. lol

Once you have the object you can run all of the same VBA commands, just formatted through VB.NET.