vba auto fill drawing register - any thoughts?

[Lee Mac]

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.

[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.

[comcu]

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.

[comcu]

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

[BIGAL]

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.

[SimonC]

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

Best Regards

Simon

[SimonC]

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

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

Best Regards

Simon

[dbroada]

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

[Lee Mac]

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