Bookmarking PDF documents in the Chrome browser

1. Bookmark a page by appending "#page=n" to the book's URL. E.g. "http://blah.pdf#page=23". The number, n, seems to be the page number from the first page (usually displayed as "n of m"). Useful for documents without a table of contents.
2. Set the zoom level by appending "zoom=x" to the document's URL. E.g. "http://blah.pdf#zoom=150". The number is a percentage, so "150" = "150%".
3. Combine the extensions using "&". E.g. "http://blah.pdf#page=23&zoom=150".

The parameters as the same as the command line switches for Adobe Acrobat (see below) but I was only interested page number and zoom: Parameters for Opening PDF Files

Internet Explorer 11 Compatibility View

Internet Explorer 11 has a "compatibility view" setting so that you can open web sites or applications that rely on quirks in older versions of the browser. However, you can only specify compatibility view for a top-level domain (i.e. test.com), not a sub-domain (i.e. xyz.test.com) or even a URL (i.e. http://test.com/app), so it's useless if your organization has quirks-mode and standard-mode applications. An alternative is to create a compatibility list in a group policy. We are transitioning away from quirks-mode but the interim is a PIA for users and administrators (who have to explain to users how MSIE works).

Formulas for Library Catalogue Sort Order

If you use library catalogues, you may notice that book or film titles in English are sorted without the leading article, "A", "An" and "The". For example, this list of titles ...

A Kind of Intimacy
An Awfully Big Adventure
The Girl In The Polka-Dot Dress

... is sorted like this in a library catalogue ...

An Awfully Big Adventure
The Girl In The Polka-Dot Dress
A Kind of Intimacy

To reproduce this sort order in a spreadsheet, create an additional column containing titles without the leading article then sort this column.

Kind of Intimacy
Awfully Big Adventure
Girl In The Polka-Dot Dress

If the title is in cell A1, the formula to transform a title in Excel is this nested IF() formula below, which tests for each possible article at the start of a string and returns the title without the article:

=if(left(A1,2)="A ",mid(A1,3,100),if(left(A1,3)="An ",mid(A1,4,100),if(left(A1,4)="The ",mid(A1,5,100),$B33)))

The equivalent formula is much shorter in Google Sheets because it has regular expressions formulas. The one below simply replaces the leading article in a title with an empty string:

=REGEXREPLACE(A1, "^(A|An|The) ", "")

Formula to Convert mm/dd/yyyy String to Date

Some data I receive has the date in a string in mm/dd/yyyy format, which is easier to sort or filter in Excel when converted to a date number. If the data is in cell A2 then the formulas for splitting the date string into substrings and creating a date value are:

String	Year	Month	Day	Date
5/31/2016	2016	5	31	31/05/2016

• Year: =RIGHT(A2,4). The year is always the last four digits.
• Month: =LEFT(A2,FIND("/",A2)-1). The month is the one or two digits before a forward slash.
• Day: =MID(A2,FIND("/",A2)+1,FIND("/",A2,4)-FIND("/",A2)-1). The day is the digits between the first and second forward slash.
• Date: =DATE(B2,C2,D2).

Probably the non-obvious bit is finding the second forward slash. The FIND() formula takes three arguments: find_text, within_text, start_num, where start_num is the position to start the search for find_text. Since the month and day numbers are always one or two digits, the second forward slash must be in position 4 ("m/d/yyyy"), 5 ("mm/d/yyyy", "m/dd/yyyy") or 6 ("mm/dd/yyyy"), so starting the search from position 4 will always find the position of the second forward slash.

Later ... I could just use Data, Text To Columns and split the date string into three columns using the forward slash as the delimiter.

Excel VBA "Code execution has been interrupted"

Very strange bug: After starting an Excel VBA macro, the macro stops and Excel displays a "Code execution has been interrupted" dialog. The only way to get the macro to finish is to keep pressing the "Continue" button. The problem only occurs on my development account not in other accounts. The solution is to wait for the dialog, press the "Debug" button to use VBE then type Ctrl+Break. Apparently, the issue is caused by Excel reactivating breakpoints in the macro from earlier debugging sessions. I guess that is why the macro runs to completion on other accounts. Hat tip to The two WORST Excel Errors EVER.

Google Play cannot download error 495

When downloading an application from Google Play while using mobile / cellular network, the download may fail and Google Play reports "error 495". In my case, the fix was to enable background data for the "Media" application (I restrict almost all applications from downloading background data on my phone).

1. Navigate to Settings, Data Usage.
2. Select the mobile operator tab.
3. Scroll down until you find "Media" application. If has "restricted" next to it, it is prevented from downloading background data, so enable it and try to install your application again.

Recursive preorder traversal of a folder tree in VBA

To process files in a folder tree using Excel, I implemented a recursive preorder traversal of a tree in VBA below. I haven't had to do this earlier because I usually use Gnu "find . -exec" to process files in folders.

Option Explicit

'References
'1. Microsoft Scripting Runtime

Global gFso As Scripting.FileSystemObject

Sub Main()
  Set gFso = New Scripting.FileSystemObject
  TraversePreorder "C:\KS Work\Temp"
End Sub

Sub TraversePreorder(ByVal sPath As String)
  Dim oFolder As Scripting.Folder, vMember As Variant
  
  If gFso.FolderExists(sPath) Then
    Debug.Print sPath 'Process folder
    Set oFolder = gFso.GetFolder(sPath)
    For Each vMember In oFolder.Files
      TraversePreorder vMember
    Next vMember
    For Each vMember In oFolder.SubFolders
      TraversePreorder vMember
    Next vMember
  Else
    Debug.Print sPath 'Process file
  End If

End Sub

Excel auto-fill keyboard "shortcut"

Excel's AutoFill is usually activated by selecting a group of cells and dragging the fill handle (the black square on the bottom-right hand corner of a selection) with the mouse until you have the range you want. The keyboard "shortcut" (more a sequence of keystrokes than a simple shortcut) is to select the range you want first then type: Alt+h,fi,s,Alt+f,Return. The breakdown of the steps are:

1. Select the range you want.
   
2. Alt+h: show the Home menu strip.
3. fi: show the Fill sub-menu.
   
4. s: show the Series dialog.
   
5. Alt+f: In the Series dialog, select Type=AutoFill.
   
6. Return: In the Series dialog, press the OK button.
   

SVG Clocks

Display UTC and local time using analogue clocks. This document uses Javascript and inline SVG code in an XHTML document. It can be viewed using a SVG-capable browser such as Chrome. I wrote this page nearly a decade ago and only ported it to Blogger.

Activation context generation failed ... HsxClient.dll

After a server patch, the HFM (Hyperion Financial Management) Windows client could not start. Below is the error message in Event Viewer.

SideBySide,
Activation context generation failed for "... HsxClient.dll". Dependent Assembly Microsoft.VC80.MFC,processorArchitecture="x86",publicKeyToken="1fc8b3b9a1e18e3b",type="win32",version="8.0.50727.4053" could not be found. Please use sxstrace.exe for detailed diagnosis.

Oracle Support suggested reinstalling VC++ Redistributable 2005. Searching for the version number brings up "Microsoft Visual C++ 2005 Service Pack 1 Redistributable Package ATL Security Update". Installing the x86 redistributable package solved the problem.

Excel cannot find the data you're searching for

Sometimes Excel cannot find text in a cell that is clearly visible. Some workarounds and solutions:

• In the "Find and Replace" dialog, untick "Match entire cell contents".
• In VBA, specify the LookAt parameter, .Find(..., lookat:=XlLookAt.xlPart)
• In your worksheet, remove custom formatting from the cell range you are searching.

The last workaround is pretty weird. One would think that formatting shouldn't affect searching for data!

Excel 2010 build multi-criteria search in Autofilter

Excel 2010's search in autofilter can be used to easily build a multi-criteria filter but I found that the steps for adding the first criteria is different from adding the subsequent criteria.

Assuming your started Autofilter for your worksheet, the steps to add the first criteria are:

1. Click on the filter icon in the column.
2. In Autofilter pane, enter your criteria.
3. Autofilter pane displays the reduced list, including your criteria (assuming it is found).
4. Untick "(Select All Search Results)". Excel removes the tick marks from the reduced list, including your criteria. If you don't untick this option, all the rows in your worksheet remain selected.
5. Don't tick "Add current selection to filter". If you tick this option, all the rows in your worksheet remain selected.
6. Tick your criteria.
7. Press the OK button to activate the filter.
8. Excel closes the Autofilter pane and displays only rows containing your first criteria.

To add more criteria to the filter, the steps are:

1. Click on the filter icon in the column.
2. In Autofilter pane, enter your next criteria.
3. Autofilter pane displays the reduced list, including your next criteria (assuming it is found).
4. Leave "(Select All Search Results)" ticked.
5. Tick "Add current selection to filter" to keep the rows previously filtered.
6. Leave your criteria ticked.
7. Press the OK button to update the filter.
8. Excel closes the Autofilter pane and displays only rows containing all your criteria (including the first criteria).

What is confusing is that you can't see your previous criteria in the Autofilter pane; you have to infer it from the list of visible rows.

Easily turn off and on your Android phone data connection

Easily turn off and on your Android phone data connection using a shortcut to the Data Usage page:

1. Navigate to Widgets, Settings shortcut.
2. Press and hold Settings shortcut until Android prompts you to drop the shortcut into a Home Screen.
3. After you drop the shortcut, Android prompts you to select a category. In this case, select Data usage. If you want to cancel the action, press the Back button.
4. Android creates a Data usage shortcut in the Home Screen.

Hyperion Financial Reporting book point of view

After changing a report dimension's point of view to "User point of view" in Hyperion Financial Reporting, check that books that include this report actually allows you to select values in this changed dimension. I found that these books need to be opened and resaved using the editor before I can select a member in that dimension.

Disable "useless" keys on a keyboard

I find the F1 (open online help) and Insert (toggle insert and overwrite mode) keys on a standard Windows keyboard useless; more often than not, I hit them accidentally and have to reverse their actions. If you use AutoHotkey, you can remap keys to do nothing.

F1::return
Insert::return

Smart View "Error" text in Excel cells

If your Excel worksheet has a lot of Smart View formulas (e.g. more than ten thousand HsGetValue), you may find that each cell with a Smart View formula shows "Error" after you refresh the worksheet. I think Excel encountered an error during the refresh process and does not update any of the cells. The workaround is to type Ctrl+Alt+F9 which "calculates all worksheets in all open workbooks, regardless of whether they have changed since the last calculation" (see Excel shortcut and function keys). This method of recalculation isn't visible in the Excel Formulas, Calculation menu.

Timestamps in yyyymmdd format using Visual Basic

I often use the "yyyymmdd" format for timestamps but the Microsoft FormatDateTime supports only available operating system formats and FormatNumber doesn't support left padding integers with zeros, so here's a little bit of code to get the date format that I want:

 dtNow = Now
 strToday = Year(dtNow) & Right("0" & Month(dtNow), 2) & Right("0" & Day(dtNow), 2)

The dtNow stores the current date to avoid the date changing just after midnight. The Right("0" & Month(dtNow), 2) trick ensures that the output always has two digits. I think I first saw this idiom here: VBScript How can I Format Date?.

OTRS user already exists

When adding a customer account to OTRS 3.0.11, you may encounter the "user already exists!" message but the customer account isn't visible in the Customer Management page. The problem is that the page doesn't indicate that you may have more than one page of customer accounts and there is no way to see more pages (i.e. no "Next" or "1, 2, 3 ..." links). The workaround is to restrict the search, e.g. if you are looking for "johnsmith", enter "john" or "smith" in the Search field.

Workaround to conditionally format for a range of cells in Excel

You can format cells in one column based on the value of cell in the same row in another column in Excel using conditional formatting but you have to workaround some quirks in the Excel Conditional Formatting Rules Manager (CFRM) user interface to get that result. Say that you want to format cells in column A based on the value of a cell in column B for the same row, i.e. A1 is depends on B1, A2 on B2, A3 on B3, etc. The steps for creating a conditional formatting rule for column A are as follows:

1. In Excel, select menu item Home, Conditional Formatting, Manage Rules.
2. In the CFRM dialog, press the New Rule button.
3. In the New Formatting Rule (NFR) dialog, select Use a formula to determine which cells to format.
4. In Format values where this formula is true, enter B1.
5. Set the format required.
6. Press the OK to close the NFR dialog.
7. Back in the CFRM dialog, you should see your new rule. In the Applies to field, enter $A:$A to use this rule for Column A. At this stage, if you press the OK button, the conditional format does not work for Column A. Use the following steps to workaround the
8. Press the Edit Rule button to display the Edit Formatting Rule (EFR) dialog.
9. In Format values where this formula is true, change ="B1" to =B1 (i.e. remove the double quotes) then press the button.
10. In the CFRM dialog, press the OK button.

After applying these steps, when a cell in Column B is TRUE, Excel applies the specified formatting to a cell in the same row in Column A.

Not automatically delete mail in Lotus Notes

After being spammed by "out of office" messages, I wanted to add a Lotus Notes (LN) rule to move these types of messages from my Inbox into the Trash folder. However, LN doesn't provide an action that moves messages to the Trash folder. Instead, the action that sort-of fits my requirement is to "Delete (don't accept message)" which I think means that LN will just delete these messages, sight unseen. I prefer to review my mail before permanently deleting them so my workaround is to create an action to "Move to Junk" then review the Junk folder before deleting messages.

Ref: Filtering new mail using rules