Visual LISP, AutoLISP and General Customization
cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 

reading .xlsx files and modifying a block

7 REPLIES 7
Reply
Message 1 of 8
sethtriad
3117 Views, 7 Replies

reading .xlsx files and modifying a block

I was searching the series of tubes for an answer, but I couldn't find one, so I thought I'd ask here:

 

Is it possible to write an AutoLISP script to read an .xlsx file and use the data in the various cells to modify a block within a .dwg?

 

Ex.: We use Flow Control Modules (FCMs) and use AutoCAD Electrical to show electrical contractors where/how to wire up the devices. In AutoCAD, we have blocks for various types of FCMs, and each block has terminals which are tagged DESCA1, DESCA2, DESCA3, etc. Each of these tags will change based on what the particular FCM is used for. 

 

We also have .xls(x) files we call FCM Maps, which come from our engineers and tell the drafters how to lay out the .dwg files. 

 

Is it possible to write a LISP script to take, say, cell A1 from the .xlsx file, and put that data into DESCA2 in the block, cell A2 into DESCA3, and so on?

7 REPLIES 7
Message 2 of 8
Hallex
in reply to: sethtriad

I know nothing about your stuffs sorry

To get you started try this code

Note Excel file must be open and activated

on desired worksheet before you run this lisp

_____________________________________
C6309D9E0751D165D0934D0621DFF27919
Message 3 of 8
sethtriad
in reply to: Hallex

This is really cool!

Could you explain this section a bit so I can expand upon your work:

 

(cond ((eq (vla-get-tagstring attref) "DESCA1")
			 (vla-put-textstring attref (nth 1 vl)));A2 (if cell A1 is as header)
			((eq (vla-get-tagstring attref) "DESCA2")
			 (vla-put-textstring attref (nth 2 vl)));A3
			((eq (vla-get-tagstring attref) "DESCA3")
			 (vla-put-textstring attref (nth 3 vl)));A4
			;;	---	etc	etc	---	;;

 I'm assuming that DESCA1 of the selected block is filled with information from cell A2, right? How do I modify the "nth 3 vl" to selelect different cells of the spreadsheet? Say I waned information from cells A2, B3, C4, D5, how would I modify this code to include those cells.

Message 4 of 8
Hallex
in reply to: sethtriad

I changed code to select list or separate cells,

see block attributes , yo may need to change them all

if you need to select block by name then add

check on this name or select using:

 

(setq sset (ssget "_+.:S:E" (list (cons 0 "insert")(cons 2 blockname)))))

(setq en (ssname sset 0) ... etc, etc

 

hth

_____________________________________
C6309D9E0751D165D0934D0621DFF27919
Message 5 of 8
sethtriad
in reply to: Hallex

Your first script seems like it has more potential for what I need: open a .dwg w/ the block in it, run the script, select the block, then select the range of cells in excel, and the script would fill out the tags in the block. 

 

In your scripts, you have a few lines of code that pick cells from excel and assign them to tags in the block within ACADE: 

(cond ((eq (vla-get-tagstring attref) "DESCA1")
			 (vla-put-textstring attref (nth 1 vl)));A2 (if cell A1 is as header)

 

 How does that select cell A2?

 

Does it base the information on how I select the cells in excel? If I started my selection at B2, for example, would that code put the B2 data into "DESCA1"?

Message 6 of 8
Hallex
in reply to: sethtriad

Second routine is works good both ways with single cell as well as with list

You have just change ta part to working with your block,

this is another deal, I don't know either your block name and attribute, or

other things you want to pull cell data to, it looks like homework question for me.

Thus you have to finish it by yourself, sorry

_____________________________________
C6309D9E0751D165D0934D0621DFF27919
Message 7 of 8
sethtriad
in reply to: Hallex

I've attached a blank FCM .dwg to show what I/we start with as well as a typical FCM .dwg and an example FCM map which is where the data comes from for the FCM descriptions/tags.

 

in the typical FCM .dwg, every description that has "meter", "pulser", "no solenoid", "nc solenoid", "additive", "control valve" are standard and never change. Every description that has "Product A/B", "Additive A/B", "FCM 1", "LANE 1 FCM PANEL", "LANE 1", all come from cells in a spread sheet. 

 

Looking at the descriptions on the blocks, each LOC description is changed from "FCM PANEL", in the blank FCM, to LANE 1 FCM PANEL, where LANE 1 comes from cell A1 in the example excel spreadsheet. 

 

DESC1 on all these blocks is derived from cell A2 in the spreadsheet. 

Message 8 of 8
Profmanprof
in reply to: Hallex

Hello,

 

can you check "table control dynamic  block ?????

 

 

can we upgrade that lisp to drive block with block properties in custom ??????????

 

thanks your hep....

best regards.

Can't find what you're looking for? Ask the community or share your knowledge.

Post to forums  

Autodesk Design & Make Report

”Boost