Access to Embedded Excel Worksheets in Drafts

Builder
Builder

Hello there.

 

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.

5 REPLIES

Re: Access to Embedded Excel Worksheets in Drafts

Solution Partner Phenom Solution Partner Phenom
Solution Partner Phenom

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

Re: Access to Embedded Excel Worksheets in Drafts

Builder
Builder

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!

Re: Access to Embedded Excel Worksheets in Drafts

Builder
Builder

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!

Re: Access to Embedded Excel Worksheets in Drafts

Solution Partner Phenom Solution Partner Phenom
Solution Partner Phenom

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

Re: Access to Embedded Excel Worksheets in Drafts

Builder
Builder

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.