Excel to Autocad - batch

[Scotsman]

Hi, I want to use a list of parameters in Excel to create multiple parametric blocks in a single Autocad drawing (actually it's Bricscad but all the commands/scripts work the same)

Here is my code so far in ME which works fine for a single block:

 

 

And here is my spreadsheet showing the parameters I want to use to create the various blocks:

I tried using ASCII file Begin Process but that only grabs the parameters, while I need to have the ACAD commands as well. Any suggestions on how to go about this?

thanks

 

 

 

 

 

[Cory]

I'd use AutoLISP.

[Scotsman]

Hmm.. I'm not familiar with Lisp - I heard others mention how great ME is with Autocad but maybe this idea is a bit of a stretch? Or is it possible, but more work then most macros? Was hoping to use ME as it seems quite powerful and intuitive Plus I'd like to keep all my ACAD macros together (in ME) but maybe I'm barking up the wrong tree?

 

[Scotsman]

Or do you mean Lisp in combination with ME?

[rberq]

Probably you could use the ASCII File Process, but hard-code the parameter descriptions in your Text Type commands.

For example, if you use an array named "column" to receive the file (spreadsheet) rows, the X parameter will be the second item of the array.

 

So

   Text Type X

   Text Type <SPACE>

   Text Type %column[2]%

   Text Type <ENTER>

 

Or for each parameter make a single Type statement like in your example:

   Text Type _bminsert <ENTER> %column[1]%<ENTER>

   Text Type X %column[2]%<ENTER>

   Text Type Y %column[3]%<ENTER>

   Text Type Z %column[4]%<ENTER>

 

[Cory]

I used to be an expert at AutoCAD but I moved into IT about 20 years ago and I've not used it much since, so my information might be dated. 

I put a link in my post. AutoLISP it the automation language in AutoCAD. It was structured to be syntactically like LISP not it's not LISP. AutoLISP exists only in AutoCAD and is akin to VBA for MS Office products. Back in my day and since the beginning of AutoCAD, AutoLISP was the most powerful automation tool in AutoCAD. I think it could do anything. I only wrote a few and had other employees write more complex programs, but it's a wonderful tool and does not use the GUI. It is however syntactically difficult for someone who has never programmed before. However if I had automation to do in AutoCAD and I had a choice to learn MEP or AutoLISP, I'd do AutoLISP. 

But if you want to stick with MEP for other reasons, I'm sure it can do what you want. Like RBerg said, ASCII file process is your friend. I think you would start with you would have MEP type the AutoCAD Insert command. I Don't remember being able to specify attributes on insert thought, so I think you would have MEP enter the AutoCAD ATTEdit command with the "last" option for selection. But it's been awhile. 

I do have AutoCAD still and MEP. I'm sure I can help you find a way. 

[Cory]

Oh, and export the Excel to a CSV or Tab delimited file. I prefer tabs. Or use MEP to parse the clipboard contents after you copy the range in Excel. In all MS apps, they're tab delimited and easy to spit. I split by lines then split into cells I then add to arrays. 

[Cory]

Looking at posts like this, it seems pretty simple to create, insert, and edit attributes of a block in AutoLISP. 

[Cory]

Insert

AttEdit

 

[Cory]

You know.... You could use MEP or any programming language to create the text of a script file then run the script in AutoCAD. That would eliminate all the problems with timing and such. I did this once to create an unsupported solid helical geometry once in AutoCAD. That would make much more sense. 

[Scotsman]

Thanks for the replies! - it will take me some time to try a few ideas on my end with these suggestions.

cheers

[terrypin]

Faced with learning a new programming language or persisting with MX Pro it would be ‘no contest’ for me! But then I have no AutoCAD experience.

 

I’d probably tackle it just as Robert suggests. However, if I’d already methodically set up an Excel spreadsheet as you have, then I’d first just try a VBA macro from Excel itself. If you have no VBA experience try recording the steps. The devil as usual will be in the details. For instance, where must the processed text end up before being executed by AutoCAD?

 

EDIT: Thinking about it over breakfast it should be relatively straightforward in either MX Pro or Excel VBA (or a combination of both, as I've sometimes used). Screenshot shows the way my mind was working:

 

Terry

 

[Scotsman]

Thanks, I have Autocad experience but not with writing Lisp so I lean towards MEP as well.

 

I also would prefer not to use Excel to write the script for a few reasons:

- I really like the MEP activation - shortkey especially for ease of use.

- spreadsheets sometimes get lost over here, but with MEP the script stays safe in the program.

- I don't have to go digging for the spreadsheet to run the script since I have MEP on most of the time.

- The spreadsheet I put together for the parameter list is easily reproducible.

 

So I'm leaning towards Excel as the list only, with the whole script in MEP. Will keep working in this direction and hopefully have some success soon.

I've been trying rbergs suggestion with the array variables with some success but need to learn more.

cheers

 

 

 

 

[Cory]

I covered using MEP as well in my message and made many suggesting including the AutoCAD commands needed. 

Also, I don't see why you wouldn't use AutoCAD scripts. But, whatever. Do it your own way. 

[Scotsman]

I've been thinking about your and others comments about lisp and would like to learn more. Maybe it could be combined with MEP to make use of the fast activations. Re the scripts, I have used them in Acad but only for small things. I need a way of drawing from the excel sheet and repeating the same commands for multiple blocks, which led me to MEP.  I should know pretty soon if it will pan out.

thanks again...

 

[Cory]

You could save the source information as a tab file and edit the tab file in Excel. There's no reason to keep it in the native Excel format unless yo need formulas and such. But even then, MEP can do that math for you. 

If I wanted to use MEP I'd copy the block properties from the tab file, mentioned above, using ASCII File Process and have MEP create output the command sequence to a text file. Then have MEP run the file as a script, then delete the file for you. Whether it's one or a thousand, it woudl work. The main reason I say that is speed and timing. MEP is unaware of when things finish so you either have to add much delays for each command or handle errors when it goes to fast. By making a script, AutoCAD will do it as fast as possible and you will have no timing issues to deal with. 

 

[Scotsman]

Interesting - Acad does seem to be unpredictable when/if it will finish each operation, so I've had things fail when running a script for a single block.

I can do the file process copy but the rest is a bit fuzzy - I don't suppose you know of an example floating around? I can definitely see the advantage in this approach.

thanks

[Cory]

I sent you links to the Insert and AttEdit commands from the AutoCAD documentation. Did you see that or are you speakgin of somehting else?

[Scotsman]

this is the part I was speaking of:

 

"using ASCII File Process and have MEP create output the command sequence to a text file. Then have MEP run the file as a script, then delete the file for you."

 

thanks

[Cory]

OK. Give me a couple of minutes

[Cory]

Attached is a simple macro to illustrate ASCII File Process to create a script file with a sample input and output. I can't remember the syntax in AutoCAD so I'm just going to make up one. You can change the text to whatever you want. Say the command to create a line was the word "Line" followed by the coordinates of both ends. The idea being this would be just like what the user woudl type into the command box and can be run at once as a script in AutoCAD. The input file is a few lines with the X and Y of the ends of the lines. E.G. the first one would create a line from 1,1 to 2,2. You could make multiple lines per input row also. Whatever you need. For simplicity I just made the output file have an "-out" suffix. This could be somehting prompted for or done more properly later. 

Lines.mex Lines.txt Lines-out.txt

[Scotsman]

Wow!- thanks so much, I'll go over this tonight and report back.

cheers

 

[Cory]

I was thinking.... Maybe you could insert a block and edit it's attributes manually. Then copy the command sequence text and post it here. Make it a dummy block and make it as simple as possible. Maybe do this. 

FYI I might be indisposed later tonight and tomorrow, so my responses might be delayed. 

Good luck. 

[Scotsman]

double post

[Scotsman]

Here it is - just a little block with 3 parameters. I had to turn the file dialog off, but this should give a rough idea. I use Bricscad not Autocad so the syntax may be slightly different but all the commands are the same.

thanks

SampleBlockScript.scr