Archive for the ‘MS Office’ Category

h1

Presentations – Keeping your screen active

20/07/2009

Power saving options on computers are something that I personally think are great.  They not only help reduce carbon footprints, but they also help draw out even more life from your laptop battery.   One of the main ways in which this is done (others being things such as disabling hardware) is by having an inactivity option that turns off the screen and the hard disk after a period of inactivity.  Unfortunately there are times when this isn’t something you want.  The primary example of this is when you are giving a presentation.

The typical way of overriding this in Windows is to change what power saving scheme is being used through the Windows control panel or a third party application that links directly to these options.  However sometimes end users don’t have access to this.

This typically occurs where a group policy has been applied to stop users ‘tinkering’ with the power saving schemes.  Users with access can amend or delete power saving schemes which can lead to complications with machines and so it is not that uncommon to restrict access.

Whilst applications such as Microsoft PowerPoint can lead the OS into knowing that it should keep things like monitors on if a presentation is in a browser or some other “power unaware” application, then power saving actions can kick in.

I decided to apply a brute force solution to this by creating an AutoHotKey script to send a key press to the OS at regular intervals.  By default it sends a Shift key press as this is least likely to have any impact on any application, but this can be modified along with the frequency with which it is sent by the use of a settings file.  The settings file also includes an option to choose whether to start sending key presses as soon as it is run.

The code for the script is given at the end of this post, but I have also compiled this into a stand alone executable that you could even pop onto your flash drive if you’re going to be presenting on someone else’s PC.  Similarly you could add it into your start-up group (with it set not to auto start sending key presses) so it is always there when you need it.

The script places a monitor icon in the system tray.  When it is black power saving actions through inactivity will take place.  When it is blue, key presses will be sent.  Right clicking on the icon will display a menu with an override option.  Selecting this option will place a check mark next to it which will set the icon blue and initiate the override mode.  Selecting the option again will uncheck it, set the icon black and turn off the override mode.

Only one setting is available on the settings menu.  This is another check item and determines whether override mode should be enabled immediately when the utility is first started.

The utility looks for a settings file called ActiveDisplay.ini in the folder from which the utility is being run.  If it does not exist, the utility will use its default settings which match the settings given in this example file:

[Settings]
;Set to 1 to enable override mode at start-up and 0 to disable.
EnableOnRun=0
;Specify what characters should be sent.  Use {} for special key strokes
KeyStroke={SHIFT}
;Number of milliseconds between sending the key strokes (120,000 milliseconds : 2 minutes)
Period=120000

If you want to tweak the script to meet your own needs you can get the details below and use your own icons.  If you want to get the compiled version you can download it for free.

#Persistent
#SingleInstance

;Read in settings
iniread, EnableAtStartup, %A_ScriptDir%\ActiveDisplay.ini, Settings, EnableOnRun, 0
iniread, KeyStroke, %A_ScriptDir%\ActiveDisplay.ini, Settings, KeyStroke, {Shift}
iniread, Period, %A_ScriptDir%\ActiveDisplay.ini, Settings, Period, 120000

;Create the tray menu
menu, tray, add, Override, Override
Menu, SettingsMenu, add, EnabledAtStartup
Menu, tray, add, Settings, :SettingsMenu
menu, tray, add, About
menu, tray, add, Exit
menu, tray, nostandard

;Initialise
;Time to do something that seems crazy - we'll flip these and then call the menu selection routines where it will get flipped back
EnableTimer := EnableAtStartup
EnableAtStartup := Not(EnableAtStartup)
GoSub, EnabledAtStartup
EnableTimer := Not(EnableTimer)
GoSub, Override
return

;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;

SendKeyStroke:
Send %KeyStroke%
return

Override:
EnableTimer := Not(EnableTimer)
If EnableTimer
{
 menu, tray, Check, Override
 menu, tray, icon, C:\Icons\Monitor On.ico
 menu, tray, tip, Default Power Saving Mode Overridden
 SetTimer, SendKeyStroke, %Period%
}
Else
{
 menu, tray, UnCheck, Override
 menu, tray, icon, C:\Icons\Monitor Off.ico
 menu, tray, tip, Default Power Saving Mode
 SetTimer, SendKeyStroke, Off
}
return

EnabledAtStartup:
EnableAtStartup := Not(EnableAtStartup)
If EnableAtStartup
{
 menu, SettingsMenu, Check, EnabledAtStartup
 iniwrite, 1, %A_ScriptDir%\ActiveDisplay.ini, Settings, EnableOnRun
}
Else
{
 menu, SettingsMenu, UnCheck, EnabledAtStartup
 iniwrite, 0, %A_ScriptDir%\ActiveDisplay.ini, Settings, EnableOnRun
}
return

About:
MsgBox, 0, About Active Display, Version 1.0`nCopyright 2009 RebootIT`n`nhttps://flagit.wordpress.com
return

Exit:
ExitApp
Advertisements
h1

SharePoint Filenames Issue

14/05/2009

Today I came across an issue in Microsoft SharePoint Server 2007 (a.k.a. MOSS).  I was trying to upload some Microsoft PowerPoint slides into a slide library in a My Site and everything had gone fine with the first presentation, but the second one I tried came up with an error:

1 of 1 slides being published to ... failed

1 of 1 slides being published to ... failed

As you can see this is not a useful message as there’s simply no indication of why the failure occurred.

After a couple of minutes trawling the web I came across a TechNet article that suggested using explorer and uploading using WebDav might overcome this.  I published the slides to a local folder and set them copying to the library through explorer.  Unfortunately this didn’t work either.

An error occurre copying some or all of the selected files.

An error occurred copying some or all of the selected files.

Another meaningless message and still no further towards resolving the issue.

After another five minutes of further hands on and Internet free investigation I found the cause of the issue.  Apparently SharePoint is a bit particular about file names.  If a file name has two successive dots / periods in it, then it won’t allow you to upload it.  I checked this for document and image libraries too.  All libraries are lists in SharePoint, so I’m reasonably confident that it’s an issue across the board.

So if you have a file named “Dot2..dot.doc” for example would give you the issue but “Dot2.dot.doc” would work just fine.  So if you see an error like those shown above, check your filename and rename it if it has the two successive dots.

h1

Excel – Dynamic Named Range

27/04/2009

I’ve recently been helping some colleagues develop some reporting templates and they had a very particular requirement around drop down lists (which I’ll probably post about at some point in the future).  One issue that did occur however was that the dynamic lists that were incorporated into the workbook were added to and even though I had mentioned to the users about using Excel’s name manager to redefine the range of cells with that name, they forgot and inevitably the workbook would end up back with me to rectify.

Use name manager to define the dynamic range

Use name manager to define the dynamic range

I decided that I’d try and find a way of defining a range so that it would automatically extend itself when another cell entry was added to the bottom of the list.  It turned out to be more straight forward than I’d initially thought it might be.  The statement below sets a range that will begin at cell “A2” on worksheet “Sheet1” and extend down for the number of cells that contain a text entry.  It assumes that the list is unbroken and that the top cell is the name of the list (hence the -1after the “COUNTA”). Whilst the name is not essential I include it as having the name of the list in the top cell allows me to prgramatically carry out list operations by referencing the top cell in a column.

=OFFSET(Sheet1!$A$2,0,0,COUNTA(Sheet1!$A:$A)-1,1)

Example list

Example list

The screen shot on the right shows a sample list of colours that has a range defined by using the function above.  A drop down list is automatically populated from this list and is shown in cell B2.

h1

Overlaid Text Display Problem

07/04/2009

I came across a bizarre issue today in Microsoft Word. A user had a document containing a set of information laid out in a tabular format where the text in each cell was being condensed and overlaid so that the text was all being placed one character on top of another. The image shown here illustrates how “Hello world” looks when the characters are overlaid one on top of another.

compressed-font

Plain text and all the letters on top of one another

Copying and pasting the text out of a cell into the main body did not resolve the overlay issue.  Pasting the text into notepad did however prove that the text was indeed intact.  Therefore the issue was something that related to lay out.

There were no style or formatting settings that seemed to affect the character spacing in the way I expected so I then tried changing the font.  Originally it was set to Times New Roman, but changing it to another font restored the text to a legible format.  Changing it back to Times New Roman again incurred the overlay issue.  This suggested a font corruption.

I also tried adjusting the size of the text.  It then turned out that any size other than 12 pt resulted in the text being disaplyed correctly.  So anything but Times New Roman 12 pt was displayed correctly.

I had a quick look at the font file (found in the fonts applet in control panel in Windows XP) and the 12 pt size loked okay which suggested that it was something else affecting the way in which Word was laying it out on the page.  The page layout view was being used and this is built from the parameters of the current printer driver.

I changed the printer from the network HP printer located a few desks away to a CutePDF printer … and lo and behold the text appeared as normal.  I set it back to the HP printer and the overlay issue returned.  It seems a corruption in the printer driver was causing the issue.

I deleted the HP printer and then reinstalled it.  The text in the document was once again visible in Times New Roman 12 pt and the user also succeeded in printing out the document (which had previously suffered from the same issue as that seenon screen).

So if you have a bizarre layout issue in Microsoft Word that changing style based settings doesn’t seem to clear, try changing or reinstalling your printer.

h1

Developer Tabs and Drop Down Lists in Word 2007

17/03/2009

It seems that drop down lists are the in thing at work right about now.  One of my colleagues dropped by with a query about creating drop down lists in Word 2007.  So this is how to go about doing it…

First of all you need to ensure that the Developer tab is enabled in Word.  To do this you need to click on the Microsoft Office button (top left) and then on the Word options button at the bottom of the office menu that appears.  In the Popular section there is a list of check boxes about the “Top options for working with Word”.  One of these check boxes is called “Show developer tab in the ribbon” … this is the one that needs to be ticked.

With the developer tab enabled and selected the controls section on the ribbon is where we need to focus our attention.  The fourth button across on the top line is the one required to create the drop down list control – a.k.a. a combo box.  Clicking this button inserts a combo box at the current cursor position.

The next step is to fully define the combo box.  By default it has one entry in it – “Choose an item”.  Select the combo box and then select properties from the controls section on the ribbon.  Alternately you can enter design mode (from the controls section on the ribbon) and right click to get to the combo box properties.

The properties dialog provides a quick and easy way to not only add and remove items from the combo box’s list of selectable items, but also to name it something meaningful using the title.

Once you’re done editing the document, you then make it into a form by selecting “protect document” on the protect group on the developer ribbon and then selecting “Filling in forms” from the editing restrictions option.

The problem however seems to be that when you set the protection options in the properties of the combo box you can stop it from being deleted, you can stop it from being edited (at all!), but you can’t seem to stop it from having an option overtyped.  Seems a little bit odd and this is my one outstanding issue with the control.

h1

Excel – Special Names in Dynamic Lists

15/03/2009

I posted earlier about a better way for producing dynamic lists in Excel.  One of the “tricks” I used to simplify things was using the text values of the items in a primary list to identify a secondary list to use.

i.e.

Define the drop down lists

Define the drop down lists

Here We have three lists with the options set as named ranges called “Type”, “Fruit” and “Vegetable”.  Using data validation, drop down lists and Excel’s INDIRECT function means that if “Vegetable” is selected from a drop down linked to the “Type” list as a primary list, a related secondary list drop down will dynamically link to the “Vegetable” named range to validate the drop down.

This eliminates the need to do any complex lookups, but complex lookups look like the option when confronted with a primary list that contains entriess that would be invalid as names for a named range.

For example if we were looking at say UK regions and cities then an entry of “North Yorkshire” in the primary list would cause an issue as it contains a space which is an invalid character in an Excel name.

My solution is to replace the space in the naming of the range with an underscore.  This obviously won’t match to the entry in the primary list but we can use a quick substitution to accommodate this.

Simply put we might have something like in the data validation list entry for a cell linked to cell A2:

=INDIRECT(SUBSTITUTE(A2,” “,”_”))

This would simply replace the spaces with underscores and provide the valid name for Excel to use.

If we’re using the full validation option from my previous post on data validation, then this substitute would be moved into the function of the working area.  So using the same example as the previous post:

Cell D4 would now contain the following function:

=IF(B4=””,””,SUBSTITUTE(B4,” “,”_”))

Cell C4 would (still) contain the following for its data validation list source:

=INDIRECT(D4)

So we can still avoid complicated lookup functions and still keep things relatively simple and instantly readable.

h1

Excel – Dynamic Drop Down Lists with Full Validation

15/03/2009

This post has now been migrated to ThoughtAsylum.com.

Follow this link to go directly to the article.