Wednesday, June 12, 2013

Using Excel to Insert names automatically

A while ago I had a question posed to me about linking an excel document to Autocad and then having a diesel macro that establishes a user variable and then returns that value to the excel document which in turn returns another variable to Autocad for labeling functionality.  For instance if you were assigning names to office spaces and you had them all in an excel document.  You could create a vlookup function in a cell, and have the cell that autocad will return a value to as the one that the vlookup is looking at to return your name. 

The diesel macro establishes a user variable is the indexing number and another one as the incrementing number.  Each time you use the command it will update one table which in turn updates the excel document and returns you the name that is associated with that number.

Is there anyone that has needed this function or any one who would like to have this function?  If so, keep reading this post.

Alrighty…the instructions to automating (fingers crossed) the names being inserted by table and then exploded.
First up…creating a new table style:

In the command line type: tables  

Then hit enter.

The following dialog box should come up.

Once you click the “New…” button enter whatever name you want to name it.
In the next dialog box follow as shown in pic below


In the borders tab change the border to be “No Border”
Then hit okay.
Once done you will go back to the main dialog for the table styles.
Make sure that the current style set is the one that you just created.

Now comes is the fun part…
First…lets create a table that is (1) column x (1) row. And then insert that into your drawing.
Now pick on that table and highlight cell “A1”…
Then on the ribbon panel you will see a button that says field…click that button. 


Then in the field dialog change your “Field category:” to show “Other”.
In the “Field names:” column pick “DieselExpression”.
And then in the box to the right enter the diesel expression that is shown.
See pic below on next page for clarification…
Once the expression is entered then click the okay button to get out of the dialog box.  And then escape out of your table.  If done right then the value in the cell should look like what is in the above picture…gray with white text.

Next is some of the tricky stuff.
Pick the table that you just created
And click on the link cell button as shown in picture below.

Then follow the steps that are shown below in pictures.



Here is where you will select the excel file with the names that you are going to be generating in your cad file.

Be sure to select the “link to range” button and then put a random cell in there that you can remember for later when you have to get into your excel doc and create a vlookup value.

p.s. you have to do at least a 2 cell range or it won’t take it.  Also, make the sheet a different one from where the names are as well.

Now…we need to get into the excel file, and I am kind of hoping that you have the names numbered 1-300 or something because it will make it a lot easier, but if you have a different case of associating a number to names then leave a comment about what you are needing and I will see what I can do to help remedy the issue.
In the excel doc…on the sheet with the names; off to the right of those columns pick an empty cell and create a vlookup…the “Sheet?!A!” is referring to whatever sheet and cell you linked the table to that you have already created.

=vlookup(Sheet?!A1,A1:b300,2)

Make sure you save the excel doc and then close it…
Next…
We are going to go through the creating the datalink to the same excel doc but this time the linked range is going to be the one that is going to show the name in it.
When creating this datalink…I would recommend naming it “names2” just because that name is already built into the macro.
Now once you have that done you are basically ready to create the button with the macro…
Below is the macro for the button…

*^C^Cupdatefield;all;;tabledit;\;datalinkupdate;w;all;;-table;l;names2;\explode;$m=$(getvar,lastpoint);setvar;userr1;$m=$(+,$(getvar,userr1),$(getvar,userr2))

It is amazing how short it is, and yet there is so much work prior to being able to use it.
When you run the macro the first thing it will do is prompt you to pick a table…and you will pick the initial table that you built that has the updating variable number.  Once you pick it then the next thing you do is place the name.
Once you place it you will notice that it has borders…and that is because it is inserted as a block and then explode in the macro.  The border lines won’t print out on your drawing.
Again, we are using userr1, and userr2; so userr2 always stays as a value of 1.
Okay…I think that is it…at least I hope that is it.
Let me know if there is anything else that you need clarified and I will do my best to help clarify things.

No comments:

Post a Comment