Page 1 of 2 12 LastLast
Results 1 to 10 of 11

Thread: Problem vba code

  1. #1
    Member
    Join Date
    2014-05
    Posts
    14
    Login to Give a bone
    0

    Default Problem vba code

    Hello everyone
    I need your help to fix this code I'm trying to adapt to my needs . Let me explain what happens
    I open autocad and insert attributes from another excel sheet with a sequence selected from a table , such as :
    1st Ftc_NO
    2nd Ftc_NC
    3rd Ftc_NO
    4th Cont_NO
    5th Cont_NC
    6th Lamp_Green
    7th Lamp_ Blue
    8th Ftc_NO
    In Excel , the spreadsheet " Attributes " with the button "Extract" extract attributes .
    The problems are :
    1 extracts attributes not with the input sequence , but in reverse order
    2 the names of the blocks , and the handle are positioned in cells exact , but the rest of the attributes are scattered
    3 if I extract the attributes , the first time it works , but the second is not working and nothing appears.
    Every time I do a test run I must eliminate the CAD drawing and with the new one works


    In the first image the sequence of insertionIn the first image the sequence of insertion


    In the second extraction errors


    I hope for your help to fix the code

    https://dl.dropboxusercontent.com/u/...xtAttr_v2.xlsm
    Attached Images Attached Images

  2. #2
    Active Member
    Join Date
    2012-11
    Location
    Italy
    Posts
    65
    Login to Give a bone
    0

    Default Re: Problem vba code

    Here's a list of what it occured to me:

    1) I can't grasp the logic of dwg opening statements block
    Code:
    Dim Dwg As AcadDocument
    For Each Dwg In AcadApp.Documents
        If StrComp(Dwg.FullName, Cells(1, 1).Text, vbTextCompare) = 0 Then
            Dwg.Activate
            Opened = True
        End If
    Next
    
    If Not Opened Then
        AcadApp.Documents.Open (Cells(1, 1).Text)
    End If
    since after
    Code:
    Range("1:65536").ClearContents
    , and
    Code:
    excelSheet.Range(Cells(1, 1), Cells(40, 11)).Clear
    as well, you end up with empty Cell(1,1). so that
    Code:
    If StrComp(Dwg.FullName, Cells(1, 1).Text, vbTextCompare) = 0 Then
    test will always fail unless you have the default empty "drawing1.dwg".
    should you not have the default empty drawing opened, then
    Code:
    AcadApp.Documents.Open (Cells(1, 1).Text)
    would execute, leading to an error
    so it seems your sub can only deal with the default "Drawing1.dwg" already open in your current Autocad session!

    2) I'd use
    Code:
    Cells(1, 1).Value
    instead of
    Code:
    Cells(1, 1).Text
    ". they'd actually work the same if the cell has no number formatting, which should be your most probable case. otherwise "Text" property would return a formatted string (lets' say "dwg: mydwg.dwg" where the actual cell content is "mydwg.dwg" but with ""dwg:" @" cell format) while "Value" property would always return the "naked" value you input.

    3)
    Code:
    For I = 0 To SelSet.Count + 1
    should lead to an error since selectionsets are zero-based (for instance should a selectionset have two elements they would have index 0 and 1)

    4) the selectionset handling is not safe, since it'd work only with drawings with no selectionset named "SELSET" already
    one way of handling it is adding a
    Code:
    SelSet.Delete
    at the very end of your sub just before ending it

    5) are all
    Code:
    Cells(3, Column)
    references correct? they oddly point at the third row of your table

    6) inside
    Code:
    While Not IsEmpty(Cells(3, Column))
    loop, you keep looping through columns even after finding the one whose heading matches the current attirbute tagstring. but is it possible or useful having two attributes sharing the same tagstring?

    7)
    Code:
    Set Acad = Nothing
    dosn't match you previuos
    Code:
    Set AcadApp = ...
    setting

    I think points 4) and 5) are the most peculiar for your specific issues

  3. #3
    Member
    Join Date
    2014-05
    Posts
    14
    Login to Give a bone
    0

    Default Re: Problem vba code

    grazie RicVba
    Your help is always very welcome
    I followed your advice
    now the program vba examines the active sheet of autocad
    I'm sorry but I do not understand
    I can not give the hot spots on the sheet , and probably that's why records them on the contrary , is not it ?
    sorry I do not know how to manage entries
    grazie ciao

  4. #4
    Member
    Join Date
    2014-05
    Posts
    14
    Login to Give a bone
    0

    Default Re: Problem vba code

    ciao ricvba
    Your help is always very welcome
    I followed your advice
    now the program vba examines the active sheet of autocad
    but I apologize
    I do not know how to handle the attributes ( then are all the same for each block ) and placing , why read them on the contrary
    che palle la telecom, ...................scusa

  5. #5
    Active Member
    Join Date
    2012-11
    Location
    Italy
    Posts
    65
    Login to Give a bone
    0

    Default Re: Problem vba code

    Quote Originally Posted by chiab_gigi View Post
    ciao ricvba
    Your help is always very welcome
    I followed your advice
    now the program vba examines the active sheet of autocad
    but I apologize
    I do not know how to handle the attributes ( then are all the same for each block ) and placing , why read them on the contrary
    che palle la telecom, ...................scusa
    Quote Originally Posted by chiab_gigi View Post
    grazie RicVba
    Your help is always very welcome
    I followed your advice
    now the program vba examines the active sheet of autocad
    I'm sorry but I do not understand
    I can not give the hot spots on the sheet , and probably that's why records them on the contrary , is not it ?
    sorry I do not know how to manage entries
    grazie ciao
    ciao chiab_gigi
    What do you not understand, exactly?
    What do you mean with "I can not give hot spots on the sheet"?
    What are you need for succeeding in handling attributes? the code you posted was quite well done for this specific task.

    It would help us if you post your last code and a dwg sample, showing what you're starting from and making it clear what your goal should be like and what debugging problems you meet.

    bytheway I see I'm not alone with my poor english! but we're in a international forum and must stick to it.
    so please try and explain your doubts again as best as you can.

    maybe you could write them in english and in italian, and I could answer the same way. but before doing that I think we must wait for this forum moderator's approval.

    Finally, let me explain what I got about this forum, since I feel it could help you here (and in many other forums I know, too).
    It's an excellent source of knowledge sharing, and it really helps. But it mainly works in that way where you are asked to make and show your efforts in your trial and error work and other people helping you at it.
    So that it hardly happens someone makes your own homework with you just looking at him!

  6. #6
    Member
    Join Date
    2014-05
    Posts
    14
    Login to Give a bone
    0

    Default Re: Problem vba code

    thanks to the explanations of RicVba I modified the code and definitely is almost perfect
    I'm working on the management of the attributes on the sheet
    I will stop here , on what you have pointed out
    Cells(3, Column)
    I have to solve the positioning of the attributes and the sequence of extraction
    I'm working on
    Last edited by chiab_gigi; 2015-02-01 at 09:21 AM.

  7. #7
    Active Member
    Join Date
    2012-11
    Location
    Italy
    Posts
    65
    Login to Give a bone
    0

    Default Re: Problem vba code

    thank you for the files you posted
    as for the dwg one I need an Autocad 2010 version to open it.

    but reviewing the xlsm one I can go on trying and help you out
    and to this end I think reviewing those 5 points out of post#2 (and adding some new ones...) could be a good leading way for me

    1) Autocad application and active document handling
    you added
    Code:
    Public acad As AcadApplication
    at the very top of your module before any subs beginning
    but then in your ExtraireAttributs() sub you also
    - declare and use "AcadApp" throughout the entire sub with the same purpose
    - use "acad" in
    Code:
    Set acad = Nothing
    only; so it's useless
    just choose what variable you use and get rid of the other
    As a personal choice of mine I try not using "Public" variables but module scope ones only and pass them to subs and functions as parameters

    in
    Code:
        On Error Resume Next
        Set AcadApp = GetObject(, "AutoCAD.Application")
        If AcadApp Is Nothing Then
           Set AcadDoc = AcadApp.ActiveDocument
         
            AcadApp.Visible = True
        End If
    there's a logic flaw, since if "AcadApp" has not been set (as an application object), then any attempt to use it (like
    Code:
    Set AcadDoc = AcadApp.ActiveDocument
    ) would lead to an error
    most probably you're not experiencing any error yet since you always run your sub with AutoCAD running already. and besides you never use "AcadDoc" variable, also (you use "AcadApp.ActiveDocument" instead, to get the active document)
    but this way you're not handling a quite probable condition of the user starting your macro without any Autocad session running.
    the simplest (for you) handling of any Autocad session running could be just throwing a message to inform the user that he has to start an Autocad session with a valid drawing open before runnong this macro, and then exit the macro

    4) selection set handling
    adding
    Code:
    SelSet.Delete
    before exiting your sub prevents you from improperly adding elements to your selectionset on each run of your macro
    next, you should get this issue some steps further
    with
    Code:
      On Error Resume Next
      Set SelSet = AcadApp.ActiveDocument.SelectionSets.Add("SELSET")
    you're first asking VBA interpreter to ignore errors and then trying to set a new selection set name "SELSET".
    so you're not handling the case of a selectionset named "SELSET" being in your drawing already. And this could very well happen (should you ever exit form your previous run before reaching the "SelSet.Delete" statement (due to any code error before it, or the user stopping the macro, or a power failure, and so on...) resulting in no "SelSet" object set and thus making any subsequent statement using it (like
    Code:
    SelSet.Select acSelectionSetAll, , , FiltersType, FiltersData"
    ) fail.
    to handle this condition you could act in a pretty similar way you do with AcadApp variable: after
    Code:
    Set SelSet = AcadApp.ActiveDocument.SelectionSets.Add("SELSET")
    check if "SelSet" is nothing. if it is, the a selection set named "SELSET" is already there so you just set your "SelSet" object to it (use selectionset object's "Item" property instead of its "Add" method).

    5) you replaced any previous
    Code:
    Cells(3, Column)
    with
    Code:
    Cells(Row, Column)
    this way you're checking if the running attribute name (namely, its TagString property) equals the string in your activesheet's cell placed in the current column (which seems correct) and in the current row (which however gets updated to the next down after each "SelSet" entity iteration). just check and setthe proper row reference to where you actually must find the attribute name in "Attributes" sheet

    6) still open issue


    furthermore, I think it's time to add the following issues

    8 ) cells references
    after properly using statements like
    Code:
    excelSheet.Range(Cells(1, 1), Cells(40, 11)).Clear
    and the likes, you also use statements like
    Code:
    Cells(1, 1).Value = "BLOCCO"
    but be aware that omitting "excelSheet." reference leads to referring to the active excel sheet. which could not be the one you'd want to use!
    this could happen if you'd place a button to run the macro in a sheet different than "Attributes" (or in a different workbook even!) so that all those "Cells" references would point to the button sheet
    it's much safer alway using a complete reference like "mySheet.Cells(" to be sure you're always pointing to cells in the proper sheet (and Woorkbook too, if you take care of it when setting your worksheet object).
    in such cases, you may also avoid the boring recurring of a most probably long string by means of the "with - end with" block statement like follows:
    Code:
    With excelSheet
        .Cells(1, 1).Value = "BLOCCO"
        .Cells(1, 2).Value = "Handle"
        .Cells(1, 3).Value = "TAG1"
        .Cells(1, 4).Value = "TERM01"
        .Cells(1, 5).Value = "TERM02"
        .Cells(1, 6).Value = "TERM03"
        .Cells(1, 7).Value = "POS1"
        .Cells(1, 8).Value = "POS2"
        .Cells(1, 9).Value = "POS3"
        .Cells(1, 10).Value = "COLOR"
        .Cells(1, 11).Value = "XREF"
    End With
    it's a handy feature but you mast also carefully always check which "with-end with" block you're in, to avoid misleading references

    9 ) code polishing
    9.1) "Dim" statements - 1
    check all you "Dim" statements to find those referring to variables you don't use (like "mspace", and some others) or with double declaration (like "Excel")
    you can simply place the cursor inside a variable name and use the "find" button (that one with a binoculars icon) or pressing Crtl+F to find all its occurrences.

    9.2) "Dim" statements -2
    every variable mus be followed by "as" keyword and the proper type variable name
    otherwise a "Variant" type would be assumed as default
    this means that a statement like
    Code:
    Dim i, Row, j, Column As Integer
    would result in "i", "Row" and "j" variables declared as of variant type and "Column" as of integer type.
    this should not affect your macro as it is, but could lead to some error should you ever use some methods that require specific variable types.
    So it's good programming practice to have total control over variable declaration

    last but not least, I wonder whether you're already using the debugging tools the VBA IDE offers. They're really helpful, and among all others I think you should try using "break points" for stepping through your code and switching between VBA IDE, Excel and Autocad windows to see what's going on after each step of your macro. you can also check variables current values through "Local variable window" and the "Immediate Window" (you can activate both from the "View" menu). I've always found this as the most proficient learning tool.

  8. #8
    Member
    Join Date
    2014-05
    Posts
    14
    Login to Give a bone
    0

    Default Re: Problem vba code

    Ops, here the file in Autocad 2010 version
    https://dl.dropboxusercontent.com/u/...tr_v3_2010.dwg
    regards

  9. #9
    Active Member
    Join Date
    2012-11
    Location
    Italy
    Posts
    65
    Login to Give a bone
    0

    Default Re: Problem vba code

    thank you

    if you will, let me know if what pointed out in post #7 led you to any progress
    its point 5) should be the most effective for your algorithm logic. but all other points I think are useful to make your macro robust

  10. #10
    Member
    Join Date
    2014-05
    Posts
    14
    Login to Give a bone
    0

    Default Re: Problem vba code

    Hello everyone
    I put into practice the valuable advice of RicVba and the results are excellent
    In fact when extracting attributes everything matches
    but if you will have a few questions
    1. When you extract the attributes because they return the results in reverse order ( read from right to left)
    2. Should I post the same block several times how do we know if it is the order of insertion
    Then I have to say I've changed the locks so that everyone had the same labels , otherwise the code vba inserted the label in the first free cell rather than the exact one
    I attach files and hoping for more good advice

    https://dl.dropboxusercontent.com/u/...xtAttr_v4.xlsm

    https://dl.dropboxusercontent.com/u/...tr_v4_2010.dwg

Page 1 of 2 12 LastLast

Similar Threads

  1. Code Problem!
    By jayhay35365091 in forum AutoLISP
    Replies: 2
    Last Post: 2014-11-21, 02:52 PM
  2. Help Please.....Problem With Code
    By CADdancer in forum AutoLISP
    Replies: 9
    Last Post: 2012-07-31, 07:26 PM
  3. Help! A problem with VBA code
    By safar.said235204 in forum VBA/COM Interop
    Replies: 2
    Last Post: 2010-11-28, 08:47 PM
  4. problem loading lisp code
    By mdsalman2003 in forum AutoLISP
    Replies: 14
    Last Post: 2010-06-18, 08:23 PM
  5. Problem with code in VBA
    By DW2Whittle in forum VBA/COM Interop
    Replies: 1
    Last Post: 2008-03-31, 04:13 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •