State of Play 2018/wk28


Been converting VBScript files from vbs to wsf, so that can use common library files, thus reducing the size of the files: placed the revised wsf files on my sendto menu. Wrote VBScript for generating a series of folders based on calendar sorted into seasons, months, weeks and days, and parts of day: with optional tagging of days based on some recurrent cycle.

MS Excel Export to HTML

Experimenting exporting of spreadsheet data to simplified html file. For the tables on the blog I already have vba  macros to export to html table, however most of my spreadsheets are not tables and therefore can be made simpler.

First stage was to scan the “UsedRange” and write each row to an html “div” element, and combine each cell into several “span” elements. Apply classes through a css style file, to set numbers right justified. The problem with this approach was meaningless cell elements detached from illustrations. To fix this I created a style named “Figure”, and styled the region of the illustrations. The illustration comprises of shapes with annotations in worksheet cells.

The new process was to scan the region and exclude those cells which are styled as “Figure”, this allowed only the text to be exported. Having got this good enough, then looked at exporting the illustrations to image files.

MS Excel Export Images

Having now exported the text component of the worksheet, started a second scan to now export the illustrations, by capturing the cells styled as “Figure” as an image. This was done by copy/pasting the image to a “Chart” object belonging to a “ChartObject” object then using the export method. Also attempted to make the background of the image transparent but experienced problems.

Whilst could use the user interface to set the background colour to transparent, such was not possible when using vba. Then again if undo the setting, it is no longer possible to set the transparent colour. Using the GUI, can paste the image to a picture object and set the background colour to transparent, but cannot paste the image to the chart object, can only set the image as a background: when do that the transparency is lost.

Therefore the vba became cumbersome. Capture the image using “copypicture” method, paste to worksheet set “TransparentBackground” to true, then set “TransparencyColor” to desired colour for the picture object. Then copy this shape object, then delete, then paste into the “Chart” object of the “ChartObject” and then export to image file (.png).

Having got this to work created another style “TextBlock” for th text areas of the worksheet. The idea was to create the html file with two side by side columns, one with text and one with images. But for the test worksheet there was too much overlap between images and text blocks, also the task seemed too involved at the moment. So abandoned that idea and instead highlighted all the blocks as “Figure” and exported the worksheet as a series of images. Which at this stage may be a better idea: as it keeps the html simple. Also at the end of the day I need to insert the images to the post via the wordpress image library: so I cannot use any html code I generate in its entirety, I will have to selectively copy/paste chunks of the html into the wordpress post.

Regular Expressions, Arrays to Ranges, and Dictionaries in VBA

Experimented with dictionaries. Scanning list of internet bookmarks and another list of project  titles for keywords. Used a list of stop words and general words to check the character strings. So placed the stop words on one worksheet, general word list on another worksheet. Whilst the list to search was placed on yet another worksheet. The “UsedRange” of the list to search was converted to an array. The stopwords and general words were added to a dictionary by scanning the ranges. The array of data was then scanned, each element was broken into words using regular expressions. The words were then compared against the stops words and general word dictionaries, if not in either than the word was added to a new dictionary. The new dictionary was then written to a worksheet.

I expected the task to take several minutes compared to past experience using MS Access or MS Excel and parsing the words using own routine, and otherwise searching keyword table and adding new elements to the end. The task however was completed in a few seconds. Speed improvement comes from regular expression splitting text string, and then from dictionary fast check on existence and adding new items. Not sure that adding range to an array contributed to much speed improvement for this task: but haven’t checked.

The Website

Also noticed I haven’t added the search box back to the website, so something I will need to do in next few weeks.