Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]

Groups > microsoft.public.excel.programming > #108880

Modify Code to loop back through table after its been updated and process it

Show all headers | View raw

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




-- 
TimLeonard

Back to microsoft.public.excel.programming | Previous | Next — Next in thread | Find similar | Unroll thread

Thread

Modify Code to loop back through table after its been updated and process it TimLeonard <TimLeonard.119a37a8@excelbanter.com> - 2016-06-05 20:21 +0100
  Re: Modify Code to loop back through table after its been updated and process it GS <gs@v.invalid> - 2016-06-05 20:06 -0400
    Re: Modify Code to loop back through table after its been updated and process it TimLeonard <TimLeonard.119b6d08@excelbanter.com> - 2016-06-06 18:27 +0100
      Re: Modify Code to loop back through table after its been updated and process it GS <gs@v.invalid> - 2016-06-06 14:42 -0400
        Re: Modify Code to loop back through table after its been updated and process it TimLeonard <TimLeonard.119bc168@excelbanter.com> - 2016-06-06 23:54 +0100
          Re: Modify Code to loop back through table after its been updated and process it GS <gs@v.invalid> - 2016-06-06 23:41 -0400
          Re: Modify Code to loop back through table after its been updated and process it GS <gs@v.invalid> - 2016-06-07 12:42 -0400
            Re: Modify Code to loop back through table after its been updated and process it TimLeonard <TimLeonard.119d5938@excelbanter.com> - 2016-06-08 05:34 +0100
              Re: Modify Code to loop back through table after its been updated and process it GS <gs@v.invalid> - 2016-06-08 03:46 -0400
              Re: Modify Code to loop back through table after its been updated and process it GS <gs@v.invalid> - 2016-06-08 13:45 -0400
                Re: Modify Code to loop back through table after its been updated and process it GS <gs@v.invalid> - 2016-06-08 13:55 -0400
                Re: Modify Code to loop back through table after its been updated and process it TimLeonard <TimLeonard.119e8e98@excelbanter.com> - 2016-06-09 03:45 +0100

csiph-web