Recent posts and inspirations

Well here is a link with many online colour scheme resources: http://www.devwebpro.com/25-popular-color-scheme-and-palette-generators/

EVA, the 'Ethical Vegetarian Association' in Gent, Belgium, has successfully promoted the campaign to take up a vegetarian diet for at least one day a week -
on Thursday. The local authorities have been endorsing the project, making Gent one of the first, if not the first city in the world to officially endorse the one vegetarian day a week.

The project celebrates it's one year anniversary this week, and launched a video on YouTube under the slogan:

One day makes such a big difference
Een dag maakt zo'n groot verschil

This happened to me and I was really pondering what was going on.
But I remembered that when Excel has a crash, the next time it loads it might prompt to de-activate certain add-ins. And indeed that did the trick.

So this might help out if you encounter the same issue:

• Click on the Office button and go to the Excel options
• Choose Add-ins
• Go to the combo box at the bottom entitled 'Manage'
• See whether you find personal.xlsb add-in and enable it again.

If you're after some good explanation and highlights in terms of error handling and debugging with VBA, I would suggest these:

• http://bytes.com/topic/access/insights/648856-debugging-vba: an excellent overview of tools available in VB editor
• http://www.cpearson.com/excel/ErrorHandling.htm: a good and brief article on the matter
• http://www.aivosto.com/vbtips/errorhandling.html: another guide
• http://pubs.logicalexpressions.com/pub0009/LPMArticle.asp?ID=410: with good example on how to use the locals window
• http://support.microsoft.com/kb/146864: List with VBA error codes and their meaning

I was asked recently to do a course on DOS-commands and on RUN-commands, i.e. commands you can type at the run prompt.

And although I scoped it somewhat to the most used and/or basic ones, there are people coming up with huge lists of them:

• http://mypchell.com/guides/34-guides/69-156-useful-run-commands

This course really felt like 'closing a cycle', as my very first IT manual ever was a DOS manual which came along with a Commodore PC - one without even a harddisk, that was, back in 1986.
And speaking of DOS commands of course brings us to batch files.
If ever you want to try some:

• http://forums.techguy.org/dos-pda-other/685747-write-log-output-batch-file.html
• http://www.computing.net/answers/programming/writing-to-a-file-from-xcopy/13152.html
• http://www.melbpc.org.au/pcupdate/8900/8904article3.htm
• http://www.chebucto.ns.ca/~ak621/DOS/BatExamp.html#DAF
• http://www.computerhope.com/batch.htm

Actually I'm using a batch files for my daily backups to external harddisks, with log files on top of it.... All thanks to good old DOS and a double click of a shortcut on my desktop :-)
Here's what I use:

setlocal
set LogPath=e:\Backups\
set LogFileExt=.txt
set LogFileName=Backup%LogFileExt%
::use set MyLogFile=%date:~4% instead to remove the day of the week
::set MyLogFile=%date%
set MyLogFile=%date%
set MyLogFile=%MyLogFile:/=-%
set MyLogFile=%LogPath%%MyLogFile%_%LogFileName%
::Note that the quotes are REQUIRED around %MyLogFIle% in case it contains a space
If NOT Exist "%MyLogFile%" goto:noseparator
Echo.>>"%MyLogFile%"
Echo.===================>>"%MyLogFile%"
:noseparator
echo.%Date% >>"%MyLogFile%"
echo.%Time% >>"%MyLogFile%"
:startbatch

xcopy d:\data g:\data /d/s/c/h/r/y/f/k >>"%MyLogFile%"
xcopy d:\2013Website g:\2013Website /d/s/c/h/r/y/f/k  >>"%MyLogFile%"
xcopy d:\Graphics g:\Graphics /d/s/c/h/r/y/f/k /EXCLUDE:e:\exclude.txt  >>"%MyLogFile%"
xcopy d:\Images g:\Images /d/s/c/h/r/y/f/k /EXCLUDE:e:\exclude.txt  >>"%MyLogFile%"

xcopy e:\Audio h:\Audio /d/s/c/h/r/y/f/k  >>"%MyLogFile%"
xcopy e:\Video h:\Video /d/s/c/h/r/y/f/k  >>"%MyLogFile%"
xcopy e:\VideoAudioTraining h:\VideoAudioTraining /d/s/c/h/r/y/f/k  >>"%MyLogFile%"

Excel pivot tables are just a fantastic tool. And you can even boost it in various ways.
Just some creative ideas I encountered while working on a problem with hiding items in a filter.

Sometimes you just need to think out of the box to get where you want to get. And that may mean create an extra column with new data, you can then integrate in your pivot table analysis.
As as sample of this approach: Filter an Excel Pivot table for upcoming dates.

And did you know there are some specific shortcuts and manipulations to sort and hide information in pivottables? You'll find a good article on this at following blog topic.

What I hadn't realised yet is that in pre-2007 versions, there was an option in the 'page field zone' to hide certain items from appearing in the combo box, as you can see in this article on pivot-table.com .
Excel 2007 uses a new concept under the name of 'report filter' and as the 'multi-selection' feature has been added, this possibility to remove items from appearing in the combo has disappeared.
So if certain data shouldn't appear, you might have to go back to the source data and hide them there and thus use a 'out of the box' solution.

One of the toughest things to do when coding VBA in Access development projects is coming to terms with SQL queries.
I've found following tips and tricks helpful :

Allen Browne, one of the main authorities on the matter, proposes a form with VBA code which actually does the hard coding into VBA of any SQL string of your choice.
You'll find the code and procedure at his site: http://allenbrowne.com/ser-71.html

Another thing you might have ran into is using query parameters into SQL strings. Well you can't basically, you must 'transform' them and here is how: http://www.mvps.org/access/queries/qry0013.htm. If you run into the error "Queries: Too few parameters, expected n" - that's exactly what I'm referring too here :-).

And this might come in handy too: some basic testing for empty recordsets - as when your SQL query doesn't return records in the recordset, you'll off into problem land as well.
I've found this one useful to solve this issue: http://bytes.com/topic/access/insights/632600-testing-empty-recordset

Looking after bootdisk sources?

• http://www.bootdisk.info/
• http://www.weethet.nl/english/hardware_bootfromusbstick.php
• http://www.ubcd4win.com/
• http://www.ezpcfix.net/
• http://netensis.net/combos:usb

I had never thought to still get involved in a Wordperfect for DOS project in the year 2010 !
And once going into it, I soon discovered there's still a good amount of people using this system for all kind of reasons.
I even discovered two very precious resources in setting up and configuring WP for DOS :

• http://www.columbia.edu/~em36/wpdos/
• http://www.wpuniverse.com/

As it concerned a very specific installation on top of being a DOS program (a Dutch language version with both azerty and US international qwerty keyboards) all kind of issues occurred ...
And getting the keyboards to behave with special accents was probably the most difficult task to tackle.

But end well, all well, and here are some specific links if ever it help someone out too:

• http://vetusware.com/ was the place where I could get onto the sourcefiles - allowing me to redo a clean installation to start with.
• http://www.infoprat.net/astuces/windows9x/astuces/clavier_009.php : explains and gives the code for an Azerty keyboard in a DOS environment (valid under DOS prompt or older Windows versions till Win2000) - along the same lines you'll find help on this forum topic as well: http://www.computing.net/answers/windows-31/keyboard-layout/10558.html.

• To have the Azerty keyboard behave under Windows XP go here: http://www.wpuniverse.com/vb/showthread.php?threadid=25159
• To tackle the US international keyboard you can try this link
• To install a network printer under Win2000 or WInXP: http://www.columbia.edu/~em36/wpdos/usbprint.html#win2knetuse

Doing lots of database development in Access brought up all kind of interesting insights and links.
Just a selection of some of these encountered goodies :

• http://allenbrowne.com/AppIssueChecker.html : Here you'll find a "Database Issue Checker Utility" - a small application addin doing all kind of checks.
• http://allenbrowne.com/bug-09.html: Flaws in MS Access, with an interesting discussion on Zero length and how to put it off.
• http://bytes.com/topic/access/insights/632600-testing-empty-recordset: Useful code to check for empty recordsets.
• http://autofeupdater.com/index.htm: A tool to quickly distribute updates in a Front-end / Back-end environment;
• http://www.fmsinc.com/tpapers/vbacode/: 17 ways to improve your VBA code in Access projects
• http://www.granite.ab.ca/access/tipsindex.htm: Tony Granite, another authority in the field of Access development, also published tons of inspiring tips
• http://www.rogersaccesslibrary.com/forum/forum_topics.asp?FID=1: Roger's Access library with many samples to download.
• http://www.fmsinc.com/Products/Admin/index.asp: A tool to monitor who's online and using the Backend in a FE / BE environment.
• http://bytes.com/topic/access/answers/480516-ado-vs-dao and also http://www.ozgrid.com/forum/showthread.php?t=28221: both sources on the difference between DAO and ADO libraries.

Ingenious all those solution maths-people come up with.
Take the problem to find a random value between two integers.

Well you take the interval between the two figures you have in mind, add one, then multiply with a random value and add the minimum of the figures you had in mind. Just rendering it as an integer and you're done ! If that sounds too much like maths.... well ... it is :-)

Given that the random function in Access is rnd() ( you obtain a decimal between 0 and 1) you get something like this to obtain a number between 10 and 100

Int ((100 - 10 + 1) * Rnd + 10)

Another interesting topic coming up through one of my clients.

I've found an enlightened discussion at one of these great Excel resource sites: http://www.dailydoseofexcel.com.
Especially the breakdown of the described first function and the elaboration on arrays is worth reading.

These are the two takes I've been going for:

{=MIN(FIND(CHAR(ROW(INDIRECT("65:90")));A1&"ABCDEFGHIJKLMNOPQRSTUVWXYZ"))}

The trick consists in finding the ASCII value of the first capital of a text-string composed out of the cell containing the evaluated text value combined with the 26 capitals of the Alphabet.

And then also a user defined function in VBA:

Function FirstCap(Cell As Range)
   For FirstCap = 1 To Len(Cell.Value)
      If Mid(Cell.Value, FirstCap, 1) Like "[A-Z]" Then Exit For
   Next FirstCap
End Function
' And =IF(firstcap(A1)>LEN(A1),NA(),firstcap(A1))

During a quite substantial development project in Access recently, I initially started developing in Access 2007. But as downgrading to the Access 2003 version the client used posed all kind of issues, I gave up and continued the development in Access 2003.

So far so good - well I thought, but not till rapidly ran into something I had never experienced. All kind of forms suddenly didn't want to load anymore as they became corrupted. < /br> Randomly, apparently, although frequently linked to removing labels or moving fields with their labels.
And mostly with the laconic error message: "there isn't enough memory to perform this operation" .
It became really a desperating situation.... grrr :-(

I then found a message on http://www.access-programmers.co.uk/ which really saved me and pointed me to a method to save a form as a text file (!!!) and then retrieve it again... Although it didn't always work, this and regular (obsessional ;-) ) backups made me swim across this river.

Application.SaveAsText acForm, "YourFormNameHere", "C:\Path\Filename.txt"

Application.LoadFromText acForm, "NewFormNameHere", "C:\Path\Filename.txt"

If the issue still continues, try doing a Compact and Repair before retrieving the form.
And a further comment on the forum pointing to a possible use of this technique:

If you have to provide documentation for your application you can use this technique to create text files that contain all the information about your forms. Then should anyone modify your form at any point or it becomes corrupt you can restore it from the text file.
You can be really clever and create a support database that would be similar to the MSysObjects table that contains a list of all your objects and store the contents of the text files into a memo field.

To close: you'll find a database corruption repair guide at everythingaccess.com
And some good tips to prevent corruption at the famous Allen Browns corner:http://www.allenbrowne.com/ser-25.html

The beauty of Excel is that there are many ways to achieve the same thing.
Take this topic e.g. : how to find the cell with the lowest value in an Excel sheet?

If you're after the exact location of that cell, and you can limit your search to a specific column or row with results, you could use a combination of the ADDRESS() and the MATCH() function which would give you something like:

=ADDRESS(MATCH(MIN(A:A);A:A;0);1;4;1)
=ADDRESS(2;MATCH(MIN(A2:Z2);A2:Z2;0);4)

Or you might be more picky as to which type of information you'd like to withdraw from that cell containing the minimum value -
it could be the range address, but also it's content, or it's column position, it's formatting or it's locked status. That's something you can do with the CELL() function -
it's first argument determines the type of info you'll get as a result. With our two examples:

to obtain the cell addresses::
=CELL("address";INDEX(A1:J2161;MATCH(MIN(A:A);A:A;0);1))
=CELL("address";INDEX(A1:J2161;2;MATCH(MIN(A2:Z2);A2:Z2;0)))

to obtain the cell values::
=CELL("contents";INDEX(A1:J2161;MATCH(MIN(A:A);A:A;0);1))
=CELL("address";INDEX(A1:J2161;2;MATCH(MIN(A2:Z2);A2:Z2;0)))

to obtain the cell lock status::
=CELL("protect";INDEX(A1:J2161;MATCH(MIN(A:A);A:A;0);1))
=CELL("protect";INDEX(A1:J2161;2;MATCH(MIN(A2:Z2);A2:Z2;0)))

If you need this approach regularly, you could also take the VBA road and insert a module with your own user defined function. Like the one here found at http://www.excelforum.com :

Function MinCellRef(dsRange As Range)

Dim c As Range
Dim MinCell As Single

MinCell = Application.WorksheetFunction.Min(dsRange)

For Each c In dsRange
If c.Value = MinCell Then
MinCellRef = c.Address
Exit Function
End If
Next c
End Function

And if you want some VBA solution which highlights and formats the cell containing the minimum value, that's possible too : http://excelvbamacro.com

You can download flash videos from a website either with dedicated tools for that purpose or by going into the source code and tracking the path allowing you to then manually download the flash file e.g.
Obviously the latter requires a bit more technical experience. And even if you have that, it can still be a time consuming job, as many flash files are often encapsulated in all kind of codings.

A good place to start probably is wikiHow: http://www.wikihow.com/Save-Flash-Animation-from-Website .
In terms of the dedicated tools I've personally good experiences with Replay Media Catcher - amongst others I've tried are Fast Video Download, a Firefox plugin and Flash Saver (http://www.flash-screen.com/flashsaver/).

On the coding side: you'll find a very good description and approach with Firefox, free but a bit more technical, on following website: http://geeklad.com/download-flash-video-from-any-website

ps: Regarding Fast Video Download: although it's a great tool, you might run into the problem of dialog boxes starting to open extremely slow: http://support.mozilla.com/nl/forum/1/623540

For those who are searching the specific AutoSave feature in Excel: search no longer - it is really gone.
Well yes it was replaced by the AutoRecover feature, but it's behavior is sligthly different.

You'll need to use an add-in to get exactly what you had before, i.e. possiblity to autosave your Excel file at a specific interval on a specific place (under a specific name ?).
I've found one here: http://www.jkp-ads.com/download.asp

If you're using a lot of templates in Office, it's a good idea to organize things a bit. This means changing the default template location and putting your templates in subfolders.
Because if you don't specify a specific location for your templates yourself, Office saves them in a very hard to tackle place in the profile folder of the user.
And if you have created sub-folders in the main template folder, Office is then going to show or represent these as separate tabs in the template dialog window.

In Office 2007 this still happens as in the older versions of the suite: rather mysteriously that is !
Because you have to go into the Word options to find the locations button - and only there ! - as it's unavailable in Excel or Powerpoint ...
So just go to the Advanced Options in the Word options dialog window, at the very bottom, you'll find the button File Locations giving you access to the default folders for the User Templates, as well as the Work group templates. Just choose the parent folder for you templates and presto.

All you have to do next is saving your Excel, Powerpoint or Word templates in this folder, or in sub-folders of this folder, and they will then automatically appear in the Template window.

Ever wondered or needed to do a mailmerge in MS Word with multiple group levels?
It is possible indeed; it involves customizing the merge fields a bit, but if that's what you need, you'll find the whole procedure through following links:

• http://cornell.veplan.net/article.aspx?&a=3815
• http://support.microsoft.com/kb/294686/
• http://homepage.swissonline.ch/cindymeister/mergfaq1.htm#DBPic

I had to go into the topic of video editing recently and come across this nice overview of tools for the matter: http://www.convertdirect.com/top_10_flv_converters.html

I got the best results with AVS Video Converter in terms of reframing, cutting and merging parts of video materials. But I really liked the user interface and the friedliness of a tool not listed in this article: FLV Editor Pro - I only had problems in properly merging the pieces as this didn't happened precisely enough for what I needed... in my experience and with my limited skills in this area, that is :-)

Two other tools which I've used and tried out are Replay Converter, the best tool in my experience in terms of allowing recording from web pages - this tool really allowed recording flash from some sites where all other tools failed.
And also Super, which I've found the best tool for converting purposes; not the most user friendly, but a really Super one !

If you work with iframes, you might have realised already there's an issue with the transparency settings.

Especially in the IE browsers even if you give specific CSS instructions to have the body background colour rendered transparent, you won't get what you're after - the background is still going to remain opaque.
Following article describes the issue and solution nicely : http://www.tech-recipes.com/rx/1253/htmlcss-transparent-iframes-in-all-browsers/ - in essence: you need to add allowtransparency="true" to the iframe HTML ; and of course also set the background transparency to the body CSS tagselector :-)

This question came up during one of my Excel courses - clearly something to be done with VBA, so I googled it and found following code at http://www.pcreview.co.uk/forums/thread-3768672.php:

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
     If ActiveWindow.SelectedSheets.Count > 1 Then
     With Application

          .EnableEvents = False
          .Undo
          MsgBox "Oh, please don't group the sheets..."
          ActiveWindow.SelectedSheets(1).Select 'Ungroups
          .EnableEvents = True
          End With
     End If
End Sub

ps: for some interesting elaboration on Events go here: http://www.cpearson.com/excel/Events.aspx and here : http://www.cpearson.com/excel/AppEvent.aspx

I remember having prepared an blog post covering this topic, after a question during one of my courses... However it never saw the light because there's always so many things to accomplish at the same given time.
But never despair as it says: recently an article was published on Sitepoint on this matter. Which triggered also an interesting debate.

Nice reading if you're in the CSS business.

This sounds like something fundamental, but for some reason I never really needed something to merge many word documents into one new document.
Till recently that is.

You can of course go the direction of the Master/Child documents approach in Outline view.
For more information on this you could try these links:

• http://office.microsoft.com ..... /....
• http://www.steve.maurer.net/... /....

But if you really envisage one flat document, apparently you need a macro to do this properly - you'll need to finetune it with the specific path containing your Word documents as well as pay attention to the specific extensions the documents have :

Sub MergeDocs()
    Dim rng As Range
    Dim MainDoc As Document
    Dim strFile As String
    Const strFolder = "C:\Courses\Word\" 'change asper your need
    Set MainDoc = Documents.Add
    strFile = Dir$(strFolder & "*.doc") ' can change to *.docx or *.rtf
    Do Until strFile = ""
        Set rng = MainDoc.Range
        rng.Collapse wdCollapseEnd
        rng.InsertFile strFolder & strFile
        strFile = Dir$()
    Loop
End Sub

If you right click a public folder in Outlook, and choose the option to 'Add to favorites', you'll add this favorite to the Public folder favorites only.
But if you thought or wanted it to appear in the main mailbox favorites, you'll notice or have noticed already that nothing was actually happened.

No need to feel defeated any more, as the solution is really simple, although unfortunately not 'obvious' at all:
simply right click the Public folder favorites folder shortcut that you created earlier, and then select 'Add to favorites' again and hey presto, the favorite is now going to show up in your main mailbox favorites area.