State of Play: 2016/wk24

Date Formats

The week numbers currently used in the title are compatible with Thunderbird and the printed calendar I use, these I believe are iso week numbers, unfortunately I previously used MS Outlook dates and MS Excel and these week numbers became out off sync. Additionally I wanted to write a VBScript to sort files based on week number and didn’t have any appropriate date functions in VBScript. To get VBA code suitable information was found on Pearson Consulting website, whilst the Microsoft Office support provides information about extra parameters for the Weeknum function, and some other site which indicated that the extra parameter required is the number 21.

I revisited dates again recently whilst writing a VBA subroutine to read all my Acad drawing files (.dwg) with ProgeCAD 2016, and extract drawing time (TDINDWG) and looking at drawing creation(TDCREATE) and modification dates (TDUPDATE). Whilst I could format the dates with DIESEL via a menu macro, I couldn’t find any information about the format of the these dates returned in VBA.  At first I thought may be they were Unix format dates, it being the origin of some of the other peculiarities in Acad. I had previously looked into Unix dates to MS Excel dates when writing routines to read the google bookmarks from Chrome html export file: chrome and blogger tools. However turned out not to be a Unix format date, some websites indicated that was a Julian date or day number, since I have Turbo Pascal procedures for Julian day numbers I attempted to use these: but I’m guessing the routines I have are shortened to be compatible with the smaller integer capabilities of early PC’s.  Searching for more appropriate routines, discovered that most MS Excel sites referred to some other kind of Julian date format which is otherwise known as an ordinal date and therefore not much use. Therefore settled for working through the calculations presented in the Wikipedia article by setting up the calculations in an MS Excel workbook. This confirmed that the Acad dates are compatible with Julian Dates and Times.

That being resolved my main interest was the time worked on the drawings, most sites suggested multiplying the fractional component by 86400 to get time in seconds which doesn’t seem very helpful. Why multiply it by such? The answer is that the time is given in days, the fractional part is thus a fraction of a day, and there are 24 hours/day x 60 minutes/hour x 60 seconds/minute,  = 86400 seconds in a day. The time command in ProgeCAD 2016 fails to return the number of days, for TDINDWG, which was the cause of part of my problems in interpreting the meaning of the variable.

Geographical Information Systems (GIS)

Previously set up a simple geographical information system using Acad LT and MS Excel. I basically created the key map grid from my UBD street directory. This I did by inserting blocks for each map, then extracting the attribute data for these blocks generating tables which allow me to map the UBD map references to Australian Map Grid (AMG) coordinates. Our project database in MS Access contains project site and client addresses, and at the minimum conatins the UBD map references for the suburbs. Using such data was able to plot the project location and density map shown on the about page. That worked for Adelaide metropolitan area, but don’t have such UBD map for the whole of the Yorke Peninsula, therefore went looking at alternative systems.

Discovered that can create a simple kml file for Google earth, and only need to provide an address for a place marker, if import this kml file into Google earth it will automatically generate the latitude/longitude coordinates. If save back to a kml file then that file will have the coordinates. There after looked into how to convert the latitude/longitude coordinates into Australian Map Coordinates or at least 2D coordinates I can use in Acad LT, and use with simple trig formula. My main interests are central places, and catchment zones, and the travelling salesman problem, and the transportation algorithm. My simple graphical approach so far is illustrated by the following MS Excel workbook : the approach taken is simply to draw lines to show connection between two or more nodes within a given distance of one another. I have found source code for Delaunay triangles and put that to work, but really want code for Voronoi polygons.

Taking into consideration my need for data and at least a grid for the Yorke Peninsula, along with the tools for analysing geographical data, I decided to take another look at available GIS’s. The first I found previously was MapMaker gratis, and that is still available, then there is Google maps and Google earth, but the tool which seems to hold most promise is QGIS though at the moment the Delaunay and Voronoi polygon tools fail with an “unable to write file” error. On the other hand there are source code files written in python, though that still leaves the problem of coordinate conversion which is one of the main reasons started to take a look at GIS.

Any case this got me back to looking at the original mapping I did with Acad LT, and created a simple workbook for plotting lines defined by nodes to ProgeCAD using COM automation. This code I already had in a form for drawing tension membranes and cable-nets, so I ripped it out of the workbook for such and made more general purpose. This way I can copy the geographical points and lines calculated in other workbooks and paste them into the workbook for drawing.

Acad LT and ProgeCAD Scripting Differences

Previously I generated the Acad script in the worksheet cells, and pasted into Acad LT, however ProgeCAD, which I am currently using requires more blank lines than Acad LT and also doesn’t recognise blank cells as pressing the enter key. Consequently most of my MS Excel workbooks which generate script in the worksheets are of little use for working with ProgeCAD. Modifying the worksheets seems like too much work and also would make the worksheets less readable. Modifying my VBA script library for use with ProgeCAD is a lot easier than modifying the worksheets. the primary problem is that ProgeCAD requires more CR/LF’s where a simple space would work for Acad LT 2000. This I can accept for situations where input values now permit named items with spaces in them, but for what seems to be every single command option requiring an extra line, the script becomes less readable.

The worksheet script code I can paste to files, which I was doing before I discovered I could paste to the command line. So the simple solution would be to write something which can parse a script file, and replace appropriate spaces with CR/LF to enable the script to run in ProgeCAD. the problem is finding the appropriate spaces. An alternative is to write a MS Excel vba macro to print a worksheet range to a text file and parse the Acad script as it does so.

Which suggests another option, and that is to create a simplified description of the Acad script requirements, making the worksheet more readable, and the VBA macro then generates the required Acad script from the simplified code. And by the looks of it I probably don’t need to write anything as there is a tool available called script sheets, though I am mostly using 64 bit system and software, so may not be any help.  These are the spreadsheets I need to convert:

  1. CADD Scripting with spreadsheet
  2. Spreadsheet for drawing cold-formed steel sections

all other scripting I do using VBA via a library of script commands which I have written versions for Acad Script, and COM automation using DesignCAD, IntelliCAD, and MS Excel shapes layer. My main programs thus remain largely unchanged with changes relevant to the specific CAD system in the vba script library.

So dates, GIS, and CAD scripting whilst otherwise attempting to produce some generic design reports for cold-formed steel building systems.