I'm asking this after a lot of unsuccessful reserching, hoping I can get a solution.
I'm trying to read excel-table info from a number of drafts. That info is placed into these dafts embedded or linked. In any case, I know how to reach it from Symbols2d or SmartFrames2d collections.
As far as I can see using Solidedge spy, the Excel stuff is placed inside the Object member of each of the items in those collections. So, more or less my code looks like this:
Dim objSmartFrames As SolidEdgeFrameworkSupport.SmartFrames2d objSmartFrames = CType(objSheet.SmartFrames2d, SolidEdgeFrameworkSupport.SmartFrames2d) For i As Integer = 1 To objSmartFrames.Count Dim objSmartFrame As SolidEdgeFrameworkSupport.SmartFrame2d = Nothing Dim objExcelWorkbook As Excel._Workbook = Nothing Try objSmartFrame = CType(objSmartFrames.Item(i), SolidEdgeFrameworkSupport.SmartFrame2d) objExcelWorkbook = CType(objSmartFrame.Object, Excel._Workbook) 'This always fails! Catch
'Conversion to Excel.Workbook always fails End Try Next
As I write in the code, I'm not able to get the Excel object in order to read information from it. According to Solidedge, the Object member is Excel._Workbook, but the casting always fails, due to COM stuff.
I have used this same approach on the Symbols2d collection with exactly the same results.
I just want to read information from the Excel tables and generate native Solidedge tables, but no clue about how to reach it.
Many thanks in advance, I think this information can be quite useful, since embedding Excel stuff in drafts is a common practice.
I don't know what type of object is returned by Solid Edge in the Object property, but you can use "late binding" for the "workbook" and then use "early binding" for the Worksheet and all other Excel objects.
This worked for me (see code below):
Imports Microsoft.Office.Interop Dim fileName As String = "" Dim sheetName As String = "" Dim range As String = "" Dim objDoc As DraftDocument = objApp.ActiveDocument ' Read the Excel information from the Symbols Dim objSymbols As Symbols = objDoc.ActiveSheet.Symbols For Each objSymbol As Symbol2d In objSymbols If objSymbol.OLEType = OLEInsertionTypeConstant.igOLEEmbedded AndAlso objSymbol.Class.StartsWith("Excel.Sheet") Then Dim xlWBook = objSymbol.Object fileName = xlWBook.FullName Dim xlSheet As Excel.Worksheet = CType(xlWBook.ActiveSheet, Excel.Worksheet) sheetName = xlSheet.Name range = xlSheet.UsedRange.Address Debug.WriteLine("FileName: " & fileName) Debug.WriteLine("SheetName: " & sheetName) Debug.WriteLine("Range: " & range) Marshal.ReleaseComObject(xlSheet) xlSheet = Nothing End If Next
' Read Excel information from the SmartFrames Dim objSmartFrames As SmartFrames2d = objDoc.ActiveSheet.SmartFrames2d For Each objSFrame As SmartFrame2d In objSmartFrames If objSFrame.IsLink AndAlso objSFrame.ObjectType = "Worksheet" Then Dim xlWBook = objSFrame.Object fileName = xlWBook.FullName Dim xlSheet As Excel.Worksheet = CType(xlWBook.ActiveSheet, Excel.Worksheet) sheetName = xlSheet.Name range = xlSheet.UsedRange.Address Debug.WriteLine("FileName: " & fileName) Debug.WriteLine("SheetName: " & sheetName) Debug.WriteLine("Range: " & range) Marshal.ReleaseComObject(xlSheet) xlSheet = Nothing End If Next
Many thanks, that really worked.
I had to make a little change on the Late Binding , since I use 'Strict On'.
With 'CallByName' I'm able to get a certain Property even when not knowing the type of an object. Not sure if there's a wiser way for this:
For i As Integer = 1 To objSmartFrames.Count Dim objSmartFrame As SolidEdgeFrameworkSupport.SmartFrame2d = Nothing Dim objExcelWorksheet As Excel.Worksheet = Nothing Try objSmartFrame = CType(objSmartFrames.Item(i), SolidEdgeFrameworkSupport.SmartFrame2d) objExcelWorksheet = CType(CallByName(objSmartFrame.Object, "ActiveSheet", CallType.Get, Nothing), Excel.Worksheet) 'More stuff here... Catch End Try Next
However, why can't I cast the Symbol2d.Object or SmartFrame2d.Object in its given type??? It would be more elegant and with some exception catching it's easy to discard other possible object types.
SolidEdgeSpy shows Excel._Workbook for that particular instance, but CType fails to convert. Also, what's the difference between Excel._Workbook and Excel.Workbook? Does the underscore mean something dark...?
Again, many thanks, that was wise!
I have another question related to this.
The .UsedRange Property returns, as I can guess, the cells which have some sort of content. However, they don't always match the cells which are visible in the embedded view of the sheet within SE.
Is there a way of getting the cell range actually visible in the SE view, which is editable with right-click mouse button? I want to study only the cells visible, since they are the only ones with sense within the draft context, but no idea how to get them.
Again, thanks in advance!
If you had copied a range of cells from an Excel sheet and pasted it into Solid Edge as a Symbol, then you may use the RangeSelection property of the Excel Window to get the visible range. I am not sure, if that will work for all cases.
For SmartFrames, I haven't found any way to get the visible area without trying to calculate it by checking the columns' width and rows' height and comparing it against the Crop... properties. The difficulty here is to find a common unit to do the calculations, including possible scale factors used in Excel and/or Solid Edge.
Dim range As String = "" Dim objDoc As DraftDocument = objApp.ActiveDocument ' Read the Excel information from the Symbols Dim objSymbols As Symbols = objDoc.ActiveSheet.Symbols For Each objSymbol As Symbol2d In objSymbols If objSymbol.OLEType = OLEInsertionTypeConstant.igOLEEmbedded AndAlso objSymbol.Class.StartsWith("Excel.Sheet") Then Dim xlWindows As Excel.Windows = CType(CallByName(objSymbol.Object, "Windows", CallType.Get, Nothing), Excel.Windows) range = xlWindows.Item(1).RangeSelection.Address Debug.WriteLine("VisibleRange: " & range) Marshal.ReleaseComObject(xlWindows) : xlWindows = Nothing End If Next
Again, thanks for your fast answer.
I have analyzed the RangeSelection Property, and, as you said, it works but only in certain scenarios.
From what I have guessed, when you simply copy and paste a selection of cells, with no further editing of that object inside Solidedge, all those cells seem to remain selected inside the "symbol", so RangeSelection works as expected.
However, a number of times that selection is lost. Maybe when you edit some of the cells content after pasting, or when you do some adjusting on the frame in order to change the range visualized (which is what I want to get). Maybe there's no way of reaching this info and we must try dirty methods :-P
If someone can add some light here it will be very appreciated.
Many thanks for your interest and time.