CAD and Excel Relationship

[Anonymous]

I am looking for a way to extract my block attributes, that once I click the attribute value in Excel, I want that block to be highlighted in autocad to identify where it is located. Basically, I want a relationship that in one program one instance is selected and it shows in other or at least I select in excel and it highlights in autocad. The purpose behind this is we have some non CAD Users in asset management and they only use Excel and this possibility will help them big time.

[zph]

While I am unsure if the 'highlighting' can occur automatically, if the AutoCAD block 'entity handle' of each respective block were exported into the excel table, your people could copy the value of the entity handle from the excel cell using CTRL+C.

Obtain an AutoLISP routine that searches the .dwg for an entity by its entity handle name via user input.  After executing this routine, CTRL+V, and the routine will select the entity - effectively highlighting within the drawing.

The routine to find an AutoCAD object by its entity handle is available around the 'net.

My thoughts. good luck.

[Anonymous]

I have tried that lisp, but it doesn't zoom enough to recognize which object is this, as my drawing is set on real coordinates, and since its too far from 0,0 zoom to object doesn't work.

[zph]

You could try this...

Get the boundary box for the object.

Use the two generated points and use 'zoom to window' of the two points.

---

Whenever I hit the limits of the drawing zoom feature, I REGEN at the drawing limits seems to expand.  Perhaps you need to include a REGEN in the code of the handle selection routine.

[Anonymous]

Could you please elaborate a little more, I didn't understand.

[zph]

What part didn't you understand?

[wkmvrij]

I have some questions as well about your initial posting. Can you describe the relation between XL and ACAD a bit closer?

You mentioned users that only use Excel. Would they want to then open ACAD to look at the part?

Or if that is not the case would you want to use Excel to navigate to a part that is included in a DWG?

Pls make a list of actions->results 

With ActivX available in both XL as in ACAD one could dream up very elaborate scenario's 

[zph]

There is no direct relation between Excel and AutoCAD [that I know of].

It is possible to create links between tables located in AutoCAD to excel files, but I'm fairly certain that this functionality isn't what you are looking for.

Yes, with AutoLISP and VL, there may be possibilities, but I know of none that fully automate what you want to do.

The solution, in my initial response, requires user input.

I've worked asset management; we had a system that worked for us.  I'm betting your current system is set up differently, but is attempting to accomplish a similar goal.  A drawing file showing where you are currently might go a long way to figuring out what might work for you.

[Anonymous]

Actually the ACAD will be open, but they don't know how to use it, so we want a relationship that they click in excel and indicates in ACAD to show them where this Asset is installed.

[Anonymous]

Get the boundary box for the object............. Do you mean that I have to make a rectangle or boundary around my project?

And then zoom window to that boundary.

I did that but still can't zoom an object by lisp.

Thanks

[zph]

Posting your code would help.

[wkmvrij]

I believe that the problem as you describe it should be attacked in a more or less surprising way. You have two programs but as well to types of users, each with different skills. 

I think activities should be described as follows. Excel users would choose a cell in a spreadsheet that contains a definition of a product. This cell is in conjunction with additional data (same row different column, or lookupfield) which enable grabbing  a block insert with attributes in a DWG file. The values of these attributes should than be used by either the excel user or somebody else again.

I attached both a sample excel file and a DWG., together with a LSP. 

Now this LSP when loaded in the DWG will open the Excelsheet with the data through ActivX methods and retrieve the value of a cell that contains the HANDLE of the object to be consulted. Then a call to (vl-cmdf... issues a ZOOM  to an object that is retrieved through the (handent...  call. 

This the only line of interest in this LSP. One might obtain attdefs from this same object and do something with them as required.It is imperative to use HANDLE (assoc 5 ...) since these will remain constant over the life of the drawing file

Where the problem starts to be beyond this forum is that this entire sequence should be initiated with a macro in the Excel sheet. with the cell with the part definition activated  It would activate an instance of Autocad , open or display the  DWG file and run the LSP that will zoom to the required block insert. All this is possible along the same lines as is done in the LSP wit Excel ActiveX methods. Excel macro's are written in VB, which is not my speciality. You might find someone to help you further with this.

.

(defun HighlightHandle (/ xlfn)
  (DEFUN con-excel (/ *XL*)
    (OR	(SETQ *XL* (VLAX-GET-OBJECT "Excel.Application"))
	(SETQ *XL* (VLAX-CREATE-OBJECT "Excel.Application"))
	(SETQ *XL* (ALERT "Coul not connect to Excel. Abort"))
	(EXIT)
    )
    *XL*
  )
 
  (DEFUN getcel	(Sheet row column /)
    (VLAX-GET (VLAX-GET-PROPERTY Sheet 'Range (STRCAT column (ITOA row)))
	      'Value
    )
  )
  (AND (setq xlfn "C:\\Data\\Acad\\LSP\\WEB\\zoomXL.xlsx") ;;;Adapt to your requirement
    (OR	(SETQ XL (con-excel))
	(ALERT "Could not load Excel. Sorry")
	(EXIT)
    ) ;_ end of or
    (NOT (VLAX-PUT XL 'Visible :VLAX-TRUE))
    (OR	(AND (setq
	       xlBook
		(VLAX-INVOKE (VLAX-GET XL 'Workbooks) 'Open xlfn)
	     ) ;_ end of vlax-invoke

	     (SETQ xlSheet (VLAX-GET xlBook 'ActiveSheet))
	)

	(IF XL
	  (SETQ XL (VLAX-INVOKE xl 'QUIT))
	)
	(ALERT "Could not find worksheet. Sorry")
	(EXIT)

    )
  )
  (vl-cmdf "ZOOM"
	   "OBJECT"
	   (handent (getcel xlSheet 3 "A"))
	   ""
  )
)

[wkmvrij]

Did some digging 🙂

Made a VBA macro in the example Excel file that made the setup as described previously work.  I am sure somebody will able to elaborate the VBA module to add whatever suits the required functionality better. I can think of adding a column with DWG filenames to open what is needed in Autocad and a 'way out' for whatever attributes isw reaped from the INSERT entities as requested  

OOPS extention .xlsm is refused for upload

Function ACConnect()
Dim acObj As Object
Dim acDocObj As Object
Dim Comtext As String


Set acObj = CreateObject("AutoCad.Application")
acObj.Visible = True
Set acDocObj = acObj.Documents.Open("C:\Data\Acad\LSP\WEB\XLZOOM.dwg", ReadOnly:=True)
Comtext = "(vl-cmdf ""ZOOM"" ""OBJECT"" (handent """ & Application.ActiveCell.Value & """) """") "
acDocObj.sendcommand (Comtext)

End Function

Sub ACZoom()
Run ACConnect()
End Sub

above code worked for me in Excel 2013

[Anonymous]

Many Thanks for your Valuable Input,

First, I am using AutoCAD 2012 and I am unable to open cad file, it gives Fatal Error and close the autocad. Second i have not used macros so unable to run the macro, could you please make a small tutorial and share with us, (video or Snap Shots) I might be asking too much, but it will be a great help for me always for many others.  

Many Thanks In advance.

[Anonymous]

I managed to Run VBA, what it does is, open up the CAD file, but don't zoom to that particular object. Btw its File Specific and need to specify file location for every file.  I found A VBA which is very AWesome and wrks great, but it works for only data extracted from a specific add-on (paid). 

The website to download 14 day trail version is , http://www.cadstudio.cz/excellink#2012

and the VBA below works perfectly,

Private Declare Function SetForegroundWindow Lib "user32" (ByVal hwnd As Long) As Long
Private Declare Function GetForegroundWindow Lib "user32" () As Long

Sub ZoomTo()
Dim acadApp As Object
Dim acadDoc As Object
Dim hgt As Double

Set acadApp = GetObject(, "AutoCAD.Application")
Set acadDoc = acadApp.ActiveDocument

hgt = acadDoc.GetVariable("TEXTSIZE")

Dim Center(0 To 2) As Double
Dim magnification As Double
Center(0) = ActiveCell.Value
Center(1) = ActiveCell.Offset(0, 1).Value
Center(2) = 0

magnification = hgt * 40

acadApp.ZoomCenter Center, magnification
SetForegroundWindow (acadApp.hwnd)

End Sub

Sub XLimport()
Dim acadApp As Object
Dim hand As Long

'AllowSetForegroundWindow
hand = GetForegroundWindow()

Set acadApp = GetObject(, "AutoCAD.Application")
SetForegroundWindow (acadApp.hwnd)
SendKeys "XLINK~Import~", True
SetForegroundWindow (hand)
End Sub

Sub XLsummary()
Cells.Select
Selection.Sort Key1:=Range("A2"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
Selection.Subtotal GroupBy:=1, Function:=xlCount, TotalList:=Array(1, 4), _
Replace:=True, PageBreaks:=False, SummaryBelowData:=True
End Sub

[wkmvrij]

It is certainly looking as if we are getting somewhere 

It is time now to define more exactly what you expect from your setup. Questions to be answered are:

A) Who/what are your intended users , their tasks to be executed, and the tools at their disposal, yourself included.

B) The data and their types that are to be common in both Excel and Autocad permitting the afore mentioned tasks. So far I have the HANDLE of an Autocad Object as published by Autocad to be present in an EXCEL range of cells. There might be others. An example would be to have in Excel an image depicting the assembly clickable on a certain detail to establish a "ZOOM".in Autocad and subsequently retrieving ATTRIB details to be inserted in a Word document for ordering purposes.

Would you need LSP or VBA routines to establish this relationships automatically. I have dreamed up a short LSP to fill my example excel file with data from my example DWG.

C) Do you wish to exploit the system bidirectional or oneway, and if so in which direction? Would you require a UI (user interface) for either side?

Note, that you would need a VBA routine in Excel   AND   a LSP or VBA routine in Autocad  to get the functionality as required,

Or a C++ or C# executable to accomplish the tasks in between both programs.

I stress the point, that I am not proficient enough in VB(A) to do much more than my posts, so I would have to further let you alone on this one. I have played around a bit with C++ and could make something but I do not have professional level on that to be of great use

Keep progress posted ...