View Single Post
  #1   Report Post  
TimLeonard TimLeonard is offline
Member
 
Posts: 46
Default Modify Code to loop back through table after its been updated and process it

I have been looking for a way to expand the following code to update some attribute block values (Tagstrings) after modifying the spreadsheet but with no success.

What I am trying to do is, after populating the Attribute Table, update the table with new values, then send the updated Attribute Table values back to the attributes. I am not sure how to pull it back in to VBA and then loop through it in order to update the attribute blocks

I really appreciate any help that can be provided. I have been searching and working at this for days.



Code:
Sub ExtractAtts_With_Filters() 
  
  Dim Xl As Excel.Application
  Dim XlSheet As Object
  Dim XlWorkbook As Object
  
  Dim RowNum As Integer
  Dim Header As Boolean
  Dim elem As AcadEntity
  Dim Array1 As Variant
  Dim count As Integer
  Dim blk As AcadBlockReference
 

    '****************************************************
    'Delete the .xls Attribute Report File
    Dim KillFile As String
    varPath = ThisDrawing.Path

    On Error GoTo Exit_XL_App
    KillFile = varPath & "\" & "Attribute.xls"
    
    'Check that file exists
    If Len(Dir$(KillFile))  0 Then
        'First remove readonly attribute, if set
        SetAttr KillFile, vbNormal
        'Then delete the file
         Kill KillFile
    End If

    
    
    ' Launch Excel and Get Attributes***************************.
     Set Xl = New Excel.Application
     Xl.Visible = True
    
     ' Create a new workbook and find the active sheet.
     Set XlWorkbook = Xl.Workbooks.Add
     Set XlSheet = Xl.ActiveSheet
     XlWorkbook.SaveAs fileName:=varPath & "\" & "Attribute.xls", FileFormat:=1
     
     RowNum = 1
     Header = False
    
     ' Iterate through model space finding
     ' all block references.
     For Each elem In ThisDrawing.ModelSpace
       With elem
         ' When a block reference has been found,
         ' check it for attributes
         If StrComp(.EntityName, "AcDbBlockReference", 1) = 0 Then
           If .HasAttributes Then
             ' Get the attributes
             Array1 = .GetAttributes
            
            
 
            ' Filter through a list of attribute tagstrings in the current block.
            ' Cycle throught the list of attributes.
            For i = LBound(Array1) To UBound(Array1)
              ' Check for the correct attribute tag.
              Dim Attrib As AcadAttributeReference
              Set Attrib = Array1(i)
              ' If the Tagstring containd "Addresses" then
              ' Copy the Tagstrings into Excel
              If Attrib.TagString = "ADDRESS" Then

 
 
                 ' Copy the Tagstrings for the
                 ' Attributes into Excel
                 For count = LBound(Array1) To UBound(Array1)
                   If Header = False Then
                     If StrComp(Array1(count).EntityName, "AcDbAttribute", 1) = 0 Then
                       XlSheet.Range("A1") = "HANDLE" 'Code below adds column names
                       XlSheet.Cells(RowNum, count + 2).value = Array1(count).TagString
        
                     End If
                   End If
        
                 Next count
        
                 RowNum = RowNum + 1
                 For count = LBound(Array1) To UBound(Array1)
                   If XlSheet.Range("A" & RowNum) = False Then
                      XlSheet.Range("A" & RowNum) = "'" & "'" & elem.Handle 'Fill the columns with block data from modelspace
                   End If
                   XlSheet.Cells(RowNum, count + 2).value = Array1(count).TextString
                 
                 Next count
        
                 Header = True
              End If
            Next i 'Next Filter Check
           
           End If
         End If
       End With
     Next elem
     
 Xl.Sheets("Sheet1").Cells.EntireColumn.AutoFit



'************************************************************
'   'HERE is where the code to pull updated table from access goes…
'    Attribs(0).TextString = "THIS IS THE NEW NEW VALUE!"
'
'    ' Get the attributes again
'    Dim newAttrib As Variant
'    newAttrib = blkEntity.GetAttributes
'
'    ' Again, display the tags and values
'    strAttributes = ""
'    For i = LBound(newAttrib) To UBound(newAttrib)
'        strAttributes = strAttributes + "  Tag: " + _
'        newAttribs(i).TagString + vbCrLf + _
'        "   Value: " + newAttribs(i).TextString
'************************************************************


        'Xl.DisplayAlerts = False
        XlWorkbook.Save
        XlWorkbook.Close True '*** uncomment to keep open
        Xl.DisplayAlerts = True
        Xl.Quit
        
        Set XlSheet = Nothing
        Set XlWorkbook = Nothing
        Set Xl = Nothing
        GoTo Exit_Sub
        

Exit_XL_App:
        
        MsgBox Err.Number & " - " & Err.Description & " Error occurred in Excel App Process"
        
        Xl.DisplayAlerts = False
        'XlBook.Save
        XlWorkbook.Close True '*** uncomment to keep open
        Xl.DisplayAlerts = True
        Xl.Quit
        
        Set XlSheet = Nothing
        Set XlWorkbook = Nothing
        Set Xl = Nothing
     
Exit_Sub:
     
End Sub