AutoCAD auto cleanup – a script on close

Research links:

  • talk of general cleanup that one should do
  • triggers in AutoCAD are called reactors (this is a good starter article)
  • A colleague mentioned not to do anything that takes up too much time on a document close reactor – because it triggers AFTER the close has been issued.
    • I suggested on a save? And the answer was yes, overwrite the qsave function and call a qsave at the end of that routine.
  • we are looking for a drawing reactor – when the doc saves. It mentions to use the vlr-types command to return a complete list of available reactor types.
  • a “before close” reactor … almost exists – read this. Yes – this is the ticket. Change “CLOSE” to “QSAVE” or “QUIT” … or all 3 if you wish. It works.

Continue reading

A really quick way to format your XML – use Notepad++ with the ‘XML Tools’ plug in

What someone asked me was to make the xml readable, and I needed it last week too. The term is called “pretty print” and that is the function or procedure that needs to be performed on the text to make it work.

What I use is Notepad++ with the tool plugin called “XML Tools”. Simply

  • download Notepad++
  • do the updates it recommends (it will restart)
  • click the menu Plugins->Plugin Manager->Show Plugin Manager
  • scroll down to XML Tools, check it and click Install ( if you already have it it might be the Installed tab)
  • Then perhaps after a restart
    • paste in your xml text (it must validate – which is one draw back about this tool – but that can be solved by a gazzillion online validators assuming your data is not too private (try this, or this, or this – sometimes all 3 will do the trick believe it or not. Fiddle with the options too.)
    • highlight the text (not sure if that is necessary but I do it and it works) then
    • choose the menu item Plugins->XML Tools->Pretty Print (XML Only with line breaks)
      • feel free to experiment with the other kinds.

 

Better VBA Error Handling

With VBA, it is generally a solution for one’s self or a small group of individuals. Most of my apps if an exception happens, a yellow line is highlighted in the VBA editor, you investigate the variables LIVE (on thy fly – love that about VBA) and adjust or fix the situation. If the code is for others, then some error handling is added and the world is stable until the next time.

Recently, I was asked to make the error handling cascade like c# – so now it is time to fully understand the error handling of VBA.

Here is SUPER resource – it is for MS Access, but will apply equally for all MS Office and other VBA apps. There are few new tricks I will try on some hornery AutoCAD tools. In Excel the “Do Events” usuallly fixes many of the super odd errors (VBA is going faster than Excel and.. Do Events helps to sync the two into stability.

Excel and web services

So there is an application that requires a best guess – but still human interaction to choose which item when there is more than one result returned.

So one can make a web service to pull back a pull down and make a form for the user to make a choice allowing quicker spreadsheets to be made. Why not web services and in my case, ASP.NET can be used.

Here is a REALLY old article, but it has all the pieces. http://msdn.microsoft.com/en-us/magazine/cc163837.aspx#S1

http://stackoverflow.com/questions/8249706/run-stored-procedure-and-return-values-from-vba

http://stackoverflow.com/questions/1120674/accessing-sql-database-in-excel-vba

http://www.excelforum.com/excel-general/758998-data-validation-drop-down-menu-from-a-database.html

http://www.erlandsendata.no/english/index.php?t=envbadac

Really geeky stuff – what is late vs. early binding. Also … Dictionaries in VBA

OK – so I am reading stuff that seems a little too geeky, but after asking “what is this late vs. early binding” stuff – I had to know what it was. Here is a simple explanation – even I can understand. It really does (did) not matter – but the main point is summarized nicely in this article “… as long as the programmer uses it responsibly (late binding) “.

http://stackoverflow.com/questions/10580/what-is-early-and-late-binding

What is this about? I want to use Dictionaries in VBA … now I have finally snapped and found out. Here is THAT article …

http://stackoverflow.com/a/23675963 and the ref to the msdn to it is here.

http://www.brianweidenbaum.com/projects/vba-arraylist/

Virtual Reality Headset for Navis? Cad? Interesting.

A friend mentioned this to me. Because I am working for an EPC (Engineering Procurement Company) that builds 3D models, I immediately put VR and Navis together. It turns out he was just mentioning a cool concept. Nonetheless – if this can all be put together for an EPC, a virtual head tour might not be far behind.

Oculous: http://www.oculusvr.com/blog/announcing-the-oculus-rift-development-kit-2-dk2/

Adding text box input to a function

1. Start by finding the function that runs the dialog box in AutoPlant, for me this was the “H.I.D. Stanchion Mount Lighting Fixture”.

2.Open Bentley AutoPlant Class editor.

Capture1

3.Find the function in the “Class List” window.

4.Double click on the selected class to open the class properties window.Capture2

5.After opening the window select the methods tab.

6.Using the “script path” column find the directory and file associated with the INPUT_METHOD.

7.Copy this file to your working directory and rename it.

8.In the class list window find and open the class associated with your functions.

9.In the window that was just opened click on the button next to “Method Key”, this will open the Method list window.

10. In the method list window click on the line next to the *.

NOTE: Once you have clicked on the line, clicking on anything other than a different part of the line or the update button at the bottom WILL crash the class editor.

Capture3

11. Under the “method” column write INPUT_METHOD, this name is required because AutoPlant will associate it with one of the predefined method types that can be seen in the previous window.

12. Under the Function column write the description of the function.

13. Under the script path write %APP_DIR%\Files_folder\File_name, %APP_DIR% indicates that the file is in your overall working directory, it should be left as %APP_DIR% and NOT be replaced with the directory path.

14. Click the “Update” button in the bottom left conner of the window, then click Ok to exit the window.

15. Click the save button in the top left of the Bentley Class editor window, if open AutoCAD/AutoPlant must be closed and restarted for the changes to take effect.

Exporting MS Access to Excel using VBA from within Excel – MS Access dB table stacker

Recently, I made an excel spreadsheet stacker where, given a list of excel workbook paths, it would stack them all into one. Then I thought, this would be beyond great to stack Bentley Spec tables on top of one another so we can do a search and troubleshoot quicker.

In short, open up the SpecTables table of each, export those tables that we find to Excel sheets, then use my stacker and stack them all on top of one another. Since the stacker removes any need to worry about column order – AND since it tells you what columns are “lost” we can run it many times until everything is under one umbrella.

Here are the links that helped me export from MS Access to Excel sheets but using Excel as the engine which holds the VBA that makes this all work

  • http://stackoverflow.com/questions/1849580/export-ms-access-tables-through-vba-to-an-excel-spreadsheet-in-same-directory
    • here was the heart of the macro tip from this guy

[vb]

Dim outputFileName As String
outputFileName = CurrentProject.Path & "\Export_" & Format(Date, "yyyyMMdd") & ".xls"
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "Table1", outputFileName , True
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "Table2", outputFileName

[/vb]

  • http://www.mrexcel.com/forum/excel-questions/643188-excel-run-access-docmd-transferspreadsheet-into-activeworkbook.html

I had to add the MS Access reference (I am using both version 14 or MS Office 2010) and the reference for DAO 3.6 through the “Tools-Add Reference” menu in Excel’s VBA editor.