Architectural License Update based on Excel

[Anonymous]

My company is an architectural firm. We do most of our work in four or five states but we are registered in many more states. When we start up a project we use AutoLISP to copy files from a template directory into a project directory. We then use another lisp routine and insert a static text entity that has the architectural license number and expiration date based  on what state the project is in. This works fine except that sometimes a project sticks around a while and the expiration date has been updated. Currently we have an excel sheet that has the updated data, but i am looking for a way to have it updated in AutoCAD, either automatically or when the plot to pdf event has taken place. We don't use sheet sets because the boss doesn't like them. I have posted about this before and from what i remember some people suggested using xdata, or xrecords or something like that. I assume i would have to have a field or a table or something dynamic that could communicate with excel. Maybe a block with attribute could work? Has anyone done anything similar to this? Is it possible to do? It may be beyond my current capabilities, but does anyone know which direction i should look into? Would VBA or .Net be able to do this? I am not sure if lisp is capable of something like this. Lee Mac might be able to do this, but maybe someone else has done this too? The program would have to be able to access the license date field or table and compare that to the specific cell in the network location of the excel file based on what state the project is in.

[Moshe-A]

@Anonymous  hi,

You can easily do this by opening (on each plot to pdf) the excel file (but it also has to exist in csv format, cause autolisp can only read from text file) and check for right field inside. do you know how to open and read from file in autolisp? to assist you more we have to see your excel (csv) file.

moshe

[Kent1Cooper]

---

@Anonymous wrote:

.... We ... insert a static text entity that has the architectural license number and expiration date based  on what state the project is in. …..

---

Instead of using Text, use an XREF.  Then when a license expiration date changes, you change it in the XREF'd drawing file, and it will be changed everywhere in all projects that reference it.  No need to run any kind of routine [including no need to remember that you need to do that] to dig the changed date out of a spreadsheet file.

[Anonymous]

That's an interesting idea. I would need to add the xref to the startup routine, or i could grab the titleblock from a certain location that already has the xref defined based on the state. i was kind of hoping to not have to update things twice but it is a possible solution. I don't want to have too many xrefs but this will give me something to think about.

---

@Kent1Cooper wrote:

---

@Anonymous wrote:

.... We ... insert a static text entity that has the architectural license number and expiration date based  on what state the project is in. …..

---

Instead of using Text, use an XREF.  Then when a license expiration date changes, you change it in the XREF'd drawing file, and it will be changed everywhere in all projects that reference it.  No need to run any kind of routine [including no need to remember that you need to do that] to dig the changed date out of a spreadsheet file.

---

[dgorsman]

You can read Excel files through LISP, although it requires a few gymnastics.  It's a bit easier in VBA and dotNET.

Still, I'd recommend switching to XML for small/mid-sized data storage.  It's already in heavy use (e.g. tool palettes,  CUIX files), is accessible through all major customization options (including Excel VBA), and can be easily tweaked with simple text editors. 

[Anonymous]

---

@dgorsman wrote:

You can read Excel files through LISP, although it requires a few gymnastics.  It's a bit easier in VBA and dotNET.

Still, I'd recommend switching to XML for small/mid-sized data storage.  It's already in heavy use (e.g. tool palettes,  CUIX files), is accessible through all major customization options (including Excel VBA), and can be easily tweaked with simple text editors. 

---

What exactly do you mean when you say switch to XML for storage? What i have now is an excel spreadsheet that has all of the license #'s and expiration dates, based on the state. Maybe i should post a sample of the excel sheet. I don't want to post the real thing because i don't want our license #'s and expiration dates to be posted online but i will post a modified version, but you will still be able to see what i am talking about.

[Anonymous]

Here is a modified version of the spreadsheet. For instance if it is a Florida job, the expiration date might be something like the 7D cell location and if the job is in Maryland it might be 8D. So what i want to do is update the text that is in my titleblock to match whatever the excel cell has because that is what is updated from the administration staff when the license gets renewed.

[Sea-Haven]

There is lots of stuff out there about read an excel file that would be the easiest way Google. Just add a (load "checkversion") in your pdf code this way the excel stuff can be tested outside the main routine. You may want it also elsewhere not just plot update any time.