Import Excel to DWGPROPS

[cmangi86]

I work as an engineer for a solar company. We preform all of our calculations on an excel file, then transfer these values to Autocad to show our calculations for the plan checkers. I use the DWGPROPS command in Autocad to auto populate all the pages in my plans. I tend to do a lot of copy and pasting and to do all this.

 

Is there a way to isolate the excel values I need and import them into my Autocad DWGPROPS?

[ReMark]

You can bring in an entire worksheet or a range of cells via the Table > Datalink command.

[cmangi86]

Thanks for the reply, but it isn't quite what I am looking for. I need the table to insert directly into the DWGPROPS command, not just in a separate table. Do you know if that is possible?

[ReMark]

I have no idea. Inserting the table in the drawing makes more sense to me. But it is your drawing so place your data anywhere you want.

[BlackBox]

The short answer is... No.

 

A more complete answer... Is that despite the inability to paste an Excel cell range directly into specific components of DWGPROPS, one can write a relatively simple LISP routine, which can programmatically query the applicable cell values, and populate the Document Object's (read Drawing's) SummaryInfo Object's Properties accordingly.

 

HTH

[cmangi86]

Hmm that is interesting... I have never written a LISP routine before. So I guess that will be my next question. What is and how do I write one?

[BlackBox]

Hmm that is interesting... I have never written a LISP routine before. So I guess that will be my next question. What is and how do I write one?

 

That is a loaded question... You'll have to forgive the simplicity implied by my previous post, as I do quite a bit of programming these days.

 

LISP is a programming language exposed to AutoCAD in the form of an API, which allows for users to access Objects to query Property values, Settings, etc.. at the Entity, Document (read Drawing), and Application level. My description in no way does justice to fully quantify an answer here, but I am short on time at the moment.

 

Written quickly... Here's a small example of being able to access, in this case 'view', the SummaryInfo Object's Properties and Methods from the Command Line:

 

(vl-load-com)

(defun c:DumpSummaryInfo ()
 (textpage)
 (vlax-dump-object
   (vla-get-summaryinfo
     (vla-get-activedocument (vlax-get-acad-object))
   )
   T
 )
 (princ)
)

 

You can simply copy this code, paste it at the Command Line, and hit Enter to load the code into your ActiveDocument (read the current drawing)... To invoke, simply enter "DumpSummaryInfo" (no quotes) at the Command Line, and again hit Enter. The rest is self-explanitory (once you invoke).

 

HTH

[BlackBox]

Before I forget... If/When you or someone willing to help you, code this up... Be sure to start thinking about the Excel data you're needing to query. Specifically, does this data need to remain in .XLS(x) format, or can it be exported to .CSV, etc. as there is significant;y less overhead (within LISP) to access that sort of data file, and process, etc..

 

Accessing Excel.Application directly requires not only more overhead, but also error handling... A bit more than the typical LISP beginner is accustom.

[BIGAL]

If your game I think it was over at "theswamp" there is a really good excel lsp to down load I have it at home not at work I think it was "Getexcel.lsp" it has a number of functions, getacell, getmultiple cells and opposites put values into excel. It is well laid out with lots of comments about how it works. I will have a look at home later I think I found it Googling.

[BlackBox]

I could be wrong, but that sounds like a Jefferey P. Sanders (sp?) routine... Further, if its the one I have in mind, it imports the typed library functions with prefixes, in lieu of interfacing with the Excel.Application Object.

 

Perhaps our good friend Oleg can join into this conversation and offer some of his great code samples?

[BIGAL]

Renderman you have me there it makes a "list" of the cells it suited the task I was looking for will paste link when I get home. Looking forward To Oleg contribution.

[irneb]

Yep the Swamp thread: http://www.theswamp.org/index.php?topic=38450.0

 

And the GetExcel.LSP seems to be made by Terry Miller. Some other nifty routines from the same: http://web2.airmail.net/terrycad/AutoLISP-Code.htm

 

And for the DwgProps side of things you might want to look at an old one of mine: http://forums.augi.com/showthread.php?84208-Autocomplete-or-linking-text-within-a-drawing&p=870861&viewfull=1#post870861

[BlackBox]

Thanks for the correction/clarification, Irne

[Terry Cadd]

I recently received an email from someone asking me to take a look at this thread and see if I had a possible solution for his company. I have never used DwgProps, but with the help of some very useful posts on the forums by Gilles Chanteau, I was able to create NewProps.lsp to extract information from an Excel file and put it in DwgProps. I would recommend testing it in a new drawing first. Download and copy NewProps.lsp to a folder in your AutoCAD Search Path. You can download the latest version of GetExcel.lsp from https://autolisp-exchange.com/AutoLISP-Code.htm . Download the images and spreadsheet here to look at and test. Load NewProps.lsp and GetExcel.lsp and type NewProps on the command line to run it.

Best of luck. Terry

NewProps.lsp NA-09317-PB.xlsx

[BIGAL]

Very nice maybe go other way and write a macro in excel so pick top cell c3 pick bottom cell c9. Excel controlling Autocad is not hard.

 

There may be a get current cell function in excel but can access in Autocad similar to the getcell function, then no need for dcl. I do something similar where lisp waits for a OK click then does my version of getcell. Bit like current selection.

 

Something like this in vba but need to rework into a VLISP version it returns like image so should be able to work out row cols. 

 

Sub test()
    Dim myString As String
    myString = Selection.Address
    MsgBox myString
End Sub

 

 

Something along these lines (vlax-get-property  (vlax-get-property myxl "ActiveSheet") "Range" "Selection") any ideas  any one ?

 

 

 

 

Edited August 20, 2021 by BIGAL

[Handiman]

We build our Excel sheet for each rail piece and insert it on to the page.

     In Excel you highlight the cells you want inserted and select copy, then switch to AutoCAD and then Paste "Paste Special" then in the pop up window select "Paste Link" and then "AutoCAD Entities". This will insert the copied cells, it might take 10-15 seconds and then you place the "Sheet"

 

This now when you change the Excel spread sheet do a save and go to AutoCAD and type DATALINKUPDATE and update all links and the AutoCAD file will update to the Excel spread sheet but only on the previously copied cells

[Marreiro]

On 8/19/2021 at 6:42 PM, Terry Cadd said:

I recently received an email from someone asking me to take a look at this thread and see if I had a possible solution for his company. I have never used DwgProps, but with the help of some very useful posts on the forums by Gilles Chanteau, I was able to create NewProps.lsp to extract information from an Excel file and put it in DwgProps. I would recommend testing it in a new drawing first. Download and copy NewProps.lsp to a folder in your AutoCAD Search Path. You can download the latest version of GetExcel.lsp from https://autolisp-exchange.com/AutoLISP-Code.htm . Download the images and spreadsheet here to look at and test. Load NewProps.lsp and GetExcel.lsp and type NewProps on the command line to run it.

Best of luck. Terry

NewProps.lsp 12.63 kB · 15 downloads NA-09317-PB.xlsx 8.71 kB · 11 downloads

 

Hello, I tried these steps but, when I type and enter NewProps, autocad returns the following message in the command line: 

 

Am I doing something wrong?

 

Maybe it was this part of the process:

               I clicked GetExel.lsp from https://autolisp-exchange.com/AutoLISP-Code.htm but no file was downloaded. Instead, a script appeared in my screen. I copied this script and pasted in a .txt file, then converted it to .lsp through "Save as".

 

Both lsp files (GetExcel and NewProps) are in the following path --> C:\Program Files\Autodesk\AutoCAD 2022\Support

[BIGAL]

Things move on when it comes to Excel and Acad. I found a select range from Excel by Fixo so it would then be possible to populate the custom properties if that is what you want. A small improvement on the Terry Cad program.

 

Maybe if Terry is looking he could use this. The myxl is the excel application.

 

; this code was done by the great FIXO
; no longer with us

; if needed
;(setq ExcelValue
;(cond
;((= (type ExcelValue) 'INT) (itoa ExcelValue))
;((= (type ExcelValue) 'REAL) (rtosr ExcelValue))
;((= (type ExcelValue) 'STR) (vl-string-trim " " ExcelValue))
;((/= (type ExcelValue) 'STR) "")
;)
;)


(defun getrangexl2 ( / lst UR CR RADD )
(vl-catch-all-error-p
	   (setq Rng
		  (vl-catch-all-apply
		    (function (lambda ()
				(vlax-variant-value
				  (vlax-invoke-method
				    (vlax-get-property myxl 'Application)
				    'Inputbox
				    "Select a Range: "
				    "Range Selection "
				    nil
				    nil
				    nil
				    nil
				    nil
				    8))))))
)
(setq xrng (vlax-get-property rng "address"))
(setq xxrng xrng)
(repeat 4 (setq xxrng(vl-string-subst "" "$" xxrng)))
(setq xxxrng (_csv->lst58 xxrng))
(setq rngst (columnrow (nth 0 xxxrng)) rngend (columnrow (nth 1 xxxrng)))
(setq *ExcelData@ nil )
(setq Row# (nth 1 rngst))
(repeat (+ (- (nth 1 rngend)(nth 1 rngst) ) 1)
(setq Data@ nil)
(setq Column# (nth 0 rngst))
(repeat (+  (- (nth  0 rngend)(nth 0 rngst) ) 1)
(setq Range$ (strcat (Number2Alpha Column#)(itoa Row#)))
(setq ExcelRange (vlax-get-property myxl "Range" range$))
(setq ExcelVariant (vlax-get-property ExcelRange 'Value))
(setq ExcelValue (vlax-variant-value ExcelVariant))
(setq Data@ (append Data@ (list ExcelValue)))
(setq Column# (1+ Column#))
)
(setq *ExcelData@ (append *ExcelData@ (list Data@)))
(setq Row# (1+ Row#))
)
(princ)
)

; getrangexl2

 

 

[bugwhat]

I have been searching for some time on a way to import fields from excel into Cad, most of what I'm finding is outdated and no longer functions with 2023, I'm trying to set it up to have the engineer enter their data into a spread sheet and I can import that data into the fields I have already or overwrite them. What are the chances I can get the two files shown below so I can test the functionally of them? these post are newer than what I normally find (2003-2009). 

Thanks Andy

On 8/19/2021 at 5:42 PM, Terry Cadd said:

 

NewProps.lspUnavailable NA-09317-PB.xlsxUnavailable

 

[BIGAL]

There is no problems reading CELLS from Excel, I think your using wrong terminology "fields" ? Can you explain more what a field is best is post a dwg and matching excel. A field has a distinct meaning in CAD.