MACROS BASED ON AUTOCAD2000 INTEGRATION WITH EXCEL 
Dr. R. Tigrek    tigrek@hotpop.com    www.homescript.com


DESCRIPTION OF MACROS - TEXTOUT AND TRANSLATE 
FOR  MANAGEMENT OF AUTOCAD TEXT FROM EXCEL
1 FREEWARE Delivery: textout.zip  for AutoCAD 2000  .
a textout.zip  file contains  the macro file (textout.dvb) and this help file   (FREEWARE) 
translate.zip file contains  the macro file (translate.dvb) and this help file (ON SALE)		 .
b Macro file translate.dvb adds the following commands to your AutoCAD 2000

TextOutofAllDWGs (freeware  - textout.dvb)

TextOutofOneDWG  (freeware  - textout.dvb)

TranslateAllDWGs  (on sale - translate.dvb)

TranslateOneDWG  (on sale  - translate.dvb)

TextOut commands export all text inside the DWG file(s), including plain text, Dtext, Mtext, Rtext, Attributes and text nested inside blocks.

Translate command imports equivalent text from excel into DWG file(s).

AllDWGs performs this operation on all DWG files in the same directory as the DWG from which you run the macro, including any subdirectories under it; translate command saves the modified drawings where they were before. Textout command makes no change to the drawings.

OneDWG commands do not save any files. Operation is performed on the currently open drawing. You save it if you like.
2 How to install a VBA macro
a Where to copy the macro file: translate.dvb
It is customary to copy extra macros under directory Acad/Support, which is a default project file and automatically included in the search path of AutoCAD.

However, if you are going to have many extra macros, it is good practice to open a directory for them, like Acad/Support/MyMacros/ and in Options add this directory to Acad project directories so that the files may be found and loaded.
b How to load the project: translate.dvb
You load the macro into AutoCAD2000 by Menu + Tools + Macros + VBA Manager + Browse
If you try the commands and find them often needed, it is possible to automate the loading so that every time you start AutoCAD, the commands will be automatically loaded. This being standard AutoCAD knowhow, I am not explaining here but if asked, I will. To access the commands through tool box, you go to Acad Menu View + ToolBoxes + New + "Translate" etc. + Add four boxes into the new tool box + Click Mouse RIght Button on Each Box  + Select a  pictures for the box + write the name of macro for each + and here is the real functional line for macro:-vbarun textout.textoutofonedwg     and similarly for every tool box one of the macros. From now on you can click the tool and the macro runs. But still, on start of AutoCAD, the project translate.dvb has to be loaded through Menu+ Tools + macros + load VBA which can also be automated. I will tell how if you ask.
3 How to work with the new commands to edit or translate text inside drawings from Excel
AutoCAD 2000 Menu + Macros + TextOutofAllDWGs + Run
AutoCAD 2000 Menu + Macros + TextOutofOneDWG  + Run 
AutoCAD 2000 Menu + Macros + TranslateAllDWGs  + Run 
AutoCAD 2000 Menu + Macros + TranslateOneDWG  + Run 
TextOutofAllDWGs exports all text in all the drawings of the current directory into an Excel sheet. There you can sort the text and check for uniformity, spelling, etc. In excel you can use seach method to find which text exists in which drawings. this is a very convenient way of looking for an old drawing by some text in it. Of course if you do not have Excel on your computer, this will not run. If other complications show up, please contact me: tigrek @hotpop.com
Here is how the exported text looks in Excel:
NO ENTITY TEXT TRANSLATE  DWG
2
 AcDbText ARMS C:\acad\dwgsimple\attrib.dwg
3
 AcDbText BOTH C:\acad\dwgsimple\attrib.dwg
4
 AcDbText HOUSING FIRST C:\acad\dwgsimple\attrib.dwg
5
 AcDbText SCREWS GO THRU C:\acad\dwgsimple\attrib.dwg
6
 AcDbText HOUSING C:\acad\dwgsimple\attrib.dwg
7
 AcDbText LOCATED INSIDE C:\acad\dwgsimple\attrib.dwg
8
 AcDbText STRAIN RELIEF C:\acad\dwgsimple\attrib.dwg
9
 AcDbText AutoCAD C:\acad\dwgsimple\attrib.dwg
10
 AcDbText R14 Sample Drawing C:\acad\dwgsimple\attrib.dwg
11
 AcDbAttribute CE003902 C:\acad\dwgsimple\attrib.dwg
12
 AcDbAttribute SOCKET C:\acad\dwgsimple\attrib.dwg
13
 AcDbAttribute CH00531 C:\acad\dwgsimple\attrib.dwg
14
 AcDbAttribute RIVET #511 X 3/16 C:\acad\dwgsimple\attrib.dwg
15
 AcDbAttribute CG17903 C:\acad\dwgsimple\attrib.dwg
16
 AcDbAttribute VERT CARR BRKT. ASM C:\acad\dwgsimple\attrib.dwg
17
 AcDbAttribute CG1291801 C:\acad\dwgsimple\attrib.dwg
18
 AcDbAttribute FLEX ASM. 3/8 X 18 W/1 STR. CON C:\acad\dwgsimple\attrib.dwg
19
 AcDbAttribute CG11914 C:\acad\dwgsimple\attrib.dwg
 
TranslateAllDWGs is the reverse operation. Except, it starts from the second sheet of Excel file where you have the originally exported text and the new text you want to replace the original. This can be editing, revising or translating. You can get the translations from any source and paste into Excel.
Assuming we want all the text to be replaced with lowercase: here is how the translate table looks in Excel
NO ENTITY TEXT TRANSLATE 
2
 AcDbText ARMS arms
3
 AcDbText BOTH both
4
 AcDbText HOUSING FIRST housing first
5
 AcDbText SCREWS GO THRU screws go thru
6
 AcDbText HOUSING housing
7
 AcDbText LOCATED INSIDE located inside
8
 AcDbText STRAIN RELIEF strain relief
9
 AcDbText AutoCAD autocad
10
 AcDbText R14 Sample Drawing r14 sample drawing
11
 AcDbAttribute CE003902 ce003902
12
 AcDbAttribute SOCKET socket
13
 AcDbAttribute CH00531 ch00531
14
 AcDbAttribute RIVET #511 X 3/16 rivet #511 x 3/16
15
 AcDbAttribute CG17903 cg17903
16
 AcDbAttribute VERT CARR BRKT. ASM vert carr brkt. asm
17
 AcDbAttribute CG1291801 cg1291801
18
 AcDbAttribute FLEX ASM. 3/8 X 18 W/1 STR. CON flex asm. 3/8 x 18 w/1 str. con
19
 AcDbAttribute CG11914 cg11914
4 To simplify the use of macros, I have fixed the following: 
a Translate table is the second sheet of the Excel Workbook. Translate macro will look nowhere else.
b Columns 1 and 5 are ignored by the translate macro.
c You copy columns 2 and 3 from first sheet of Excel as exported by textout macro. You can sort the rows however you like, but allways Entity type and text to be on the same row. 
d Column 4 is where you put the changes or translations that are to replace what is in column 3
e You need to prepare translations in Excel only once. Later you open this Excel file and translate any number of other drawings automatically. This is the strength of my macro. Eventually, when you have a big excel file for all the text that may appear in your drawings, you can use the same file to translate any number of drawings from one language to the other at one click
f I tested it for extreme case of translation between Japanese and English - it worked. But with a trick: The Japanese text is known to AutoCAD but not plain Excel. So, first the Japanese text has to be written in AutoCAD and exported to Excel. It appears as code numbers in Excel, but when reimported into AutoCAD, assumes the Japanese letter shapes again.
g The macros here replace text while keeping the text  properties of the original text: size, style, orientation, direction, rotation, layer, color, etc.   If your projects require some other approach here, just contact me for a revision of the macros. I shall be pleased.
5 For further questions or to order the full version to translate Acad drawings, refer to:
www.homescript.com

Back to Top