Access Embedded Spreadsheet behind the scenes

[meck]

Hi All,

I am using vb.net to read an embedded spreadsheet. Everything works good, except it opens the spreadsheet everytime it reads from it. It's rather annoying seeing it open all the time. I was wondering if there was a way to have it read it without it opening every time? Do it in the background. Here's the code I am using...

Public Function GetEmbeddedExcelData(SheetName As String) As Object(,)
'This function returns an arraylist of excel data read from the active Inventor document's embedded excel sheet
'Object is actually a 2 dimensional array of spreadsheet cell values = (Row,Column)

Dim oApp As Inventor.Application = System.Runtime.InteropServices.Marshal.GetActiveObject("Inventor.Application")
Dim oOleRef As Inventor.ReferencedOLEFileDescriptor = Nothing
Dim InvName As String = oApp.ActiveDocument.DisplayName

Dim arrayRanges(,) As Object

Try
oOleRef = oApp.ActiveDocument.ReferencedOLEFileDescriptors.Item(1)
Catch ex As Exception
MsgBox("No Embedded spreadsheet in the active document!", MsgBoxStyle.Exclamation, "Connection Error!")
ReDim arrayRanges(0, 0)
arrayRanges(0, 0) = "Error!"
Return arrayRanges
End Try

Dim oWB As Microsoft.Office.Interop.Excel.Workbook = Nothing
oOleRef.Activate(Inventor.OLEVerbEnum.kEditOpenOLEVerb, oWB)

Dim oSheet As New Microsoft.Office.Interop.Excel.Worksheet

For Each oSheet In oWB.Sheets
If oSheet.Name = SheetName Then
oSheet.Activate()
Exit For
End If
Next

If oSheet Is Nothing Then
MsgBox("Could not find the sheet named " & SheetName & " in the Inventor document " & InvName, MsgBoxStyle.Exclamation, "Unable to retrieve assembly parts")
ReDim arrayRanges(0, 0)
arrayRanges(0, 0) = "Error!"
End If
'Debug.Print(oSheet.Name)
Dim startingCol As Integer = oSheet.UsedRange.Column
Dim numberCol As Integer = oSheet.UsedRange.Columns.Count
Dim startingRow As Integer = oSheet.UsedRange.Row
Dim numberRow As Integer = oSheet.UsedRange.Rows.Count

'Get range
Dim xlRange As Microsoft.Office.Interop.Excel.Range = oSheet.UsedRange

'Load all cells into Array
arrayRanges = xlRange.Value 'This line loads the range of the excel file into an array

oWB.Close()
Return arrayRanges

End Function

[JelteDeJong · Accepted solution]

I did not manage to test your code but i think you should have a look at this line:

oOleRef.Activate(Inventor.OLEVerbEnum.kEditOpenOLEVerb, oWB)

The enum has some other values maybe it can help you.

But is it necessary for you to use an excel file? If you would use a csv file or some other ASCII file format then reading the file would be much easier and there would be no need to open excel.

[meck]

That did the trick. Thanks!

I started this project using iLogic, so the spreadsheet was already embedded in the model. I ran into some limitations with iLogic so I went to vb.net. Also it allows for the designers to only deal with 1 file the model which they are comfortable with. Breaking the spreadsheet out into separate files is a better option for me, but its more difficult on the users.

Thanks again for your help!