iLogic fun: Inserting a jpg into an Excel file

[Anonymous]

So this is a project that I have been meaning to finish and I'm stuck ten feet to the finish line.  I have finally found the key to opening an assembly from a drawing and saving an isometric view.  Code as such:

Public Sub Main SaveAsJPG()
' Get the active document
Dim oDoc As Document = ThisApplication.ActiveDocument
Dim CloseFlag As Boolean = False
 Dim rDoc As Document

' Check if the document is drawing
If oDoc.DocumentType = DocumentTypeEnum.kDrawingDocumentObject Then
 Try
' Find the first view and it's model's name
 Dim oDrawView As DrawingView = oDoc.ActiveSheet.DrawingViews.Item(1)
 Dim ModelFileName As String = oDrawView.ReferencedFile.FullFileName

 ' Open the document by model name
rDoc = ThisApplication.Documents.Open(ModelFileName, True)
CloseFlag = True
 Catch
 MsgBox("Couldn't open the referenced file.", "iLogic")
 Exit Sub
 End Try
End If
Dim path As String = ThisDoc.PathAndFileName
Dim jpeg As String = path & ".jpg"
' Get the Isometric view.
ThisApplication.CommandManager.ControlDefinitions.Item("AppIsometricViewCmd").Execute
oView = ThisApplication.ActiveView
' Save the view as a jpg file.
oView.SaveAsBitmap(jpeg, 1200, 0)
' Close the referenced document
If CloseFlag Then rDoc.Close(True)
End Sub

Now that this jpeg exists, I need a way to stick it in my program that exports a BOM with a template.

Here is that entire program because, why not?

SyntaxEditor Code Snippet

Sub Main()    
    Dim pathName As String = ThisDoc.PathAndFileName(False)
    Dim fileName As String = ThisDoc.FileName
    Dim model As String = fileName & ".iam"
    Dim spreadsheet As String = pathName & "-BOM.xls"
    Dim ExcelApp As Object
    Dim WorkObject As Object
    Dim revision As Integer = 0
    'Creates a revision number if the BOM already exists
    Do While Dir(spreadsheet) <> ""
        revision += 1
        spreadsheet = pathName & "-BOM-" & revision.ToString & ".xls"
    Loop

'Exports Parts List to Active Folder using the template
    Dim oDoc As Inventor.DrawingDocument = ThisDoc.Document
    Dim    oSheet As Inventor.Sheet = oDoc.Sheets(1)
    Dim oPartsList As PartsList = oSheet.PartsLists(1)
    Dim oOptions As Object = ThisApplication.TransientObjects.CreateNameValueMap
    
    oOptions.Value("Template") = "I:\PRODUCT_CATALOG\iLogic\ESC-XXXX-BOM.xls"
    oOptions.Value("ExportedColumns") = "QTY;PART NUMBER;DESCRIPTION"
    oOptions.Value("StartingCell") = "A19"
    oOptions.Value("IncludeTitle") = False
    oPartsList.Export(spreadsheet, PartsListFileFormatEnum.kMicrosoftExcel, oOptions)

'Closes new Excel file
    GoExcel.Save
    GoExcel.Close
    GoExcel.Close
    GoExcel.Close

'Opens created Excel file to the proper Sheet
    GoExcel.Open(spreadsheet, "BOM")
    Dim i As String = "init"
    Dim cellRow As Integer = 20

'Transposes Description Column to the proper section
    Do Until i = ""
        i = GoExcel.CellValue("C" & cellRow)
        GoExcel.CellValue("K" & cellRow) = i
        GoExcel.CellValue("C" & cellRow) = ""
        cellRow += 1
    Loop
'Fixes Headings
    GoExcel.CellValue("A19") = "QUANTITY"
    GoExcel.CellValue("B19") = "PART #"
    GoExcel.CellValue("C19") = "MACH."
    
'Adds extra properties to the top of the created Excel File        
    Dim partNumber As String = ""
    Dim description As String = ""
    Dim writtenBy As String = ""
    Dim customer As String = ""
    Dim salesOrder As String = ""
    Dim customerPO As String = ""
    Dim tagName As String = ""
    Dim DWGNumber As String = ""
    
    customer = InputBox("Customer:","BOM Helper FW", "")
    salesOrder = InputBox("Sales Order #:","BOM Helper FW", "")
    customerPO = InputBox("Customer P.O.#:","BOM Helper FW", "")
    tagName = InputBox("Tag Name:","BOM Helper FW", "")
    DWGNumber = InputBox("DWG Number:","BOM Helper FW", "")

    partNumber = iProperties.Value("Project", "Part Number")
    Try
    description = iProperties.Value(model, "Summary", "Title")
    Catch
    model = fileName & ".IAM"
    description = iProperties.Value(model, "Summary", "Title")
    End Try
    writtenBy = iProperties.Value("Summary", "Author")
    
    GoExcel.CellValue("A2") = partNumber
    GoExcel.CellValue("B2") = description
    GoExcel.CellValue("B12") = writtenBy
    GoExcel.CellValue("B4") = customer
    GoExcel.CellValue("B6") = salesOrder
    GoExcel.CellValue("B8") = customerPO
    GoExcel.CellValue("B10") = tagName
    GoExcel.CellValue("B14") = DWGNumber

'Saves an image of the Model Document to the folder'        ThisDrawing.ModelDocument.SaveAs(image,True)    

    GoExcel.Save
    GoExcel.Close
    GoExcel.Close
    GoExcel.Close
    
'Opens Excel to edit BOM
    ExcelApp = CreateObject("excel.application")
    ExcelApp.Visible = True
    WorkObject = excelapp.workbooks.Open(FileName:=spreadsheet)
    
    ExcelApp = Nothing
    WorkObject = Nothing

'Programming by Brian Daley
End Sub

Bonus points if you can tell me how to save it in compatibility mode (97-2003).

So I don't know the syntax on inserting an image into an excel spreadsheet via iLogic and Google has failed me.  All I know is that it will always be in Cell K1.  From what I've read, you can't just stick a picture into a cell all willy nilly, you need to know the exact coordinates of that cell.  Whether it's true or not, I don't know.  I figure I could just add the widths of A-J and have my location.  Again, I haven't even got to that spot because I don't know where to put what code to add the picture.

Any help is greatly appreciated!  I hope my program helps someone too!

[MechMachineMan]

I found a really neat snippet from program someone else wrote on here that inserts the picture in the comments to the cell and will resize the cell to fit the picture and expose the comment

See here for program I am referencing:
http://forums.autodesk.com/t5/inventor-customization/batch-renaming-program/td-p/6298702

[Anonymous]

Kind of rough and outdated code.... Was this the snippet you were referring to?

    With Range("E" & Level)
            .Value = Range("C" & Level).Value
            Range("E" & Level).Font.Color = -16777024
            .AddComment
            .Comment.Visible = False
            .Comment.Shape.Fill.UserPicture ("C:\Temp\Thumb.jpg")
            .Comment.Shape.Height = 50
            .Comment.Shape.Width = 50
        End With

The rest of that Module goes over saving a thumbnail, which I believe I could replace with my code.  So basically, it creates a custom "Shape" of the picture and then places it in required cell as a comment?

[MechMachineMan]

Precisely!

[Anonymous]