AutoCAD Title Block from EXCEL VBA

[JGupte]

BIGAL,

 

 

Alternatively, I have the code to save each drawing update in a TXT file that can be imported using ATTIN. But the ATTIN function asks for the TXT file to be selected manually.

Is there any way to automate this so it always uses the same file?

I can then create the TXT file for each drawing within my script loop, then run ATTIN on each drawing individually. I know it will take some time, but this script can be run overnight or even on weekends.

 

 

I don't really care how it is done, just need it done.

 

 

All help appreciated.

 

 

JG

[BIGAL]

Dumping out a txt file makes it even easier as you are not relying on the link to excel. A lisp file can be called as part of a script file to do changes on multiple dwgs like changing certain attributes.

 

re 61 attributes not really a problem either if the output file was like this, it will get a liitle more complicated if you have multiple layouts and various atts to suit the cahnging layouts. Note what I am thinking about, attributes do not need to be in order as it uses the position number for updating.

 

layout1,""
1,"attribute 1 value"
2,"attribute 2 value"
3,"rev 1"
4,"attribute 4 value"
layout2,""
1,"attribute 1 value"
2,"attribute 2 value"
3,"rev 2"
4,"attribute 4 value"

 

the script would be something like 
open dwg1 (doupatts dwg1.txt) close Y
open dwg3 (doupatts dwg3.txt) close Y
open dwg4 (doupatts dwg4.txt) close Y

 

We need two things a excel that makes sense and a sample dwg.

[JGupte]

Dumping out a txt file makes it even easier as you are not relying on the link to excel. A lisp file can be called as part of a script file to do changes on multiple dwgs like changing certain attributes.

 

Yes, but as I don't know how to create or change LISP files, I am stuck.

The ATTIN.LSP does what I want, but asks the user to select the input file. And as we have over 2000 files to update, nobody is going to sit there and sect the file 2000 times - which is my stumbling block.

[BIGAL]

Your on line ! re read my post code will be lisp as I have done all the routines, but can be converted to VBA as method will be similar.

[JGupte]

We need two things a excel that makes sense and a sample dwg.

 

 

Here are the files.

 

 

Although I am trying to update 10 drawings in this procedure, I have only attached 1 (the other 9 are simply copies of this drawing).

 

 

JG

SLR-ALS-D50-CSR-DWG-063201.dwg

Update AutoCAD.zip

[JGupte]

Your on line ! re read my post code will be lisp as I have done all the routines, but can be converted to VBA as method will be similar.

 

 

Sorry, I can't understand code snippets you posted.

 

 

Are they for LISP or VBA? (I am not very good at VBA, and don't know LISP at all.)

 

 

I have tried to modify the ATTIN LSP to open the file

C:\Users\297560\Documents\Development\AutoCAD Update\Block_Update_In.txt

 

 

but I get the message "Error opening input file for read" when I run it in AutoCAD.

 

 

(see attached LSP file)

 

 

What am I doing wrong?

ATTIN_SLR.lsp

Edited August 24, 2017 by JGupte

[maratovich]

JGupte

I do not quite understand why you are using an intermediate of Lisp ...

Describe just what you want.

Do you want it that way? :

1. File Excel. The first line contains the names of the attributes. The second line shows the values of the attributes.

2. Browse all files

3. In each file, find the header block.

4. Find in this block an attribute by name from Excel.

5. Replace the value of the attribute in the block with the new value from the excel.

 

Maybe you want to change only one attribute? Why go through all the attributes?

Why get attributes from a file in a text file?

[JGupte]

Hi maratovich,

 

Yes, you have the requirement exactly.

 

Our project has sort of run away from us. The draftsmen have been very inconsistent with the values they enter in title blocks.

Management have decided they want to standardise the entries.

By putting all the entries in a spreadsheet, they can then ensure the values all meet the standard.

 

 

The txt file method (ATTIN) is only one method I was exploring.

GATTE (update all blocks of same name in dwg - multiple sheets) was another - and possibly a better method?

However, with this method, I was having problems selecting the blocks.

 

 

 

So they will fill out the spread sheet table.

My job (this script) is to go through the list and ensure all entries are as per the spread sheet.

 

So what I need to do is:

 

Loop through all dwg files in a list

for each dwg file,

open the dwg file

find the Title block values in my spread sheet and export them to the drawing

save and close the dwg file

Loop

 

If it saves a bit of processing time, I can restrict the updating to only updating those attribute values that are different from the value in my spread sheet.

(but I think this would take more time, not less).

[BIGAL]

Everything is there in the xcel file you have a dwg name you have a Tag name so no need to go down the attin path, you can open a dwg read the tag name, read correct value including nil and then up date. It gets a little trickier in that you want to update multiple dwgs and would need to possibly use a script. But its doable. I would check existing against excel so skip update. Check revs are +1 etc.

 

1st off GOOGLE "update title block form excel" I can not emphasise more loudly "ITS OUT THERE DONE ALREADY"

 

That is where I would start, customising someones code to suit your needs. I use Getexcel.lsp it has all the code done to read your excel file. Yes it is lisp but here should be an equivalent VBA version out there as well.

 

The important thing here is it is a two way system alter in Autocad and update your spread sheet.

 

Just a side note if you had posted your excel and dwg as part of 1st post it would have been all done by now.

[BIGAL]

Just another question why is there only 1 layout for a project of this type, playing with a 10km road at moment, I would have all the sheets in the 1 dwg as layouts rather than 1 dwg per sheet very odd way to do it. I have a road project with 88 layouts and update the title blocks so much easier, obviously the dwg No is part of the title block. 2-3 lisps to do certain things to the title block.

 

Doing it this way is so so much easier.

 

Given the type of project I am suprised your company is going down this path this is so old fashioned a way of doing things, is it because your civil software and I have a suspicion which it is does seperate dwg sheets, is there not an option to do all in one dwg.

 

I do appreciate a project of this type may have hundreds of sheets and yes it would have seperate dwgs but these would contain all the relevant sheet portions of a project.

 

There is a lot of discussion here about model v's layouts but the final result is still multiple sheets in one dwg.

 

We press a button and multiple layouts are created within or a new dwg from our civil design software. Going the other way would be so much better pull all the individuals into a master dwg which has all the title block details, its not hard thing to code because you would pull a whole dwg in and make new layouts if required. Also you make changes in your dwg regarding versions this should be pushed to the excel not the other way around to ensure quality control, the guy edits the excel tells the drafty but he/she goes home sick it suddenly comes unstuck.

Edited August 26, 2017 by BIGAL

[JGupte]

Hi BIGAL, and thanks for your replies.

 

Firstly, at the beginning, I did not have any EXCEL code. I was trying to create it and looking for ideas. In parallel to this forum thread I have been experimenting with various functions to find the best suitable to my purpose.

 

We actually have quite a few drawings with multiple sheets, and multiple drawings, each for a different aspect of the project.

 

As a start I was working with a single page drawing, but came to realise that was not the best way to start as I would encounter problems when I moved to multi-page drawings. I have now switched to multi-page drawings.

I am not a draftsman, and this is my first exposure to AutoCAD. am a Document Controller, with some experience with Excel Macros, which is why I have been asked to work this out.

 

All our drawings come from the same template (for title block) so have the same attributes.

 

I have figured out that the GATTE function is what I need to update multiple title blocks in one drawing with the same attribute names.

 

I just can't get ti to work. It does not recognise the attribute names, even though they are in the list that GATTE returns.

 

JG

[BIGAL]

I think your still going down the wrong path its probably simpler to go directly to the tag name and update using a lisp or VBA. You should be able to drive from excel but I would still recommend doing updates in Autocad and updating the excel.

 

Here is one example of updating attributes. Others have plenty also. You should also look at http://www.lee-mac.com for his global block attribute update code, you may be able to drive that from the command line.

 

; update a specific dwg job title block info
; change the 410 to layout name

;;-------------------=={ Parse Numbers }==--------------------;;`
;;                                                            ;;
;;  Parses a list of numerical values from a supplied string. ;;
;;------------------------------------------------------------;;
;;  Author: Lee Mac, Copyright © 2011 - www.lee-mac.com       ;;
;;------------------------------------------------------------;;
;;  Arguments:                                                ;;
;;  s - String to process                                     ;;
;;------------------------------------------------------------;;
;;  Returns:  List of numerical values found in string.       ;;
;;------------------------------------------------------------;;

(defun LM:ParseNumbers ( s )
 (
   (lambda ( l )
     (read
       (strcat "("
         (vl-list->string
           (mapcar
             (function
               (lambda ( a b c )
                 (if
                   (or
                     (< 47 b 58)
                     (and (= 45 b) (< 47 c 58) (not (< 47 a 58)))
                     (and (= 46 b) (< 47 a 58) (< 47 c 58))
                   )
                   b 32
                 )
               )
             )
             (cons nil l) l (append (cdr l) (list nil))
           )
         )
         ")"
       )
     )
   )
   (vl-string->list s)
 )
)



(defun ah:sheetupdate1 ( /  LEN ss1 lay plotabs tabname sheetnum )

(SETVAR "REGENMODE" 0)

(setq doc (vla-get-activedocument (vlax-get-acad-object)))
(vlax-for lay (vla-get-Layouts doc)
 (setq plotabs (cons (vla-get-name lay) plotabs))
)

(setq dwgname "2010026")
(setq bname "DA1DRTXT")

(setq plotabs (vl-remove "Model" plotabs))
(setq plotabs (vl-remove "PlotPreview" plotabs))

(setq oldtag1 "SHT_NO") ;attribute tag name

(setq oldtag2 "DRG_NO") ;attribute tag name

(setq oldtag3 "PROJ_NO") ;attribute tag name
(setq newstr3 "2010026")

(setq oldtag4 "REV_NO") ;attribute tag name
(setq newstr4 "PA")

(setq oldtag5 "PROJECT_DESCRIPTION") ;attribute tag name
(setq newstr5 "SEALING OF ROADS")

(setq oldtag6 "PROJECT_TITLE") ;attribute tag name
(setq newstr6 "NORTH EAST PORTARLINGTON")

(setq oldtag7 "DIRECTORY") ;attribute tag name
(setq newstr7 "MELWAYS")

(setq oldtag8 "MAP_REF") ;attribute tag name
(setq newstr8 "444&445")

(setq oldtag14 "SHEETS") ;attribute tag name
(SETQ NEWSTR14 (rtos (length plotabs) 2 0)) 

(setq oldtag15 "STREET") ;attribute tag name
(setq newstr15 "NORTH EAST PORTARLINGTON")

(setq oldtag16 "SUBURB") ;attribute tag name
(setq newstr16 "ROAD SEALING")

(setq oldtag18 "DESIGNER") ;attribute tag name
(setq newstr18 "BIGAL")

(setq oldtag19 "DRAFTER") ;attribute tag name
(setq newstr19 "BIGAL")

(setq oldtag20 "SHT_SIZE") ;attribute tag name
(setq newstr20 "A1")

(foreach tabname plotabs 
     ; no need to regen each sheet
     ;(setvar "ctab" tabname)
     ;(command "pspace")

     (setq ss1 (ssget "x"  (list (cons 0 "INSERT") (cons 2 bname)(cons 410 tabname))))
     (setq dwgnum (Lm:parsenumbers tabname))
     (setq sheetnum (car dwgnum))
     (setq newstr1 sheetnum)

   ; if less than 10
   (if (< (car dwgnum) 10.0) 
     (setq newstr2 (strcat dwgname "-D0" (rtos sheetnum 2 0)))
     (setq newstr2 (strcat dwgname "-D"  (rtos sheetnum 2 0)))
   )
     (foreach att (vlax-invoke (vlax-ename->vla-object (ssname SS1 0 )) 'getattributes)

      (if (= oldtag1 (strcase (vla-get-tagstring att)))
       (vla-put-textstring att newstr1) 
       ) ; end if
       (if (= oldtag2 (strcase (vla-get-tagstring att)))
       (vla-put-textstring att newstr2) 
       ) ; end if
       (if (= oldtag3 (strcase (vla-get-tagstring att)))
       (vla-put-textstring att newstr3) 
       ) ; end if
       (if (= oldtag4 (strcase (vla-get-tagstring att)))
       (vla-put-textstring att newstr4) 
       ) ; end if
       (if (= oldtag5 (strcase (vla-get-tagstring att)))
       (vla-put-textstring att newstr5) 
       ) ; end if
       (if (= oldtag6 (strcase (vla-get-tagstring att)))
       (vla-put-textstring att newstr6) 
       ) ; end if
       (if (= oldtag7 (strcase (vla-get-tagstring att)))
       (vla-put-textstring att newstr7) 
       ) ; end if
       (if (= oldtag8 (strcase (vla-get-tagstring att)))
       (vla-put-textstring att newstr8) 
       ) ; end if
       (if (= oldtag14 (strcase (vla-get-tagstring att)))
       (vla-put-textstring att newstr14)
       )
       (if (= oldtag15 (strcase (vla-get-tagstring att)))
       (vla-put-textstring att newstr15)
       )
       (if (= oldtag16 (strcase (vla-get-tagstring att)))
       (vla-put-textstring att newstr16) 
       )
       (if (= oldtag18 (strcase (vla-get-tagstring att)))
       (vla-put-textstring att newstr18) 
       )
       (if (= oldtag19 (strcase (vla-get-tagstring att)))
       (vla-put-textstring att newstr19) 
       )
       (if (= oldtag20 (strcase (vla-get-tagstring att)))
       (vla-put-textstring att newstr20) 
       )
      
    (command "_.pspace")
    (COMMAND "ZOOM" "e")
(PRINC (GETVAR "CTAB"))
) ; end foreach
) ; end foreach

(SETVAR "REGENMODE" 1)

) ; end defun ah

(setq curtab (getvar "Ctab"))

(vl-load-com)
(ah:sheetupdate1)


(setvar "ctab" curtab)
(princ)

This is a direct method example error checking has been removed.

(setq obj (vlax-ename->vla-object (car (entsel "Select Block"))))
(setq att (vlax-invoke obj 'getattributes))

(vla-put-textstring (nth 0 att) atlength)
(vla-put-textstring (nth 1 att) atdesc)
(vla-put-textstring (nth 2 att) att1)
(vla-put-textstring (nth 3 att) att2)

[JGupte]

If I drive this from AutoCAD, then I have to open each of the 2000 AutoCAD drawing files and run the script to import the Attribute values.

If I drive it from Excel, then I only open one Excel file, and the procedure will then open and update each AutoCAD drawing file listed in sequence - or am I missing something?

 

As stated before, I don't really care what the current value of the Attribute is. I just want to change it to a known value as listed in the Excel file.

 

From what I can see, it will be a bit faster to get the values from the Excel row, then open the AC dwg, change attribute values globally (where more than 1 page) and close the sheet.

 

The alternative is to create some sort of batch file that will open each AC dwg, and the Excel file, search the excel file for document no, then import the Attribute values.

 

Again, I am more experienced using Excel than AutoCAD and LISP, so it will take me longer to go the second method (assuming the batch file will be in Lisp).

 

Thanks for the link to http://www.lee-mac.com. Unfortunately, as I don't know LISP (and have no time to learn), those routines are not much use to me.

[BIGAL]

The answer youwill be looking for is the Ctab variable for multiple layouts a blank dwg will have ctab set to either "Model" or "Layout1" you can use this to set your layout names then update that sheet with the correct attributes. See code supplied this is the same in VBA you could use VBA send "CTAB" your layout name.

[neeravnoida]

@JGupte Hi, I also have the same requirement i.e. edit the block attributes from excel. Can you please share what you did ultimately to achieve the goal

@steven-g Your program uses -attedit command where as I don't want to keep any previous record of attributes. How to use -Attin command in place of -attedit in your suggested program

 

PS: I am new in autocad vba but do have knowledge of excel vba. Please share your thoughts, Thanks in advance

[thegreatkarthik67]

@JGupte Have you found solution for your requirement?

 

It will help me also

 

Thanks

TG

[maratovich]

thegreatkarthik67

There are various solutions.
Attach your sample file, and specify what and where to change.

[thegreatkarthik67]

Thank you @maratovich

 

My requirements,

I have one drawing file named as "Sample.dwg" and one excel database named as "sample_value.xlsx"

This sample drawing has 1 block contains 3 attributes.

 

In excel, column A indicates list of drawings

                Row 1 has attribute headings (ex. I gave 3 attributes and in actual "n" of attributes)

 

1. Copy this sample drawing and save as one by one as per listed in Column "A" in excel

    For example Sample1, sample2, sample3.... Sample10.dwg (actually "n" no. of drawings)

2. Attribute value shall be changed as per excel in each drawings as per excel.

 

Can it be done?

 

Thanks

TG

Sample_Files.zip

Edited February 14, 2019 by thegreatkarthik67

[maratovich]

Try using this: