Efficient Find & replace code (Excel VBA)

[Anonymous]

I'm using a spreadsheet to update my drawing based on the information there. Basically I'm looking for a text in Column 1, if any autocad object has this text it will be replaced by the Value in Column 2. My code works, but it runs very slow... it took 15minutes to do the job on a drawing with 3000 entities while my excel table has only 15 rows.

The "find" command in Autocad works faster, but the problem it shows a dialog box asking for amnual entry when I call it.

This is the part of my code that update the block and Mtexts... Any tip on how to improve it?

On Error Resume Next
For Each oBlock In acaddoc.Blocks
    For i = 0 To oBlock.Count
        If oBlock.Item(i).objectname = "AcDbBlockReference" Then        
            If (oBlock.Item(i).HasAttributes) Then
                attr = oBlock.Item(i).getattributes()
                For ii = 0 To UBound(attr)
                    For j = rwS To rwE           'Sets the Search Range, rwS-> first row in the spreadsheet, rwE-> last row
                        If InStr(1, attr(ii).TextString, Cells(j, col).Text, 1) Then
                            attr(ii).TextString = Replace(attr(ii).TextString, Cells(j, col).Text, Cells(j, col + 1).Text, , 1)
                            Exit For
                        End If
                    Next j
                Next
            End If
        ElseIf oBlock.Item(i).objectname = "AcDbMText" Then
            For j = rwS To rwE
                If InStr(1, oBlock.Item(i).TextString, Cells(j, col).Text, 1) Then
                    oBlock.Item(i).TextString = Replace(oBlock.Item(i).TextString, Cells(j, col).Text, Cells(j, col + 1).Text, , 1)
                End If
            Next
			'... continue code
		end if
	next i
next oBlock

[norman.yuan · Accepted solution]

That is because your code that does AutoCAD thing (looping through AutoCAD objects in AutoCAD rpocess) from an external process (Excel), thus HUGE amount of communication must occur between the 2 applications. This is called "out process" automation. If the code that does the find/replace runs in AutoCAD VBA, the code run "In Process". "Out process" could be slower then "in process" tens, or hundreds time slower.

So, if the Excel code only needs to automate AutoCAD for simple work of very low work load, it might be acceptable. But if you do work that needs heavy AuotCAD work, then you'd better run the code in AutoCAD side, not from external app (Excel, in your case). I'd bet if you run similar code (where you need to read texts to be searched from Excel sheet first in the AutoCAD VBA code, which may take a few seconds) in AutoCAD, with only 3000 entities in drawing, it could be done in seconds (may be a bit longer), instead of minutes (not to mention 15 min!).

[grobnik · Accepted solution]

I agreed with @norman.yuan I'm suggesting to operate from Autocad pointing to Excel for data to substitute, as alternative you can use selection Set to filter blocks, and looping only to found blocks after selection. here below an example

Sub SelectObject()

Dim MySelection As AcadSelectionSet
Dim FilterType(1) As Integer
Dim  FilterData(1) As Variant
FilterType(0) = 0
FilterData(0) = "Insert"
FilterType(1) = 2
FilterData(1) = "MyBlock" 'Could you use a PartialName string and "*" for expand search blocks with similar names

On Error Resume Next
Set MySelection = ThisDrawing.SelectionSets("Myss")
If Err Then Set MySelection = ThisDrawing.SelectionSets.Add("Myss")
MySelection.Clear
On Error GoTo 0
        MySelection.Select acSelectionSetAll, , , FilterType, FilterData
        If MySelection.Count >= 1 Then
        ' DO THE ATTRIBUTE EXTRACTION AND CHECK HERE

MyBlock it's the name of block to search, could you use a PartialName string and "*" for expand search blocks with similar names too.

The property Count of My selection will indicate you how many blocks has been found.

The property Item(n) of MySelection will give you info about block object found, so you have to loop only inside the found items, not all drawing blocks.

Good luck, do not hesitate to contact me if you need more info.

[Anonymous]

I tested my original code from AutoCAD and there is a big difference in the speed (some seconds vs 15 minutes). However, people using this tool may have issues with an embedded macro into a drawing... I'll give a second chance to the Selection Set - I was having issue before when adding several blocks names to the FilterData(1), but maybe it was just because I was tired. Hopefully I can save some time, if not I may need to write something to trigger the ACAD-VBA macro from excel.

[grobnik]

Ok, noted,

But if others team component has some issue to run the macro inside the drawing, you can use macro loads and run from external path with a custom button on command bar, and apply to opened drawing.

Concerning blocks multiple name, and selection set, I'll investigate, because i remember that could be selected more than a single name, by using "," between block string name. I'm not sure I'll check.

Bye

[grobnik]

Hi @Anonymous 

After further investigation and test, just for your information about blocks and selection set, in case you have different blocks name, you can try to use

gpcode(0) = 2: datavalue(0) = "BlockName1,BlockName2,BlockName3"

 I tested with simple blocks and it works fine, you could have some problems if blocks are dynamic type.

read this post

https://forums.autodesk.com/t5/visual-basic-customization/select-all-blocks-using-selection-sets/m-p/7691655/thread-id/101125/highlight/true

[Anonymous]

Thanks, I was leaving an empty space after the comma, that was the reason it wasn't working fine for me.

I tested some filtering conditions in my drawing and filtering by layer and by object type ("INSERT", "*TEXT", "MLEADER") is giving me the best perfomance so far (25seconds). I'll need to create more layers to organize the drawing and add a bunch of new elements, but at the end I expect a runtime of around 5 minutes (I guess the final users can live with that)