Small addtion to faq687-5800

[CanAusNick]

I am a total newbie to using VBA. All I am after is a modification to the faq code that will allow me to put more attributes into the one table (one per column). Currently the code allows me to place the DrawingNo, but I would like to have four other attributes placed in the table. These would be REV, TitleLine1, TitleLine2, TitleLine3, and if I want more etc... I've gotten the code from the faq to work perfectly so far. Excellent code by the way.

The database I have created is for a drawing registry, for tracking changes etc. Currently I am extracting the data manuallly from the drawings to place into the database, something I would like to avoid doing.

Once I have this bit I'll be able to run a script to batch the drawings (approx 3000 of them) into the database.

Thanks for your help.

 

[TCARPENTER]

Hi CanAusNick,

Glad the FAQ was helpful. Here's the portion you care about:

' Walk the array, comparing tag strings to field names,
  ' and populating or updating accordingly.
  '
  For intAttribCnt = LBound(varAttribs) To UBound(varAttribs)
    For Each fldAttribs In rstAttribs.Fields
      ' Does the tag string value match the field name?
      '
      [b]If UCase(fldAttribs.Name) = UCase(varAttribs(intAttribCnt).TagString)[/b] Then
        ' Must have the corresponding tag string and field name,
        ' make sure the attribute is not blank, then update the field.
        '
    If len(fldAttribs.Value) > 0 Then
      fldAttribs.Value = varAttribs(intAttribCnt).TextString
    End If
        Exit For
      End If
    Next fldAttribs
  Next intAttribCnt

All I did for the FAQ was to name the fields the same as the attribute tag names, (since it was just for demonstration purposes), but if you name your fields differently, you'll just need to "map" your attribute tag names, to the field names. The bold text above is what does the comparison.

HTH
Todd

 

[CanAusNick]

Well I guess I'm at a loss now. I've gone over everything and since I'm a complete beginner here I'm not 100% sure what can be missing. I have your code with the proper changes necessary, my datatbase and titleblock use exactly the same field information but yet I'm not populating the database. I've tried different methods of primary keys to see if that makes a difference but nothing seems to work.

Is there a limit on the number of attributes by chance? But even then I would expect some information would make it in and some wouldn't. I had the drawing number in but now it doesn't show up anymore. I'm not getting errors of any sort either. The connectivity is there and a record gets added (when I use autonumber as primary key) but it's completely blank.

I'll post my code in so you can have a look. Maybe I missed something? The number of attributes that I would like to gather from the title block is 72. Not sure if that is the issue or not.

varAttribs = AttribExtract(ssTitleBlock(0))

' Connect to the title block database.
'
Connect "\\server\newgroupdata$\Design\9000_ELECTRICAL\EBASE\Access\dwgRegistry.mdb", strTblName

' Walk the array and find the "Primary Key" field.
'
For intAttribCnt = LBound(varAttribs) To UBound(varAttribs)
If UCase(varAttribs(intAttribCnt).TagString) = "PrimeKey" Then

'PrimeKey is an Autonumber if the primary key is blank you get an error in the code
' Now search for the existence of this record in the database
' and if there's a match, ask the user how to handle it.
'
strSearch = varAttribs(intAttribCnt).TextString
Exit For
End If
Next intAttribCnt

' Now search the database, duplicate drawing numbers aren't allowed,
' if one is found, prompt the user how handle it.
'
rstAttribs.Find "[DRGNOCODE]= '" & strSearch & "'"

'DRGNOCODE is the attribute name for the drawing number



I'm assuming that if I were to do anything wrong it would be in this area as it appears I have connectivity. My table information is as follows.


On Error GoTo ExportAttribs_Error
strTblName = "ELDTitleACAD" ' Table name in database.
Set AcadDoc = ThisDrawing ' Current drawing.

' Build the filter criteria.
'
BuildFilter intData, varData, -4, "<and", _
0, "INSERT", _
2, "XGCDA125_A1TIT", _
-4, "and>"


Any help would be appreciated. Judging from what little I know this should work pefectly for my situation.

Thanks for your help so far.

 

[CanAusNick]

Hi Todd,

A co-worker and myself managed to walk our way through the code (he's a bit better at than me, I understand programming but have trouble with the code) and we got it to work eventually. We ended up commenting out this part of the code:

'If len(fldAttribs.Value) > 0 Then
fldAttribs.Value = varAttribs(intAttribCnt).TextString
'End If

We keep the string but got rid of the If statement and it worked after that.

I managed to write an AutoCAD script and populated some of the drawings as a test and it work wonderfully! Thank you so much for that code.

Now I just need to figure out the next part. Updating the drawing from Access. Know of any code for this hanging around?

Nick

 

[TCARPENTER]

Hi Nick,

Of course! Check out these other threads:

http://www.tek-tips.com/viewthread.cfm?qid=1395632

http://www.tek-tips.com/viewthread.cfm?qid=1047267

One or both has an import attributes routine - you'll get the idea pretty quickly but in a nutshell, you just reverse what you've done exporting the attributes, and instead of reading the texstring value for the attributes, you just set it.

HTH
Todd

 

[CanAusNick]

Hi Todd,

I think I get the idea. I'll run it as a separate module connected to a button in AutoCAD, along with another button to export to Access. I'll try it on Monday and see how it goes (Saturday here in Australia). Thanks!

I have one more question. I'm not even sure if this is possible but if there was a way to do it I'd be estactically happy.

Because the project I'm on is rather large and the initial setup with the drawing structure and so on has been so bad I'm kind of doing all this important stuff near the end. They didn't incorportate a CAD document system which would have been ideal but they use another document system that is web based using PDFs. Not a very effective system I might add for design. Is there a way to open a CAD drawing and convert to a PDF from Access? I would rather not have AutoCAD the program open. Maybe there's some dlls around?

The reason I want to do this is because my manager is going to be using this database I created in Access. He doesn't like using the web based system and I'm sort of tired of having to keep PDFs up to date all the time to make him happy. Even though we have TrueView, he doesn't like to use it.

Any suggestions? I'll understand if it's impossible or real difficult.

Nick

 

[TCARPENTER]

Hi Nick,

This one's tricky. The good news is there's more than one to skin a cat...

If you want to have AutoCAD process PDFs without opening
AutoCAD, take a look at the open dwg alliance opendwg.org or opendwgalliance.org I think. It's been a while since I've used their stuff.

The other option is to embed the true view into your Access forms (sometimes easier said than done), or look into the whip viewer - it's older but I think you can still find it. As for driving AutoCAD from Acces, I've had issues with this in this past - mostly unpredictability, sometimes AutoCAD would do what's it's supposed to other times it would just stare at me, or not even get launched at all...
Admittedly, this was with Access97 so it may work better now.

Now for my two cents, in the past when I've had to deal with this, I've given the users a custom save button, which just drive a save command and then a print to PDF all in one command - you can even overwrite the save command to do this if you want. But when I had to do large chunks, I wrote a batch processing routine which sat on it's own computer and it spent it's life querying a database looking for work to do. User's used a custom command in the document management system to create a list of drawings, and what they wanted to do, plot, saveas a prior release, reverse populate the document management system (similar to what you're doing), create a transmittal, etc.

Like I said lots of ways to skin a cat, each one has it's advantages and disadvantages but the drawback is, it's not hard, it's just involved.

HTH
Todd

 

[CanAusNick]

Hi Todd,

Thanks for that. Interesting stuff on Opendwg.org, wish I had the time to investigate it more (that and being a better programmer!). Thanks for all your help. I'll just stick to keeping it simple. A simple back and forth between the database and a manual PDF creation/view. I'm able to open both drawings and PDFs from my database. Access 2003 handles it rather well.


Nick

 

[CanAusNick]

Hi Todd,

Going from Access to AutoCAD appears to be a bit more difficult than I first thought. I get an error and all it says is Execution Error. I did the reverse on the code you showed in the other example.

http://www.tek-tips.com/viewthread.cfm?qid=1047267

I also created a new Sub called ImportAttribs(), could this be the issue? The sub is exactly the same as ExportAttribs() the only difference is the name. This was so I could use two different buttons. Which appear to be working except for the error.

 

[CanAusNick]

One other thing, keeping in mind that I'm totally green on this stuff, I have two titleblocks to work with, is there some code to add to make it pick up both titleblocks? The attributes are the same, different size sheets. i.e.: A0 and A1.

Thanks!

Nick

 

[TCARPENTER]

Hi Nick,

Can you post the code? It may be something simple. Also what version of AutoCAD are you using? And is AutoCAD already running, or are you trying to start it?

I doubt the two title blocks are the issue, they may be but I don't think so...

Todd

 

[CanAusNick]

Hi Todd,

I figured the Exectution Error out, I just changed an End to Exit Sub and that fixed that issue.

The other one was a question about if I can put two titleblocks into the one code easily.

The other question I have (I have lots of questions don't I?) this one I hope is easy. Because I have various types of drawings I have placed them in to different tables. I know there's better ways of doing things but this was easiest for me. One day I'll sort that out.

Anyway to avoid putting the wrong drawing in the wrong table is there a way to easily verify the drawing number?
I've made separate buttons to import each type of drawing.

I'm looking at using InStr for verification but I'm not sure of the best spot to put the code. I just want the code to tell someone they've pushed the wrong button and then goto the end.

Sort of like this:

   If InStr(strSearch, "E-AD", 1) > 0 Then 'E-AD stands for type of drawing
      
        
    MsgBox ("You have selected the wrong export button")
    
    GoTo ExportAttribs_Exit
    
    End If

Thanks for the quick reply.

Nick

 

[TCARPENTER]

Hi Nick,

Without knowing the nuts and bolts of what you're doing, it sounds like a reasonable spot, but, as long as you're at it, why not just give the user one button, have it run your test function, and then based on the result, run the appropriate routine?

HTH
Todd

 

[CanAusNick]

Hi Todd,

That code I created doesn't work for me which is why I was sort of asking for some help. I keep getting error 13 type mismatch when I use it. I've tried it in different spots with the same result. I'm just verifying the text in the drawing number to ensure I put the right drawing in the right table.

As for the side of simplicity, it comes down to not really being a programmer (I wouldn't really have a clue where to begin) and having the luxury of time. I would love to simplify the whole thing (I know generally what has to be done to do this) but I'm sort of doing this in a rush and not really having the time to sort things out. Like I stated before I know there's a lot better ways of doing things it really comes down to knowledge (which I lack) and time (which I also lack). I'm sure if you saw my program you'd be shocked! I'm sure I'm not utilising best practices.

If I get more time I will see about fixing things but for now since I'm sort of under pressure to sort this out quickly I just need to get something working and reasonably usable.

Thanks for all your help.

Nick

 

[TCARPENTER]

Hi Nick,

Change your code to this:

Instr(String1:=strSearch, String2:="E-AD", Compare:=vbTextCompare)

When you don't use some of the options, you need to specify which options you are using.

HTH
Todd

 

[CanAusNick]

Hi Todd,

Thanks for that. I finally sorted out how to get it to work and it does a good job.

Thanks for everything. My program is probably not the best thing on earth but it works. I guess that's all that matters huh?

Nick

 

[TCARPENTER]

Hi Nick,

That's exactly right! You're there to get the job done - nobody said it had to be pretty - just as long as it works!

Glad it worked for you!

Todd