PASTE SPECIAL from excel

[sgodonVEAP9]

I'm in an inventor drawing. I need macro to copy cells(1,1) to cells(77,6) from an excel workbook and paste special (tools->paste special->paste link) into my drawing.  I can easily do it manually but can't seem to find anything online that uses a macro:( any ideas? thanks!

[A.Acheson]

Any chance of showing pictures of the end result when done manually? I'm having difficulty seeing what objects your using in the drawing. 

[sgodonVEAP9]

hope it helps thanks!

[sgodonVEAP9]

[A.Acheson]

I am not sure if you can match exactly the workflow your using. The code below will just bring in the data as a custom table. There might not be any control over border style etc. But at least there is a link to update the contents via excel. 

    Dim oDrawDoc As DrawingDocument = ThisApplication.ActiveDocument
    
    Dim oActiveSheet As Sheet = oDrawDoc.ActiveSheet
    
    ' Create the placement point for the table
    Dim oPoint As Point2d = ThisApplication.TransientGeometry.CreatePoint2d(25, 25)
    
    Dim oExcelTable As CustomTable = oActiveSheet.CustomTables.AddExcelTable("C:\Temp\ImportTable.xlsx", oPoint, "Excel Table")
    

[sgodonVEAP9]

I've already tried this method, but two problems: the reason I use special paste, is that it keeps color of cells, width of th columns , it doesn't print the border of the cells etc. The other reason is that I can't link the data of the second sheet of my excel, only the first. It can be done manually so it has to be doable by programming. Appreciate the help thanks!

[Curtis_Waguespack]

I am not seeing a method to add the table as an OLE object to the drawing sheet. It's possible the developers have not given us access to this method.

The closest I could find in the API help files was a way to create the OLE embedding as a 3rd party link in the browser.

sExcel = "C:\Temp\Test.xlsx"
Dim oDoc As DrawingDocument
oDoc = ThisApplication.ActiveDocument

Dim oOLE As ReferencedOLEFileDescriptors = oDoc.ReferencedOLEFileDescriptors
oOLE.Add(sExcel, kOLEDocumentEmbeddingObject)

[sgodonVEAP9]

 Hi Curtis, I've tried this method as well but here's the problem:

Using a third party link with an excel file only allows you to link the content of the first sheet. Since I have multiple sheets, I have to save each one of them as a new excel (via a macro but still very annoying) since it adds multiple excel files in my folder. The other problem is that you have no control over what range of cells inventor decides to link (seems to be kind of random).. 

The reason I use paste special is that you dont have the previous problems. In the next pictures, you'll see the difference between the two OLE links. One of them was added with manage-> insert object -> create from file. The other one was created with tools-> paste special ->paste link. Both of them result in an ole link except that paste special as a source that points to a specific sheet and range.

insert object:

paste special:

There's a lot of stuff online about manage ->insert object, just like the code you provided, but nothing about paste special even tough they seem to be very similar.. Anyway thanks for help!

[miechh]

At our company we also use Excel tables to insert into drawings, also because we demand to have those tables with specific formatting (See picture) which could not be achieved by the 'Generic Table' functionality of Inventor. We would also like to be able to select different ranges in that Excel sheet/workbook and copy/paste them in a drawing. Now we do this manually, but if someone could point us to a workable solution in iLogic, this would be greatly appreciated.

[A.Acheson]

Here is an attempt to semi automate this workflow. The code is just opening the excel file and copying to the clipboard the range then firing the inventor command paste special. The same as you would do manually.

Option Explicit On
AddReference "microsoft.office.interop.excel.dll"
Imports XL = Microsoft.Office.Interop.Excel


'[Open the Excel Workbook
Dim xlApp As XL.Application = CreateObject("Excel.Application")
Dim xlWb As XL.Workbook
Dim xlWs As XL.Worksheet

'in order to not show Excel
xlApp.Visible = True 
Dim xlPath As String = "C:\Users\table.xlsx"

If IO.File.Exists(xlPath) = True Then
	xlWb = xlApp.Workbooks.Open(xlPath)
End If

xlWs = xlWb.Worksheets.Item("TABLE")

xlWs.Range("A1:D2").Copy
']


' Get the control definition for the command.
Dim ctrlDef As Inventor.ControlDefinition = ThisApplication.CommandManager.ControlDefinitions.Item("AppPasteSpecialCmd")
ctrlDef.Execute

xlWb.Close(False)
xlApp.Quit

[miechh]

Thanks for the code. Got things running now. Only disadvantage of this procedure is that as soon as the ctrlDef.Execute command passes, the Paste Special dialog window appears, and the iLogic code also immediately continues simultaneously. I was hoping the code would pause until the paste-action was completed. That way I could make a looping code to paste multiple Excel ranges after one another.