Faster way to pull data from an external spreadsheet/table

[DRoam]

For a while now we've had an iLogic rule that uses the "GoExcel" functions to pull some values from a spreadsheet on our shared network drive. However, this tends to be a little slow.

I was wondering if anyone knows of a quicker way to accomplish basically the same thing. A couple possibilities I've been looking into:

1. Linking/Embedding the spreadsheet
2. Entering the spreadsheet's data into the VBA side of Inventor

My findings so far:

Linking/Embedding:

Not faster if choosing the "Link" option; only faster if choosing the "Embed" option. However, choosing "Embed" means the spreadsheet is now static and doesn't update with changes the network version. Can iLogic be used to manually update/refresh the "Embedding"?

VBA:

I have no idea how to do this. This was suggested by a colleague of mine but he didn't really go into how to do it. Is this feasible? Perhaps by storing the data of each column as a separate Array variable? And can this data be easily updated to match the network Excel file if it is changed?

Any tips or other suggestions would be greatly appreciated. Thank you!

[JamieVJohnson2]

I interpret the VBA response as a 'don't code thro ilogic, rather code thru VBA'.  Current iLogic editor can use VB.Net code as well as iLogic.  Either way, there are other commands to open and process Excel data besides GoExcel.  With the use of Microsoft.Office.Interop.Excel (com object reference), you can control the opening of the excel file, the reading of its contents, and what and where you put it.  Also keep in mind, merely reading a network file could be a bottleneck.  One you can control by copying the file locally to a temp folder, reading, then destroying (only if necessary).  Below is my standard VB.Net code for opening and getting Excel files as well as closing the Excel application.

    Public Function GetExcel() As Microsoft.Office.Interop.Excel.Application
        ExcelAppWasStarted = False
        'Dim application As Microsoft.Office.Interop.Excel.Application
        If Process.GetProcessesByName("EXCEL").Count() > 0 Then
            ExcelApp = DirectCast(Marshal.GetActiveObject("Excel.Application"), Microsoft.Office.Interop.Excel.Application)
            ExcelApp.Visible = True
        Else
            ExcelApp = New Microsoft.Office.Interop.Excel.Application()
            ExcelApp.Visible = True
            ExcelAppWasStarted = True
        End If
        Return ExcelApp
    End Function

    Public Function OpenWorkbook(fi As SIO.FileInfo) As Microsoft.Office.Interop.Excel.Workbook
        Dim appExcel As Microsoft.Office.Interop.Excel.Application = GetExcel()
        Dim wb As Microsoft.Office.Interop.Excel.Workbook = appExcel.Workbooks.Open(fi.FullName)
        Return wb
    End Function

    Public Sub CloseExcelApplication()
        Try
            If ExcelApp IsNot Nothing Then
                ExcelApp.Quit()
                ExcelAppWasStarted = False
            End If
        Catch ex As Exception
            MsgBox("Can't close Excel" & vbCr & ex.ToString, MsgBoxStyle.SystemModal)
            Try
                ExcelApp.Visible = True
            Catch ex2 As Exception
                MsgBox("Can't set Excel visible either" & vbCr & ex2.ToString, MsgBoxStyle.SystemModal)
            End Try
        End Try
    End Sub