Author Topic: Modify text from EXCEL based on handle  (Read 4112 times)

0 Members and 1 Guest are viewing this topic.

Vince29

  • Guest
Modify text from EXCEL based on handle
« on: October 02, 2015, 05:29:49 AM »
Hello!

I am sorry but I am definitely not an expert on VBA. Don't blame me on this.

However I think I have the perfect application for a macro and try to develop it...

I have an ACAD drawing with 1200 independant text items that represent tags. I need to update these textstrings based on different values present in an excel table.

What I have managed to do is to export both the textstrings and the handles for each in an excel table.

So my excel table is as follows:

Column A: textstring.handle
Column B: ACAD original tag
Column D: Value of tag to be updated in ACAD

So, my whole code is the following:

Code: [Select]
Sub EXPORTER()

Dim RowIndex As Integer: RowIndex = 1
Dim tAcadApp As AcadApplication
Dim tAcadDoc As AcadDocument
Dim tTextObj As AcadText

On Error Resume Next

Set tAcadApp = GetObject(, AcadProgID)
If tAcadApp Is Nothing Then
Call showMsg("Please start AutoCAD, open your drawing and make sure there is no command active", True)
Else

'well, Acad seems to be available for COM-Reqests, see if a document is current/active
If tAcadApp.ActiveDocument Is Nothing Then
Call showMsg("Please open your drawing and make sure it's the active document", True)
Else

'ok, a drawing is active
Set tAcadDoc = tAcadApp.ActiveDocument
Dim tSelSet As AcadSelectionSet

'filter definition for selection
Dim tDxfCodes(1) As Integer
Dim tDxfValues(1) As Variant
tDxfCodes(0) = 0: tDxfValues(0) = SelectionObjectTypeName 'that's to get only objects of type "TEXT"
tDxfCodes(1) = 410: tDxfValues(1) = SelectionSpace

'create the selection
Set tSelSet = tAcadDoc.SelectionSets.Item("myTempSelSet")
If tSelSet Is Nothing Then

'then this selectionset didn't exist yet, so create a new one
Set tSelSet = tAcadDoc.SelectionSets.Add("myTempSelSet")
End If

tSelSet.Clear
'now run the selection
Err.Clear

tSelSet.Select acSelectionSetAll, , , tDxfCodes, tDxfValues
If Err.Number <> 0 Then
Call showMsg("Some error appeared while trying to select objects" & vbNewLine & Err.Description & vbNewLine & "Function cancelled", True)
Else
If tSelSet.Count = 0 Then
Call showMsg("No objects of type TEXT found", False)
Else
'ok, we have objects, so let us now append them to the current workbook
'I assume here, that Excel and the Workbook is ready and empty (or can be overwritten
'this routine starts in the ActiveSheet ==> A:1

For Each tTextObj In tSelSet

If tTextObj.Handle = Excel.ActiveSheet.Cells(RowIndex, 1) Then
tTextObj.TextString = Excel.ActiveSheet.Cells(RowIndex, 4)
Next

End If

End Sub

I am sure that where I am wrong is that it does not loop through the whole range of value of colum A to find its handle and hence retrieve its required updated tag:

Code: [Select]
For Each tTextObj In tSelSet

If tTextObj.Handle = Excel.ActiveSheet.Cells(RowIndex, 1) Then
tTextObj.TextString = Excel.ActiveSheet.Cells(RowIndex, 2)
Next

End If

Please, if you can guide me on how to do this, I would be very very grateful... Thanks, Vince

RICVBA

  • Newt
  • Posts: 62
Re: Modify text from EXCEL based on handle
« Reply #1 on: October 02, 2015, 10:33:42 AM »
you could use something like
Code: [Select]
               
                With Excel.ActiveSheet
                    For Each tTextObj In tSelSet
                        RowCount = .Range("A" & .Rows.Count).End(xlUp).row
                        For RowIndex = 1 To RowCount
                            If tTextObj.Handle = .Cells(RowIndex, 1) Then
                                tTextObj.textString = .Cells(RowIndex, 4)
                                Exit For
                            End If
                        Next RowIndex
                    Next tTextObj
                End With

BTW:
1) your whole code must be something more than what you posted, since there's missing:
- "AcadProgID", "SelectionObjectTypeName" and "SelectionSpace" initializatione values
   also, for more readable code I'd add the "Excel" variable declaration and setting.. or you may just use "Application" if you're running this macro inside Excel

- some "showMsg" sub

2) that "On Error Resume Next" statement at the beginning of EXPORTER sub is quite unsafe and may also be an obstacle for your debugging issues
you should use just where you need it - mainly just before statements that may result in some error, to immediatley handle them and place a "On Error goto 0" statements right after

Vince29

  • Guest
Re: Modify text from EXCEL based on handle
« Reply #2 on: October 02, 2015, 03:28:02 PM »
Hello Ricvba, I won't be able to text it before monday at the office but I won't miss to keep you informed. In all cases, thank you! Have a nice week-end

Vince29

  • Guest
Re: Modify text from EXCEL based on handle
« Reply #3 on: October 05, 2015, 06:30:37 AM »
Ricvba,

Works like a charm. I am really grateful!

RICVBA

  • Newt
  • Posts: 62
Re: Modify text from EXCEL based on handle
« Reply #4 on: October 05, 2015, 03:31:34 PM »
glad it helped
bye