Jump to content

vba auto fill drawing register - any thoughts?


comcu

Recommended Posts

Hi,

 

Hope you can help me with any suggestions.

I want to create a vba tool that allows me to take a few values from a drawing and transfer these values to a excel sheet.

 

Basically i have a drawing received register that i have to fill out. Sometime there are not many drawings on a job most times i have cladders, architects, steel works etc and i have been told i have to keep track of the drawings i received.

 

I would say however i do this by saving them on the computer and filing them for quick and easy access and transferring all superseded drawing to the appropriate folders leaving only the current drawings marked as current.

 

I find this easier but our head guys are insisting we do this and i know it is the right thing to do but we simply don’t have time to log in the amount of drawings we receive while still performing, and its not just the drawings its all the all the other stuff that doesn’t seem important when you are pushing jobs thru and hitting targets and most importantly getting things right.

 

As i said i have full control over the drawing via my computer but no ones else does but at the end of the day in the 2 years i have worked there i have only once been asked by the management to see a drawing received register which i couldn’t produce. It did made me look bad but it seems a lot of effort for little return? Having said all that i would like to keep a drawing recieved register.

 

So if anyone could suggest an easier way of doing this it would be greatly appreciated for some guidance.

 

I my thoughts on the vba tool is the following.

 

1. Allow the user to select the four acad entities. This would be the project name, dwg no, dwg title and then revision. The entities could be mtext, text or a attribute. Depending on how the drawing has been created by others.

2. Then save the values of the four entities and naming them eg txtstr1, txtstr2, txtstr3 & txtstr4

3. Then have my dwg received register open and a specific cell selected and basically tell excel range(“a1”) is txtstr1, range(“a2”) is txtstr2 etc then make active cell next cell down and then on to next drawing?

 

I am not great with vba but from what i have learned that seems fairly straight forward and i think i can nick bits of code of other vba tools i have created.

 

So please, does this seem over complicated? Is there a simpler way of doing this. I have been meaning to set something up for my drawings as currently i type them in manually when it is time to fill in the our drawing reg. We do not use sheet sets at my company. I can see the benefits of them but we don’t produce high volumes of drawings so i would be quite happy if i could create a vba tool for this and i always have my page set ups done correctly and use publish to quickly print drawings.

 

Cheers

Col

Link to comment
Share on other sites

  • Replies 20
  • Created
  • Last Reply

Top Posters In This Topic

  • Lee Mac

    7

  • comcu

    4

  • SEANT

    3

  • BIGAL

    2

Top Posters In This Topic

If all the required elements are attributes in say a title block, then this could be achieved upon the click of one button - The information could also be extracted from the filename, if the filename contains any of the required info.

 

I could export easiest to a csv file (just a format of excel file), as these are easiest to write to - this could then be saved as xls if you so wish... The csv file could be kept in a certain location and written to every time you open a received drawing..... then maybe, you could copy paste the info into your main drawing register to be issued.

 

Just a few thoughts...

Link to comment
Share on other sites

If you did want to go with the above suggestion, I'd be more than happy to write a LISP to accommodate this.

 

But I would need certain information about the location of the data to be written. I.e. Is is stored in attributes and if so, what is the block name and attribute name.. etc etc. (might be easier to upload a sample of the block).

 

Happy to help

 

Lee

Link to comment
Share on other sites

Just create a simple excel template and copy it into each project folder. Simple to fill out.

 

Yes, simple to fill out, but also time consuming to fill out - and that is the whole point of LISP, VBA and most other programming languages, to save the user time.

 

Wouldn't you rather just open a drawing and click a button, safe in the knowledge that all required information has been written accurately; as opposed to having to open up the excel sheet every time and manually enter the data where there is now room for human error?

 

Just a thought,

 

Lee

Link to comment
Share on other sites

Hi Lee,

 

Thank you for the offer tho, it is appreciated.

 

I know a little vba so would like to stick to that plus its the only chance i get to practice.

 

The method of your suggestion I like and it would be great but the problem the drawings come from different companys so the drawings are never the same. Most of the time the title blocks are not blocks but lines and text or mtext. Thats why i was thinking or using a selection set then storing the values then having excel open on my second screen and simply moving the mouse over to excel and hitting the button in excel.

 

I am not even sure if it is even as complicated as that. I know you can work between different programs so i could actually do it all in acad. Simply select the 4 acad entities save them as text strings then automate the transfer to the active cell in excel then make the next cell active and paste values. To speed up this process i could then make acad move to the next open drawing then allow me to make new selection. I have done some stuff between excel and word, transferring info, so it must be possible with acad. Never came across code to change drawings tho? Will have to look in help.

 

With regards to my own dwg reg this is in a proper title block with attributes. So at the minute i am typing up the drawing reg but i would look to be creating a vba program that extracts the attribute values and puts them in the appropriate excel cell.

 

Thanks for you suggestion Lee.

 

Cheers,

 

Col

Link to comment
Share on other sites

No problem Col, just offering my thoughts on how I would proceed.

 

If you want to proceed with VBA there are quite a few guys on here who are more than competent with the language - I however, know almost nothing about it. :oops:

 

If you have any further questions I could help you with, just ask :)

 

Lee

Link to comment
Share on other sites

Here would be a very basic snippet to account for text as Text, Mtext, or Attribute Reference. Code to transfer to Excel still required.

 

Sub GetTextData()
Dim varPckPt As Variant
Dim obj As AcadObject
Dim varMatrix As Variant
Dim lngContext As Long
Dim strObjName As String
Dim strText As String
  On Error GoTo MissedPick
  ThisDrawing.Utility.GetSubEntity obj, varPckPt, varMatrix, lngContext, "Select some text: "
  On Error GoTo 0
  strObjName = obj.ObjectName
  Select Case strObjName
  Case "AcDbMText"
     Dim entMText As AcadMText
     Set entMText = obj
     strText = entMText.TextString
     
  Case "AcDbText"
     Dim entText As AcadText
     Set entText = obj
     strText = entText.TextString
     
  Case "AcDbAttribute"
     Dim entAtt As AcadAttributeReference
     Set entAtt = obj
     strText = entAtt.TextString
     
     Case Else
     strText = "No text selected!"
     
  End Select
  MsgBox strText
  Exit Sub
MissedPick:
MsgBox "Missed Pick!"
End Sub

Link to comment
Share on other sites

If I understand it correctly, the drawing that come in are not at all to the same standard, and so, how does one tell which of the entities in the selection set is the drawing number, project number, revision etc etc... you would have to prompt each time... which would lead to errors most likely.

Link to comment
Share on other sites

I can’t imagine how automated logic could deal with drawings from multiple sources. The only bit my sample does is allow the selection of various entity types that may hold text pertinent to the drawing registry.

 

The code would benefit from a predictable sequence and descriptive prompts to aid the transfer effort, but I’m not sure this type of process is capable of avoiding operator error.

Link to comment
Share on other sites

I suppose an alternative approach would be if the saved received drawings had filenames containing the required information.

 

But again, this could lead to error as the filename would have to be correctly formatted every time.

 

The only reason I suggest this point is that the company I used to work for saved every drawing with a filename containing not only the drawing number, but also the project number, project name, revision number and drawing title. Therefore I deemed it as a possible solution.

Link to comment
Share on other sites

Cooperation and coordination up and down the supply chain could increase efficiency in numerous areas. Modifying file naming structure to an agreed upon standard would certainly be a good start.

Link to comment
Share on other sites

Hi, everyone, i worked on this lasty night and got it to work. it basically lets you select 4 mtexts and then creates a excel sheet and saves it and then makes it visible. it then transfers the four selected fields to the avitve excel sheet then offests the active cell before transfering the next text string the current active cell. i have built into the code a section thats ends the sub if you have not selected any values. this allows the user to move into a different layout or drawing and then restart the sub. select four mtexts and then adds again to the active cell.

 

i will look to incoporate seants code so it allows you to pick either attributes, text or mtext. i thought i would have to write 3 variations of the ocde to account for the different types. the other part i want to write in is to allow the user to select the number of entities selected. the code is set up to go to the next row once four values have been entered into the excel sheet. i would like to allow the user to type in the amount into the commnad line because as peoples template can differ, some times the project no is part of the drawing no meaning the would need 3 entities to select

 

eg.

 

Project No-DWG No

Rev

Title

 

other times there is four to select

 

Project No

DWG No

Rev

Title

 

I am quite pleased with it and i know it will save me a lot of time.

 

one problem with it tho is it has a bug. it seems every second time the code runs it crashes on the part where the valus is transfered to excel. it was really late when i finished so i will look at it again later as i am sure it is quite simple. again forgive the code if it seems a bit messy i will look and see if i can simplify it and make it easier to read but at the minute it works so that is always my first aim.

 

please comment.

 

code in the next post.

 

cheers,

 

col

 

ps i agree with you seant

 

Cooperation and coordination up and down the supply chain could increase efficiency in numerous areas. Modifying file naming structure to an agreed upon standard would certainly be a good start.

 

but i am not in a postion to demand these things. We are a curtain Walling aluminium glazing company and the drawings we recieve are from architects. these guys are higher up the food chain so i just have to live with it and come up with ways of dealing with the issues.

Link to comment
Share on other sites

Public MyTxtStr(0 To 3) As String
Public Cnt As Integer
Public WorkbookOpen As Integer
Public RowCnt As Integer
Public ErrorHandler As Error
Public Excel As Excel.Application
Public ExcelSheet As Object
Public ExcelWorkbook As Object
Public CurrRange As Range




Sub DrgRecivedRegAutoComplete3()

'allows selecting text on screen
'stores the  text  value

Dim MyMTxt As AcadMText
Dim MyoEnt As AcadEntity
Dim MyObjSS As AcadSelectionSet
Dim i As Double

i = 0
'
On Error Resume Next
ThisDrawing.SelectionSets("Selecttext").Delete
On Error Resume Next
       Set MyObjSS = ThisDrawing.SelectionSets.Add("Selecttext") '' create a new selectionset
                 
           
       MyObjSS.SelectOnScreen '' let user select entities interactively
       
           

'      ThisDrawing.Utility.prompt objSS.Count & " entities selected"

          ' ThisDrawing.Utility.GetString True, vbLf & "Enter to continue Escape To Exit"

   
    
       
       MyObjSS.Highlight True
           If MyObjSS.Count = "0" Then GoTo ErrorHandler
       
       For Each MyoEnt In MyObjSS
       
   
       
        
        
          If TypeOf MyoEnt Is AcadMText Then
          
               Set MyMTxt = MyoEnt
            
                   MyTxtStr(i) = MyMTxt.TextString
                       MyObjSS.Highlight False
                         i = i + 1
                         
                        
                         
       
       
           End If
           Next
        
           Cnt = MyObjSS.Count - 1
           
Tranfer2Excel

ErrorHandler:

Close

End Sub

Private Sub Tranfer2Excel()




 If WorkbookOpen = 1 Then GoTo SkipCreatingWorkbook
 
   ' Launch Excel.
   
   
   Set Excel = New Excel.Application

   ' Create a new workbook and find the active sheet.
   Set ExcelWorkbook = Excel.Workbooks.Add
   Set ExcelSheet = Excel.ActiveSheet
   
   ExcelWorkbook.SaveAs "Drawing Register Transfer Sheet.xls"
 
Excel.Visible = True
 
RowCnt = 1
 
     With Worksheets("Sheet1")
   .Select
   .Range("a1").Activate
       End With
   
SkipCreatingWorkbook:
   
    For i = 0 To Cnt
       Set CurrRange = ActiveCell
           CurrRange.Value = MyTxtStr(i)
               CurrRange.Offset(0, 1).Select
   
   Next
   
   RowCnt = RowCnt + 1
   
    With Worksheets("Sheet1")
   .Select
   .Range("a" & RowCnt).Activate
       End With
   
  WorkbookOpen = 1
  

       
   
   DrgRecivedRegAutoComplete3


  ' Excel.Application.Quit
   'Excel.Application.

  
End Sub



Link to comment
Share on other sites

I have done this where a user picks an object you then know wether its a block or text etc in the case of text you would always pick the first answer required then next next etc it would get complicated if its a mix of blocks and text is picked.

 

The answer may lie in when you pick a block you pick at the point of an attributes value it would require a bit of coding but you could check the attribute value is near to the point chosen. I am sure there is a value for attribute insertion point. repeat as required but the user picks in the required order.

 

As I have said we do this now updating a block picking values off drainage long sections if the user picks the wrong ones then yes the answers will be wrong.

Link to comment
Share on other sites

  • 1 year later...
If all the required elements are attributes in say a title block, then this could be achieved upon the click of one button - The information could also be extracted from the filename, if the filename contains any of the required info.

 

I could export easiest to a csv file (just a format of excel file), as these are easiest to write to - this could then be saved as xls if you so wish... The csv file could be kept in a certain location and written to every time you open a received drawing..... then maybe, you could copy paste the info into your main drawing register to be issued.

 

Just a few thoughts...

 

Hi Lee,

I was surfing the NET looking for inspiration and found the HOLLY GRAIL......

Is this at all possible? to transfer information from AutoCAD to an EXCEL Drawing Register using Attributes??

Any Assistance Would be Greatly Appteciated:shock:

Best Regards

Simon

Link to comment
Share on other sites

If all the required elements are attributes in say a title block, then this could be achieved upon the click of one button - The information could also be extracted from the filename, if the filename contains any of the required info.

 

I could export easiest to a csv file (just a format of excel file), as these are easiest to write to - this could then be saved as xls if you so wish... The csv file could be kept in a certain location and written to every time you open a received drawing..... then maybe, you could copy paste the info into your main drawing register to be issued.

 

Just a few thoughts...

 

Hi Lee,

 

Ihave tried to send a message twice giving a detailed description of my situation, unfortunately every time i hit the 'submit reply' button it takes me back to the login screen:oops:

 

To cut a long story short......

 

I was surfing the NET looking for inspiration and found the HOLLY GRAIL......

 

Is this at all possible? to transfer information from AutoCAD to an EXCEL Drawing Register using Attributes??

 

Any Assistance Would be Greatly Appteciated:shock:

 

Best Regards

 

Simon

Link to comment
Share on other sites

I am sure Lee will be along soon but you could also look at the data extraction tool already in AutoCAD. (Tools|Data Extraction)

Link to comment
Share on other sites

Simon, check out the Global Attribute Extractor in my sig - this thread is pretty old, but it looks like I accomplished it in the end :D

Link to comment
Share on other sites

  • 4 years later...
Simon, check out the Global Attribute Extractor in my sig - this thread is pretty old, but it looks like I accomplished it in the end :D

 

Simon, check out the Global Attribute Extractor in my sig - this thread is pretty old, but it looks like I accomplished it in the end :D

 

Hi LM

first a big thanks for your global efforts in the fight against Badcad.

 

I didn't want to start a new thread and found this one which I hope can get my question answered.

 

I am starting a new Project which will have over 100 drawings, all with the same title block containing attributed text. I have created an excel table with column headings representing each Attribute, and wish to have one row per drawing, thus creating a title-block table in Excel.

I wish to be able to fill in text for These attributes inside this table rather than have to open up individual drawings.

 

How can I create the link to make these title block attributes read Information from this table?

Kind of like your Global Attribute Extractor, but in reverse!

 

thanks in advance for any insight anyone can give me

Link to comment
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.
Note: Your post will require moderator approval before it will be visible.

Guest
Unfortunately, your content contains terms that we do not allow. Please edit your content to remove the highlighted words below.
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.


×
×
  • Create New...