Author Topic: Using AutoCad's ObjectDBX from Excel  (Read 12607 times)

0 Members and 1 Guest are viewing this topic.

Jeff_M

  • King Gator
  • Posts: 3945
  • C3D user & customizer
Re: Using AutoCad's ObjectDBX from Excel
« Reply #15 on: April 04, 2006, 03:31:38 PM »
One other thing, if your block is inserted only once per layout then exit out of the For..Next loop once it's found.......

Viktor

  • Guest
Re: Using AutoCad's ObjectDBX from Excel
« Reply #16 on: April 04, 2006, 03:49:16 PM »
well, you are right Jeff, it is allot quicker than opening each file and cycling through layouts. And if this is how it should be I'm more than glad with it, it's much cleaner and user friendly than the method i had before.
And I won't beg you to share your routine, :) u certaintly deserve to have something unique :-)

Thanks again, I will post the final routine here if you think it's worth it, it works good for a spreadsheet that you have already designed and you just need to populate certain cells with certain attributes.

u wouldn't happen to know how to add a progress bar in vba, now would u? i can look it up too, but you're more than welcome to share :)

Thanks again,
Viktor.

Jeff_M

  • King Gator
  • Posts: 3945
  • C3D user & customizer
Re: Using AutoCad's ObjectDBX from Excel
« Reply #17 on: April 05, 2006, 08:14:05 PM »
You're welcome, Viktor. I'm glad you got something working.

Please do share your final routine. It may help others or we may be able to show you how to cut down a bit on the processing time.

I've never used a progress bar in VBA. But THIS looks like it would be simple to implement.

Jeff

Viktor

  • Guest
Re: Using AutoCad's ObjectDBX from Excel
« Reply #18 on: April 06, 2006, 02:12:47 AM »
Sure thing, i will post it tomorrow, i've been swapped at work again so no time to play with it much. One thing i did notice, is that you don't have to have autocad running for obdx to work, but you only have to open it once and then you can close it and it will still work until you reboot the computer. This may only be the deal with network licenses, but that's the story with me. kind of freeked me out today.

BTW, i was thinking of launching autocad with shell and then closing it back when done (that is if it's not found).

And, i am having trouble with error proofing it, i'm a bit lost why my error checks are not working.
I'll post it tomorrow.
Thanks again.
Viktor.

Viktor

  • Guest
Re: Using AutoCad's ObjectDBX from Excel
« Reply #19 on: April 06, 2006, 03:13:08 PM »
Well, here's the code, its not as smooth as i want it to be, maybe over the weekend i will have some time to mess around with it.
Code: [Select]
Public acad As Object
Public odbx As Object
Public mspace As Object
Public excel As Object
Public AcadRunning As Integer
Public excelSheet As Object
Sub Extract()
    ActiveSheet.Unprotect
    Dim sheet As Object
    Dim shapes As Object
    Dim excel As Object
    Dim excelSheet As Object
    Dim RowNum As Integer
    Dim Array1 As Variant
    Dim i As Integer
    Dim ent As AcadEntity
    Dim Layouts As AcadLayouts
    Dim Layout As AcadLayout
    Dim blkref As AcadBlockReference
    Dim filetoopen As Variant
    Dim tag5 As String
       
    'Prepare Excel
   
    Set excelSheet = ActiveWorkbook.Sheets("transmittal")
    Range("prow1").Select
    Do
    If IsEmpty(ActiveCell) = False Then
    ActiveCell.Offset(1, 0).Select
    End If
    Loop Until IsEmpty(ActiveCell) = True
    ActiveCell.Offset(-1, 0).Select

'getfilenames
filetoopen = Application.GetOpenFilename("Drawing Files (*.dwg), *.dwg", , "Select Drawings", "Get Attributes", True)



RowNum = ActiveCell.Row
On Error Resume Next
Set acad = GetObject(, "AutoCAD.Application")
If Err <> 0 Then
Set acad = CreateObject("AutoCAD.Application")
End If
On Error GoTo 0
Set odbx = acad.GetInterfaceObject("ObjectDBX.AxDbDocument.16")


For Each Filename In filetoopen



odbx.Open Filename

   
       
   
   
    'Work in AutoCad
   
    Set Layouts = odbx.Layouts
   

For Each Layout In Layouts
If Layout.Name <> "Model" Then
    For Each ent In Layout.Block
        If ent.ObjectName = "AcDbBlockReference" Then
            Set blkref = ent
                If blkref.Name = "CORP-D" Then
                    Array1 = blkref.GetAttributes
                   
                    RowNum = RowNum + 1
                    For i = LBound(Array1) To UBound(Array1)
                        If Array1(i).TagString = "7" Then

                        excelSheet.Cells(RowNum, 2).Value = Array1(i).TextString
                        End If
                       
                        If Array1(i).TagString = "8" Then

                        excelSheet.Cells(RowNum, 4).Value = "Rev. " & Array1(i).TextString
                        End If
                       
                        If Array1(i).TagString = "5" Then
                        tag5 = Array1(i).TextString

                        'excelSheet.Cells(RowNum, 2).Value = Array1(i).TextString
                        End If
                       
                        If Array1(i).TagString = "6" Then

                        excelSheet.Cells(RowNum, 5).Value = tag5 & " " & Array1(i).TextString
                        End If
                       
                                             
                        If Array1(i).TagString = "1" Then

                        excelSheet.Cells(RowNum, 8).Value = Array1(i).TextString
                        End If
                       
                    Next i
                End If 'blkref
              End If 'ent
           Next ent 'each ent
         End If 'layout
        Next Layout
        Next Filename
    Set acad = Nothing
    Set odbx = Nothing
   
    ActiveSheet.Protect DrawingObjects:=False, Contents:=True, Scenarios:= _
        False, AllowFormattingCells:=True, AllowFormattingColumns:=True, _
        AllowFormattingRows:=True, AllowInsertingColumns:=True, AllowInsertingRows _
        :=True, AllowInsertingHyperlinks:=True, AllowDeletingColumns:=True, _
        AllowDeletingRows:=True, AllowSorting:=True, AllowFiltering:=True, _
        AllowUsingPivotTables:=True
   
   
   
End Sub



Thanks allot to Jeff, great tips and advice!

Viktor.

Jeff_M

  • King Gator
  • Posts: 3945
  • C3D user & customizer
Re: Using AutoCad's ObjectDBX from Excel
« Reply #20 on: April 06, 2006, 03:58:48 PM »
One thing I see that MAY speed things up a tad.......you have a number of
"If Array1(i)= something Then whatever" checks......this can be made a bit more streamlined in 2 ways, the second one being the better of the bunch.
Code: [Select]
If Array1(i) = whatever Then
  do something
ElseIf Array1(i) = somethingelse Then
  do somethingelseentirely
ElseIf Array1(i) = thisotherthing Then
  let's do lunch
End If
Then there's the preferred method...
Code: [Select]
Select Case Array1(i)
  Case Is = something
    do something
  Case Is = somethingelse
    do somethingelse
  Case Is = "Lunch"
    do lunch
  Case Else
    do something you hadn't planned on
End Select

And I'll repeat one thing from a few posts ago in case you missed it. If you expect to find only 1 block named "CORP-D" per Layout then Exit the For..Each loop once it is processed. Most PS layouts I use don't have that many entities in them and for me it probably wouldn't speed it up too much, but if you have a lot of things in PS it will probably be noticable.

Viktor

  • Guest
Re: Using AutoCad's ObjectDBX from Excel
« Reply #21 on: April 06, 2006, 05:25:58 PM »
that works nicely, very good tip (doing the select case rather than if).

One more q,
what if i have the file open? i get the error, is obdx not able to open something if it's already open?
Can i open drawings partially?

And about ending the FOR after a object is found, i can't figure out how to do that, if i end it then i get a BLOCK IF error, and i didn't have time to look into it that much. how does ending FOR work?

Jeff_M

  • King Gator
  • Posts: 3945
  • C3D user & customizer
Re: Using AutoCad's ObjectDBX from Excel
« Reply #22 on: April 06, 2006, 07:31:51 PM »
Correct, you found an error that needs to be trapped. You cannot open a dwg with ODBX that is open in the CURRENT Acad session....i.e. the one that was used to get the ODBX object. Although I usually will just trap for any errors on opening, since it will also error if you try to open a dwg saved in a newer release.

No to the partial open.

To exit out of a For...Next or For..Each loop (extremely simplified!):
Code: [Select]
For Each ent In Block
   If ent.Name = "MyBlock" Then
      'do whatever with "MyBlock"
      Exit For 'we don't need to process this block any further than this
   End If
Next

Viktor

  • Guest
Re: Using AutoCad's ObjectDBX from Excel
« Reply #23 on: April 07, 2006, 03:09:27 PM »
Thanks for the For exit.

So, what if someone else has that file open, it is in read only state, will that cause an error as well?
by trapping what do you mean? sorry errors are really my biggest weakness, do you mean to let go to 0 or exit out or stop?
Is there a way to record all the errors, for example, if drawing 111112.123 is open, have it record that number then continue unto the next one, and at the end display a msgbox with drawings that need to be fixed?

Thanks again.
Viktor.

Viktor

  • Guest
Re: Using AutoCad's ObjectDBX from Excel
« Reply #24 on: April 07, 2006, 03:27:27 PM »
Ok, i still can't get it to exit the FOR... What's killing me here? I'm getting end if without block bla bla error

Code: [Select]
For Each Layout In Layouts
If Layout.Name <> "Model" Then
    For Each ent In Layout.Block
        If ent.ObjectName = "AcDbBlockReference" Then
            Set blkref = ent
            If UCase(blkref.Name) = "CORP-D" Then
                   Array1 = blkref.GetAttributes
                        RowNum = RowNum + 1
                        For i = LBound(Array1) To UBound(Array1)
                        Select Case Array1(i).TagString
                        Case Is = "7"
                        excelSheet.Cells(RowNum, 2).Value = Array1(i).TextString
                        Case Is = "8"
                        excelSheet.Cells(RowNum, 4).Value = "Rev. " & Array1(i).TextString
                        Case Is = "5"
                        tag5 = Array1(i).TextString
                        Case Is = "6"
                        excelSheet.Cells(RowNum, 5).Value = tag5 & " " & Array1(i).TextString
                        Case Is = "1"
                        excelSheet.Cells(RowNum, 8).Value = Array1(i).TextString
                        End Select
                       
                    Next i
                  Exit For                   '<-----------SO WE EXIT IT HERE?
                End If         ' if name  '<-----------Do we still need to end the if within the FOR?
              End If           ' if block
            Next Layout
        Next Filename

Thanks Jeff, sorry to be a pain, it works now, people are using it, i'm just trying to make it a bit more bulletproof.
Thanks again.
Viktor.

Jeff_M

  • King Gator
  • Posts: 3945
  • C3D user & customizer
Re: Using AutoCad's ObjectDBX from Excel
« Reply #25 on: April 07, 2006, 04:44:30 PM »
Thanks for the For exit. You're welcome

So, what if someone else has that file open, it is in read only state, will that cause an error as well? It shouldn't, unless you try to save it.
by trapping what do you mean? sorry errors are really my biggest weakness, do you mean to let go to 0 or exit out or stop? The simplest form of an error trap (or handler) is "On Error Resum Next", but if not used correctly can wreak havoc trying to debug problems. Used correctly, though, it can be very easy to use...see below
Is there a way to record all the errors, for example, if drawing 111112.123 is open, have it record that number then continue unto the next one, and at the end display a msgbox with drawings that need to be fixed? Sure....again, see below

Thanks again.
Viktor.
Code: [Select]
For Each dwg In DwgList
    On Error Resume Next
    ODBX.Open dwg
    If Err.Number <> 0 Then
        ThisDrawing.Utility.Prompt dwg & " could not be worked with."
        Err.Clear
        Resume Resume_Here
    Else
        On Error GoTo 0 'or, preferably, have an error handler for things that may go wrong here.
        'Do your thing with the drawing
    End If
Resume_Here:
Next dwg
Ideally though, you should know what error may occur and trap for those specific errors and have your code act accordingly.

In response to the message you posted while I was typing this......you are missing the Next ent

Viktor

  • Guest
Re: Using AutoCad's ObjectDBX from Excel
« Reply #26 on: April 10, 2006, 03:32:21 PM »
Jeff, thank u! i'm learning as I go... That makes sense :)
I am getting a error if someon has the drawing open though, and i'm just doing the obdx.open and then set odbx = 0
What is my problem? or should I not use obdx.open?

Jeff_M

  • King Gator
  • Posts: 3945
  • C3D user & customizer
Re: Using AutoCad's ObjectDBX from Excel
« Reply #27 on: April 12, 2006, 07:51:24 PM »
Hi Viktor, just a note to let you know that I'm not ignoring this :-)

I'm currently in upstate New York and won't return home to Calif. until Apr. 17, so my checking in here will be minimal.

If you are getting an error when a drawing's open, just catch the error, report it, and move on.......

Bryco

  • Water Moccasin
  • Posts: 1849
Re: Using AutoCad's ObjectDBX from Excel
« Reply #28 on: April 12, 2006, 11:06:50 PM »
I find this error interesting as I haven't been very happy with gathering information from a group of drawings, especially when one drawing is crashed.
I havent been able to use the dbx saveas to help. But( with limited testing) I did manage to copy the file, open it and delete it.

Code: [Select]
Sub opdbx()
    On Error GoTo Err_Control

    Dim sDwg As String
    Dim sSaveas As String
    Dim dbxDoc As AxDbDocument
    Dim Atts As Variant, att As Variant
    Dim Fs, NewFile, F  'As File,
    Dim CopyName As String
    Dim MakeCopy As Boolean
   
   
    sDwg = "X:\PutsomethingValidInHere.DWG"
    Debug.Print sDwg

    Set dbxDoc = New AxDbDocument
  'Pick your version
 
        Set dbxDoc = GetInterfaceObject("ObjectDBX.AxDbDocument.16")
 
    dbxDoc.Open sDwg
   
   
    If MakeCopy Then
        Set Fs = CreateObject("Scripting.FileSystemObject")
        Set F = Fs.GetFile(sDwg)
        CopyName = F.ParentFolder & "\Copy" & F.Name
        Debug.Print F.Path, CopyName
        F.Copy CopyName, True
        Set NewFile = Fs.GetFile(CopyName)
        dbxDoc.Open CopyName
     End If
   
    Debug.Print dbxDoc.LAYERS.Count
    If MakeCopy Then
        Set dbxDoc = Nothing
        NewFile.Delete
        MakeCopy = False
    End If



Exit_Here:
    Exit Sub
Err_Control:
    Select Case Err.Number
        Case -2147467259   'Method 'Open' of object 'IAxDbDocument' failed
            MakeCopy = True
            Resume Next
        Case Else
        Debug.Print Err.Number, Err.Description
        MsgBox Err.Description
        Err.Clear
        Resume Exit_Here
    End Select
End Sub


Viktor

  • Guest
Re: Using AutoCad's ObjectDBX from Excel
« Reply #29 on: April 18, 2006, 08:19:54 PM »
It's alright Jeff,
This error is making my head scratch. I don't quiet get it. I haven't had any time to work on it recently, but after work i will look at it, test it and get bad results. I just don't get it.
Bryco, thanks for your reply. I will try to work with it, that's an idea to use the case for that particular error.
Thanks again.
Viktor.