2012-07-21

On Writing Scheduled Tasks for Windows AT Command

Less painful ways to write scheduled tasks for the Windows at command.

  • Use absolute paths to reference files or folder because at starts in a default folder, not the folder you are currently using.
  • Use caret (^) to escape the redirection characters so that you can combine the output (stdout) and error (stderr) streams to a file.

Here's an example that combines those tips: at 12:00 C:\Temp\Test.cmd ^> C:\Temp\Test.log 2^>^&1.

It is also be useful to dump the at command's environment variables so that you can check if the variables are set correctly (e.g. PATH): at 12:00 cmd /c set ^> D:\Temp\Env.txt.

2013/12/10: With Task Scheduler, you don't need to use the caret to escape the cmd metacharacters, i.e. Program = cmd and Arguments = /c C:\Temp\Test.cmd > C:\Temp\Test.log 2>&1.

2012-07-07

Excel Data Validation Drop Down Control Not Visible

A user found a strange problem in an Excel workbook: the data validation drop down control did not appear for any data validation cells in a specific worksheet (they appear in other worksheets). I tried the possible solutions but they did not work.

The solution came when I tried to resave the workbook as an Excel 97-2003 XLS file and the Excel compatibility checker reported some formulas were unavailable (in this case, SUMIFS) for earlier versions of Excel. After saving the workbook as an XLSX file to avoid this error, the data validation drop down control reappeared in the worksheet. It looks like opening an XLS file using Excel 2007 in compatibility mode may cause unexpected problems like this.

2012-06-30

(Slightly) Faster Typing with Standard Android Onscreen Keyboard

Found that you can enter digits and punctuation by using the touch-and-hold gesture on the standard Android onscreen keyboard. For example, hold down the q to get '1' or hold down the DOT to choose from a menu of common punctuation marks. This gesture makes it possible to type slightly faster since you do not need to change modes so frequently.

The touch-and-hold gesture also provides extra characters for keys in the number and symbol modes. For instance, 1 provides a choice of fractions with 1 as the numerator, $ provides common currency symbols and ( provides different left brackets.

2012-07-17: When three dots appear below the suggested words, touch-and-hold any of the suggested words to get a table of additional words.

If you read the Google's Use the keyboard guide, it mentions the touch-and-hold gesture but you might miss the usefulness of the feature since the guide does not go into detail or provide any examples.

PS. When I tried the touch-and-hold gesture on a physical BlackBerry Bold keyboard, I get capital letters, so that gesture is available on other mobile phone keyboards.

2012-06-23

Flatten or Collapse Excel Multi-column Data Into One Dimension

You can flatten or collapse multi-column Excel data into one row or column using GnuWin utilities.

  1. Copy the worksheet data into the clipboard.
  2. Open a CMD window and enter this chain of commands: getclip | tr -s [:cntrl:] \n | putclip.
  3. Paste the data back into your worksheet. You should get a column of data.

The getclip-putclip pair of programs gets and puts data in the system clipboard and is part of the CygWin package in GnuWin. tr translates control characters (e.g. TABs) to NEWLINEs and the -s option squeezes out repeated characters. This chain of commands works because each column of Excel data separated by a TAB character in the clipboard. Here are more examples of this pattern.

If you want to remove duplicates in your data, insert sort -u into the chain: getclip | tr -s [:cntrl:] \n | sort -u | putclip. For example, if you start with this input (note the trailing TABs in rows 1 and 2) ...

1  
2 4 
3 5 7
4 6 8
5 7 9

... you end up with the following:


1
2
3
4
5
6
7
8
9

If you want a single row output, replace all control characters with TABs: getclip | tr -s [:cntrl:] \t | putclip. In this case, you cannot include sort because it sorts lines of data and there is only one line in the output.

1 2 4 3 5 7 4 6 8 5 7 9