VBA and ObjectDbx - AutoCAD to Excel Attribute Extraction Tool

[katrinanjim]

I have created a visual basic program in microsoft Excel using ObjectDbx that will currently do the following:
- Generate a list of AutoCAD dwgs in the current directory (where excel file is saved) and display the list in Excel
- Search the drawings in the list for a specified block and display the attributes of the block in Excel
- You can then change the attributes shown in Excel and send them back to the listed AutoCAD dwgs
- Search the drawings in the list and display all external references in Excel

This program works fine with the following exceptions:
Issue #1: When attributes are updated with this program, they will appear left justified until the block is refreshed (known issue)
Issue #2: The program will only find a single instance of the specified block in each drawing. This issue occurs if you have multiple Paperspace tabs with the specified block inserted in each tab.

QUESTIONS:
Has anyone out there been able to resolve Issue #2 using ObjectDbx?
or
Will I need to go through the AutoCAD interface and generate selection sets somehow?

ATTACHMENT:
I attached my Excel file to this posting for reference. It is a very useful tool given the above issues. I am sure that it could have been a little cleaner but at least I have some commenting. Feel free to post questions or comments regarding the program and I will do my best to answer them.

Notes:
- In Excel VBA, need to reference: Microsoft Scripting Runtime, AutoCAD 2008 type Library, AutoCAD/ObjectDbx Common 17.0 Type Library
- Compatibility: AutoCAD 2008, Excel 2003+

[RobertB]

Please note that I've moved this thread from the ARX forum.

For issue #2, iterate thru the Layouts collection, using the Block property of the Layout object to gain access to the layout's placed objects.

[katrinanjim]

Thank you RobertB. It worked like a champ. I was hoping I would be able to avoid working through the AutoCAD GUI. One less Issue to deal with.

[Ed Jobe]

What do you mean, "working through the gui"? The layouts collection should be available in odbx.

[RobertB]

What do you mean, "working through the gui"? The layouts collection should be available in odbx.I think that is what they are saying... thanks to my post they can avoid going thru the GUI.

[Ed Jobe]

I think that is what they are saying... thanks to my post they can avoid going thru the GUI.

I see, one of those lines that can be read two ways.

[MikeJarosz]

Wow. It's great to see someone else has discovered oDBX.

I wrote a similar program that reads drawings from a list, prints the title block info along with the issue history, looks for certain errors, creates an Excel matrix of every issue and which drawings were in each, then makes a Word doc drawing list to insert into the project specs.

What needs to be mentioned about oDBX is its speed. The above task, which can take DAYS to do by hand, on a project with 1200 sheets took 50 seconds. Yes, 50 seconds!

[Ed Jobe]

Wow. It's great to see someone else has discovered oDBX.

There's been lots of conversations in here on odbx. I've even written a cls to address some of it's limitations.

[sujit.subramaniam]

Can somebody help to itrate between the layout collection

[RobertB]

Can somebody help to itrate between the layout collectionSurely you know how to create a For Each ... Next loop?

[sujit.subramaniam]

Sorry about the delay in reply; Rob I could loop through the layouts successfully however when I use the Set attributes function nothing changes. I looked into the code and its because of the row count for the dwg name and the rowcount for the attribs are different any ideas to work around. I hope you understand what I mean. If you want I can attach the modified excel spreadsheet

[sujit.subramaniam]

I have to thank Katrina for the spreadsheet it helped me to discover the power of ObjectDBX .

[RobertB]

Sorry about the delay in reply; Rob I could loop through the layouts successfully however when I use the Set attributes function nothing changes. I looked into the code and its because of the row count for the dwg name and the rowcount for the attribs are different any ideas to work around. I hope you understand what I mean. If you want I can attach the modified excel spreadsheetI don't think posting the spreadsheet will help. You need to post your code. Please use the code tags in the advanced editor (the # button).

[cseay]

I know this thread is several months old.. but I'm trying to do the exact same thing.. except I'm doing it through stand-alone VB.NET app instead of through excel. I've been trying to use ObjectDBX, but I can't figure out how to do it. I was using selectionsets, but those don't work with odbx.

How do you iterate through the layouts collection? I know how to do a For... next loop.. that's not what I'm having trouble with. This is my first time automating AutoCAD and I'm not sure what the layouts collection, or how you access it to loop through it.

[Ed Jobe]

Any collection object can be iterated using:

For Each CollectionObjectType In Collection
'perform tasks

Next

Where CollectionObjectType is a variable of the type the collection holds and Collection is an instance of the collection being iterated.

In the case of layouts, the Layout object is a special object that acts as a wrapper for a block of the pattern *PAPER_SPACE#, where # is the number of the block. Blocks starting with an asterisk are called anonymous blocks. Layout blocks store the entities in the layout. You access them using the Layout object's Block property. The block items can be accessed using it's Items collection.

[cseay]

Ok think that I understand what you're saying... I tried this code.. and it works... sort of... but it's only returning one block when there are two in the file... any idea why?

Dim Block As AXAUTO15Lib.AcadBlock
For Each Block In V_AutoCadInterfaceObject.Blocks
If Block.Name.ToString = "START" Then
MsgBox(Block.Name.ToString)
End If
Next Block

[Ed Jobe]

You are searching the Blocks table for the definition. You need to search paperspace for AcadBlockRef objects, the block insertions.

[cseay]

Wow... that went right over my head! I'm sorry.. this is my first time ever trying to work with AutoCAD with a program other than very simple AutoLisp programming, and I still haven't quite wrapped my head around it yet.

Any chance you could give some more detail about how you search paperspace for AcadBlockRef objects?

[Ed Jobe]

In case I don't get a chance to get you some code quickly, search this forum for "acadblockref".

[cseay]

Ok.. I'll try a search and see what I come up with... I'll post back if I find something... thank-you!

[cseay]

Wow... the only thing I get when I search this forum for acadblockref is this thread... lol... gues it's time to google it.

[Ed Jobe]

Try using "BlockRef"

[cseay]

Ok... I think I'm getting a little closer... I hope... but it's not returning anything. When I step through the code, it never goes inside the loop that says:

If TypeOf objEnt is AutoCAD.AcadBlockReference Then.....

I also tried:

If TypeName(objEnt) = "IAcadBlockReference" Then...

And:

If TypeName(objEnt) = "AcDbBlockReference" Then...



Dim strBlkName As String
Dim strAttTag As String
Dim objEnt As AutoCAD.AcadEntity
Dim objBlkRef As AutoCAD.AcadBlockReference
Dim varAtts As Object
Dim objAtt As AutoCAD.AcadAttributeReference
Dim objLayout As AutoCAD.AcadLayout
Dim objBlock As AutoCAD.AcadBlock
Dim I As Long

strBlkName = "START"
strAttTag = "FILENAME"
For Each objLayout In V_AutoCad.ActiveDocument.Layouts
If UCase(objLayout.Name) <> "MODEL" Then
objBlock = objLayout.Block
For Each objEnt In objBlock
If TypeOf objEnt Is AutoCAD.AcadBlockReference Then

objBlkRef = objEnt
If objBlkRef.Name = strBlkName Then
varAtts = objBlkRef.GetAttributes
For I = 0 To UBound(varAtts)
objAtt = (varAtts(I))
If objAtt.TagString = strAttTag Then
MsgBox(objAtt.TextString)
End If
Next I
End If
End If
Next
End If
Next

[Ed Jobe]

The Block property returns a BlockRef, which is not a collection, but a Block definition. It has an Item method you use to access ents. Try this, put your cursor on the Block method of the layout object and hit F1. Then find the item method, and look at its code example.

[cseay]

I'm not doing VBA... I'm in Visual Studio... so the F1 trick doesn't work. I searched the help file, and I found the item method.. I think.. It's making a collection of the objects, and then getting out layer "0"

I'm not sure how to apply this to reading out block attributes though... I know... I'm hopeless... I really am trying to understand it though.

Am I even heading down the right path for what I'm trying to do?? I need to look through the drawing, find several blocks by name... and in those blocks I need to read stardard information (x insertion point, x scale, etc...) as well as custom information (Sheet Number, Customer, etc..). Is what I'm trying to do even the way to do that??

[Ed Jobe]

Yes, you're on the right path, although you could use the .Net lib as well. Anyway, you can find the help on the ActiveX object model in acad_dev.chm in the acad Help folder or in 2008+ there's a link in Help>Additional Resources. Here's the code sample.

Sub Example_Item()
' This example shows two uses of the Item method.
' The first uses Item with an index counter to return an item in a collection.
' The second uses Item with a string to return an item in a collection.

' Iterate thru the model space collection,
' get all the items in the collection
' and store them in an array called newObjs
Dim count As Integer
count = ThisDrawing.ModelSpace.count

ReDim newObjs(count) As AcadEntity
Dim index As Integer
For index = 0 To count - 1
Set newObjs(index) = ThisDrawing.ModelSpace.Item(index)
Next

' Get a particular item, in this case a layer, based on name "0"
Dim layerObj As AcadLayer
Set layerObj = ThisDrawing.Layers.Item("0")

End Sub

[cseay]

Ok... I've finally made some progress... to a point... When I use the code below... it works just like I want it to.. but only if I actually open the drawing in AutoCAD... so apparently something is wrong with how I'm connecting to the objectdbx... or this method just does not work with it or something... so here's my code for the objectdbx as well as my code for getting the blocks.



V_AutoCad = StartAutoCAD() 'Use the function located in PublicFunctions.vb to initialize AutoCAD

Dim V_AutoCadInterfaceObject As AxDbDocument
V_AutoCadInterfaceObject = V_AutoCad.GetInterfaceObject("ObjectDBX.AxDbDocument")

V_AutoCadInterfaceObject.Open(V_FilePathAutoCad)

' ********************************************************************
Public Function StartAutoCAD() As Object

On Error Resume Next

V_AutoCad = GetObject(, "AutoCad.Application.15")
If Err.Number <> 0 Then 'If they don't have AutoCAD open.. then open it
Err.Clear()
Shell("C:\Program Files\Acad2002\acad.exe", AppWinStyle.NormalFocus)
System.Threading.Thread.Sleep(3000)
' Create a new session of AutoCAD using late binding
V_AutoCad = GetObject(, "AutoCad.Application.15")
End If
Return V_AutoCad

End Function

' ********************************************************************
Code for getting the block info:

Dim strBlkName As String
Dim strAttTag As String
Dim objEnt As AutoCAD.AcadEntity
Dim objBlkRef As AutoCAD.AcadBlockReference
Dim varAtts As Object
Dim objAtt As AutoCAD.AcadAttributeReference
Dim I As Long

strBlkName = "START"
strAttTag = "FILENAME"
For Each objEnt In V_AutoCad.ActiveDocument.ModelSpace

If objEnt.ObjectName.ToString = "AcDbBlockReference" Then

objBlkRef = objEnt

If objBlkRef.Name = strBlkName Then
varAtts = objBlkRef.GetAttributes
For I = 0 To UBound(varAtts)
objAtt = (varAtts(I))
If objAtt.TagString = strAttTag Then
MsgBox(objAtt.TextString)
End If
Next I
End If
End If

Next
Next

[Ed Jobe]

I'm a little short on time today. Search this forum for "ObjectDbx" or "oDbx.cls". Also, your StartAutocad sub has an error. The err section should be a call to CreateObject, not GetObject.

[cseay]

Ok... I'll see what I can find searching for those.

I changed that to CreateObject, I don't think that's causing my problem since I've been running this with AutoCAD aready open, so it's not going into that section of code... or shouldn't be anyway.

Thank-you again for helping me figure this out.

[cftorres]

I have created a visual basic program in microsoft Excel using ObjectDbx that will currently do the following:
- Generate a list of AutoCAD dwgs in the current directory (where excel file is saved) and display the list in Excel
- Search the drawings in the list for a specified block and display the attributes of the block in Excel
- You can then change the attributes shown in Excel and send them back to the listed AutoCAD dwgs
- Search the drawings in the list and display all external references in Excel

This program works fine with the following exceptions:
Issue #1: When attributes are updated with this program, they will appear left justified until the block is refreshed (known issue)
Issue #2: The program will only find a single instance of the specified block in each drawing. This issue occurs if you have multiple Paperspace tabs with the specified block inserted in each tab.

QUESTIONS:
Has anyone out there been able to resolve Issue #2 using ObjectDbx?
or
Will I need to go through the AutoCAD interface and generate selection sets somehow?

ATTACHMENT:
I attached my Excel file to this posting for reference. It is a very useful tool given the above issues. I am sure that it could have been a little cleaner but at least I have some commenting. Feel free to post questions or comments regarding the program and I will do my best to answer them.

Notes:
- In Excel VBA, need to reference: Microsoft Scripting Runtime, AutoCAD 2008 type Library, AutoCAD/ObjectDbx Common 17.0 Type Library
- Compatibility: AutoCAD 2008, Excel 2003+
Hi, I use this tool a lot and it's very effecient but recently I upgraded to AutoCAD2010 and its now not working. Do you have a version of this tool to work on AutoCAD 2010 or any idea on how to tweak it to work on the 2010? much appreciated.

[Ed Jobe]

Is it just this one sub that's not working? Check the project's references for missing type libraries (Tools>References).

[cftorres]

I did these from the reference:

uncheck - MISSING: AutoCAD 2008 type Library
check - AutoCAD 2010 type Library
check - AutoCAD/ObjectDBX Common 18.0 Library

but still doest work, error "Problem Loading Application"

also, the "AutoCAD 2010 type Library" is using this file "acax18enu.tlb" and the 'AutoCAD/ObjectDBX Common 18.0 Library' is using this file 'axdb18enu.tlb' both in the autodesk shared folder. Shouldnt it be using the from the this file 'axdb.dll' in the AutoCAD 2010 root folder? Honestly, idnno anything about vba but just guessing.

Many Thanks

[colin.b.white]

Hi cftorres,

I had the same issue and I have since altered the VBA code so that it now works okay on AutoCAD 2010 using axdb18.dll (ObjectDBX Common 18.0 Library). Please let me know if this has sorted out your issue.

I also had an issue with Excel truncating leading zeros off attribute text that were in the blocks that I was referencing. The code is now modified so that it changes the format of the cell to text immediately prior to inserting the attribute text.

Best Regards,

Colin

[roby.geiger556907]

Hi

Just found this thread and tried the excel on autocad 2011 64bit and got an error. The program opens and runs on Excel 2010 but gives complile error "cannot find project or library". The first line "Function dbxOpen(Path As String, DwgNam As String) As AxDbDocument" is highlighted.

is there a way to run it with Autocad 2011?

thanks

Roby

[Ed Jobe]

Go to Tools>References and you will notice that an entry that starts with "MISSING:". Uncheck that an find the version that ships with your version of acad. The name will be similar but end with a different number.

[rgeiger]

Thanks for the help.

I set both autocad 2011 64bit and Office Excel 2010 64bit to the same ( Autocad/ObjectDBX Common 18.0 type Library)

It is actually list at first as "AXDBLib" down the list. After picking it changes its name to "Autocad/ObjectDBX Common 18.0 type Library"

this is the same in both VBA editors.

The excel program finds the drawings and ceates the list OK.

When I pick the "Get Attributes" button I get an error " Sub or function not defined" and the debug.printuger jumps to this line:

Set AcadDbx = GetInterfaceObject("ObjectDBX.AxDbDocument.18")

with the word GetInterfaceObject highlighted in blue.

The little spinng wheel cursor comes on and stays on even after resetting the code with the blue square button, it stays spinning until I exit excel.

This has me confused because I have programmed in autocad and excel prior to 2007 and that code still works with the newer type libraries.

I tried it with and without acad 2011 running, and there was no change.

Otherwise a very nice program, I would use it.

thanks again.

Roby

[katrinanjim]

This post certainly has been going for a while. I actually was trying to figure out why my utility wasn't working, did a search on Google, and got sent back to my original post....

Anyways, here is what fixed the problem with me. I downloaded the Autocad 2011 Object Enabler package from Autodesk (since they are "phasing out" vba).
http://www.autodesk.com/vba-download
or do a search on the Autodesk website for "VBA support in Autocad 2011"

Then I revised my references to point to the updated dlls (not sure this is required). I am uploading the file again since I have made some tweaks here and there. That darn hourglass glitch is hopefully gone now. Sorry for the sloppy coding. That is what you get when an engineer programs.

I am sure that I will eventually have to put the time in and convert over to ".NET". After Autocad 2012, VBA support is probably going away...bummer.

Also note, the utility will take up less space and work better in newer versions of office if you save it as a "Macro Enabled" file (ie *.xlsm).

[rgeiger]

Hi Jim,

i got it to work a few times, but only with a few tweaks:

I had to change the path for the regserv line to current path not 2004.

I added 2 mode autodesk .dll into the autodesk VBAide references

and I found by luck that it works with autocad running, but hangs trying to open the axdb.dll just from excel.

I think this a "feature" related to the 64 bit Office 2010 and Autocad 2011.

Other Apps are not allowed to access the autocad ObjectDBX directly any longer. ( I think I read this somewhere)

Maybe an instance of autocad can be started from excel code and run invisible???

I am going to have to .NET as well, Jerry winters ahs a book and has a few lectures archive at autodesk university website. Migth be the places to start from. I sure miss VBdesign web site with the LLama, back in the day I learned a lot there.

I will help you test this some more if I can, but it is late here.

thanks again

Roby

[ettore_c]

This is some thread necromancy but I wanted to comment that this Excel file is absolutely fantastic. I couldn't get either of the ones that ended in a year to work, but I tried the one posted by colin.b.white with no year at the end; thank you SO MUCH.

I can now modify thousands of drawings in MINUTES (well, my labour part takes minutes, the computer plugs on for more than minutes ... but I have a PC dedicated specifically for this task); I couldn't figure out how to do what you did after MANY hours of searching, so thank you.

[fvweert404599]

I would like to use it!
But i get error-messages in Excel 2010: compilation errors.
Can someone fix that problem? I'm not a macro expert :(

[Ed Jobe]

Take a look at post 35 above.

[ettore_c]

Anyone that use this macro have any idea why it seems to massacre memory with AutoCAD (2010, in my case)? I am running it on a folder with about 20,000 drawings in it. For the first, say, 100 drawings ... AutoCAD is in the 141MB of RAM range; climbing and dropping about 3MB for every file "opened" then "closed". Then it jumps up about 75MB at a time until it hits another plateau around 1,6GB ... then again at 2.4GB ... then again at 3.1GB ... and probably again even further on, but I don't generally like watching task manager.

It seems like the "connection" to AutoCAD needs to be severed once in a while to wipe memory. I ran a test on a folder; took 1 minute 5 seconds to acquire the blocks. I then ran the macro again but where it ran about 1,000 drawings BEFORE that folder; same files took 3 minutes 12 seconds. I figured the "AcadDBX = nothing" part disconnected from AutoCAD, but it seems only to kindof close that file.

I was thinking I could code it to re-establish the AutoCAD DBX connection every, say, 200 drawings, to clean memory. I've gone over the code extensively, and it's quite simple, but I don't see any place where anything is being left open. I am using the one without the year indicated, and I see the more recent one has a few changes, so maybe I'll compare them.

[Ed Jobe]

The first thing that comes to my mind is that xl has an object model where objects are not automatically disposed of when they go out of scope. You need to explicitly set them to Nothing in the reverse order that they were created. e.g. if you use a sheet obj to create a range, you need to set the range to nothing, then set the sheet to nothing. If you don't you will end up with multiple instances of xl running. You can look at the Processes tab of Task Manager to see how many instances of xl are running.

[ettore_c]

Hmmm, this is a possibility; I am definitely not doing them in reverse order, and they're definitely nested. I would expect that it would ALWAYS work badly, but I suppose some unique combination for certain files makes my code run in a different order. Last night I ran a long list of files and found that, when Excel stops, the 3.2GB that AutoCAD was using remained. It stayed like that, despite AutoCAD and Excel not actually running (as applications), for at least 1/2 hr. Went to sleep, woke up, AutoCAD had closed at some point.

A friend told me about some troubleshooting techniques for monitoring how much memory something is using; I will look into that.

Normally I wouldn't care, but I have a large project coming (maybe 750,000 documents) and I expect to need this macro extensively; fixes for 1% speed increase matter to me ... as would memory "leaks".

[dgorsman]

AutoCAD saves some time by loading a lot of things into memory as it goes e.g. MTEXT editor when MTEXT is edited, object enablers, materials... as the drawings are processed, more of these are found and loaded. They are only unloaded when the application is closed. Not *exactly* a memory leak, as AutoCAD is performing as designed. That design never considered a scenario when 100k+ drawings are sequentially opened in a single session.

I would suggest taking the elephant approach: break the massive stack of drawings into workable chunks, and have AutoCAD chew the chunks separately to avoid needing a digital Heimlich.

[ettore_c]

Oddly enough, I have found the "problem", though I have zero idea why it's happening. There are specific files which, when opened, basically don't close properly. I have no idea why, but it's ALWAYS these files. They don't have a ton of entities, or some common goofy block that's crashing the script, or anything. Everything goes EXACTLY as planned, but AutoCAD just never "closes" the file ... for some reason.

Initially thought it was older AutoCAD files, but they are all saved as 2010. Annoying.

Edit: Turns out, this "problem" is when drawings have an image embedded.

[Michel.Levesque.ST522691]

Hello,

This release _2011 always return only one block record (have 3 into my test drawing). Do you have another release of this useful tool? Thanks for sharing!

[Michel.Levesque.ST522691]

I've tried several times but it always sent back one block (and there's three). File have been saved as xlsm btw. Can you help me?

[MICHAEL.JONES]

Dynamic Blocks?

It's a long shot, but has anyone adapted the VBA code, from this most awesome spreadsheet, to be able to access dynamic blocks?

If not I'll continue to stumble my way through the required modifications. :mrgreen:

[MICHAEL.JONES]

I've tried several times but it always sent back one block (and there's three). File have been saved as xlsm btw. Can you help me?

Are any of the blocks "dynamic blocks"? If so, that may be your problem.

[Michel.Levesque.ST522691]

I was finally able to extract/update block attributes based on their handles. I'm trying now with text handles but I'm only able to extract to excel not the reverse.... stuck there. Can someone help on how to dig into the acadentity ?

Regards!

[RICVBA]

I may try to help you if you post your code and dwg & xls files sample toghether with a better explanation of your aim.
bye

[jjnoeldam624553]

Hi Roby, Im trying to use with Autocad 2013 and I see the same error, I can extract file numbers but not attributes. Did you solved it?

[vp.sipola751071]

This is a very handy tool. Thanks for the code.
Although my problem is that i have multiple instances of a certain block in multiple layouts but the code just returns attributes from the one that was added firstly to the layout.
Can someone help me with this?