Problem with Pastspec cells from excel

[Anonymous]

Hi at all!

I make a premise: 

In my scenario Autocad must be invisible to user and not must have interactions whit him!

I'm programming in c# (i'm using ActiveX via COM/Interlop on c#)

I'have a issue when i try to call

Worksheet worksheet = excelManager.GetCurrentWorkSheet();

worksheet.get_Range("A1", "D6").Copy();      //this perform a copy like Ctrl+C on selected cell in a excel window

ThisDrawing.sendcommand("_pastspec "); //after copying the excel cells of interest

the autocad application (that run in background) show the window dialog to select the pastspec's setting preferences.

I'm not able to handle that window dialog.

the execution hangs on sendcommand line, waiting response to autocad windowdialog.

Do you know one way to handle this windowdialog?  (I would choose "Autocad Entities" option in that window)

Thanks in advice for your time. 

[norman.yuan]

Firstly, from my experience, when automating AutoCAD via COM from external app, there is always possibility that AutoCAD instance would become visible, depending on what the external app let AutoCAD instance do. Your case is perfect example that AutoCAD, as a very sophisticated desktop app, sometimes runs into a situation that user interference in need, especially when you want to use a predefined functionality (command "pastespec") meant for user interaction. There are certainly other situation that could bring an origianlly invisible AutoCAD instance to visible.

As for your specific work (as I guess: bring data in Excel sheet into AutoCAD as AutoCAD table), you can do "REAL" programming, rather than using existing command meant for manual operation:

1. Read data from Excel sheet. It would be better to read data from *.xlsx file without needing to open it in Excel app (or not even need Excel being installed). Yes, it is quite easy to do these days with .NET

2. Use code in AutoCAD (COM or .NET API) to create AutoCAD table and then fill the data.

Doing it this way, if you still insist AutoCAd being invisible, you could even consider to use AutoCAD console.

[Anonymous]

Thanks for your answer!

not is a problem if autocad become visible for few second, to perform particulary operations.

what do you mean with "REAL" programming?

1) I don't know this method to reading excel file. I can also write whitout open file?  I will study this method.

2) Can you show me some code to create and fill Autocad table?

I would something like this     

NOT like this: 

[norman.yuan]

By "REAL" programming, I mean to write your code to do exactly you want, rather than incorporate predefined command, which is meant for user interaction, into your code, such as "PASTESPEC".

Now, if you agree on this, then your code would do 2 separate things: get data and present data. Do structure your code well to separate the 2 things.

Getting data: whether you need to open the *.xlsx file in Excel app or not is not very important, as long as you get the data correctly. You may store the data in an array variable, or in a collection of a class, depending on the complexity of the data. Obviously, open the sheet in Excel (automating Excel via COM) would be much slower than getting data directly from *.xlsx file without opening in Excel (looking into OpenXML, you can even find quite some free third party utilities based on OpenXML, for reading data from spreadsheet). again, opening the sheet in Excel may not be very critically important, depending on how much data you need to retrieve.

Presenting data: creating an AutoCAD table in drawing programmatically is not very difficult. Here is an VBA sample (COM) from AutoCAD VBA document:

Sub Example_CellManipulation()
    ' This example adds a table in model space and sets and gets a column name

    Dim MyModelSpace As AcadModelSpace
    Set MyModelSpace = ThisDrawing.modelSpace
    Dim pt(2) As Double
    Dim MyTable As AcadTable
    Dim cName As String
    Set MyTable = MyModelSpace.AddTable(pt, 5, 5, 10, 30)
    
    Call MyTable.SetCellDataType(2, 2, acLong, acUnitDistance)
    Call MyTable.SetCellFormat(1, 3, "testFormat")
    Call MyTable.SetCellState(4, 3, acCellStateContentLocked)
    Call MyTable.SetCellValue(1, 4, 5)
    
    MsgBox MyTable.GetCellValue(1, 4) & " is the test cell's value "

    ZoomExtents
    
End Sub

which is overly simplified, but you get the idea, and a bit study on AutoCAD COM API could lead you to write code to create very complicated AutoCAD table.

[Anonymous]

Thanks again for your help.

I've already found a code similar to yours, but i've a question.

Maybe i've leve out one detail.

In Excel file there are many cells that occupy more than one row and this table structure must be reported in the autocad table:

this is the reason for whitch i asked to special paste.

Do you think that some code similar to your is able to perform this?

[norman.yuan]

Well, in that case, you need to do a lot more work, probably just as what AutoCAD does behind the scene when user choose "AutoCAD Entities" in the "Paste Special" dialog box: you need to read the sheet cell formatting information, and translated to corresponding AutoCAD table formats with code. It is quite some work, I can imagine. Look, you have to different application that present data in their own ways. Now if you want the two presentations look the same or very similar, it is as difficult as it gets, but it is doable and all could be in control of your code.

You may try Windows' SendKey() to stimulate key stroke on UI to complete the dialog box. However, in your case, the "Paste Special" dialog box is pops up by SendCommand(), I doubt SendKey() could helps you.

[Anonymous]

You are right, there is much work to do!

In my second way, I'm drawing a tamplate table in autocad, in which each cell is a dynamic block, stretchable, and the attribute inside move following cell strach.

I hope that i will able to handle this "made in hand" pastspec, in this way... 🙂

Thanks!

[Anonymous]

i've done it with succeed!