Archive for the ‘MS Office’ Category

h1

Excel Text Limit

02/04/2010

This post has now been migrated to ThoughtAsylum.com.

Follow this link to go directly to the article
.

h1

Excel 2007 Hyperlink Limitation

13/01/2010

For a couple of years now I’ve been pulling IT service desk statistics into a spreadsheet and setting up worksheets to break down and analyse the data in various ways.  The number of worksheets has subsequently grown a significant amount and I wanted to create a front worksheet that contained hyperlinks to and brief descriptions for each of the subsequent worksheets.

This is straight forward to do in Excel.  Press CTRL+K to bring up the hyperlink dialog and select the “Place in this document” set and then just select the worksheet you want the hyperlink to link to.  The issue I discovered was that this really does only apply to worksheets.  I had several chart sheets inserted into the workbook and these were not being listed in the hyperlink options.

As a result I’ve copied the charts from each of the chart sheets and pasted them as items on new worksheets.  An inelegant solution, but a practical one that doesn’t involve adding a macro to do the job.

h1

Finding the size of a Microsoft Office 2007 file

10/01/2010

Prior to Microsoft Office 2007 (MSO2007), office provided a quick and simple way to find out just how big your file was.  This was done by selecting the properties option from the file menu which then displayed the same file properties dialog box that you could select from the file in Windows Explorer.

In MSO2007 the information is still available, but has been annoyingly ‘hidden’ somewhat deeper in the application.  In fact it is so awkwardly hidden that I know many people have not come across how to access it … so I thought I’d share.

Along with the menu item click options I’ve included the accelerator / shortcut keys to help make this as quick as possible.

  1. Select the ‘Office button’ [ALT+F].
  2. Select the ‘Prepare’ menu item [Press E].
  3. Select the ‘Properties’ menu item [Press P].
  4. In the ‘Document Properties’ section that appears, click the title (“Document Properties”) of the section to reveal a drop down list containing just one item – ‘Advanced Properties’.  Select this item to display the file properties.

NB: Please keep in mind that until you save a file, the properties window will not provide a file size though other file properties may be populated.

h1

Get Value from the Text Function in Excel

07/01/2010

I’ve been doing a little bit more work with some spreadsheets recently and I came across a situation where I needed to work with a date and carry out a comparison to a string of text.  My first attempt fell short as it wouldn’t get the value I wanted, but Excel’s TEXT() function came to my rescue.

The issue came about like this.  I wanted to take a date in the format “dd/mm/yyyy” in one cell and display it in a short date format of “mmm-yy” in the cell next to it.

e.g.  “07/01/2010” in cell A1 and “Jan-10” in the neighbouring cell A2.

My original approach was to set the second cell to pick up the value of the first.  So taking the example above A1 would contain “07/01/2010” and cell A2 would contain a formula “=A1”.  I then set the formatting on cell A2 to a custom format “mmm-yy”.

I next wanted to check if cell A2 was equal to “Jan-10”.  This was where the issue arose as although cell A2 displays “Jan-10” its value is actually “07/01/2010″ … the value in cell A1.

The solution was to set the formatting of the A2 cell back to ‘general’ and then change its formula to… =TEXT(A1,”mmm-yy”)

The value of cell A2 is then “Jan-10”.

For me this highlights the difference between the content (what is typed into the cell, i.e. the formula), the value (what the formula evaluates to), and what is displayed (the value can be formatted to change how the user sees it on screen/printed).

h1

Top FlagIT Posts of 2009

01/01/2010

It’s been about a year since I started blogging here and whilst it was primarily supposed to be a site for quick useful tech-related posts several have grown into far larger posts.  Whilst some posts have remained somewhat quiet on the number of reads others have generated far more interest than I could have expected.

It’s actually five more days until this blog is a year old, but I thought that new year’s day might be as good an opportunity to review what the top ten posts were.  This has been based on the number of visits from more than six and a quarter thousand since the blog was created.

10. Reclaim more Memory on a Windows Mobile Device

Posted: 04/04/2009

Reads: 95 (Daily Average 0.35)

Windows mobile phones have a finite amount of memory and like any operating system (particularly M$ ones?) memory leaks can stop memory being released.  This post highlights how to resolve some storage issues with the Mobile Opera browser and a useful tool for reclaiming that memory lost to leakage.

9. Simple Template Workaround for Evernote

Posted: 21/11/2009

Reads: 95 (Daily Average 2.32)

A relatively recent post about one of my favourite pieces of software (Evernote) and how to use a simple bit of scripting to create templates in Evernote.

8. A Ceiling Function for VBScript

Posted: 31/07/2009

Reads: 133 (Daily Average 0.86)

This post includes some VBS code to round a number off to the greatest integer.

7. Developer Tabs and Drop Down Lists in Word 2007

Posted: 17/03/2009

Reads: 135 (Daily Average 0.47)

This post explains how to access the developer tab in Microsoft Word 2007 and briefly discusses some of the shortcomings of the drop down lists it offers.

6. VBScript – Count Occurrences in a Text String

Posted: 30/07/2009

Reads: 148 (Daily Average 0.95)

Another simple bit of VBS code that counts the number of instances of a string of text within another string of text.

5. Omnia to Oblivia (and back)

Posted: 05/04/2009

Reads: 151 (Daily Average 0.56)

I have a Samsung Omnia mobile phone and have had a number of issues with it.  This describes how I resolved one of the most difficult issues with the file system constantly mounting and dismounting a storage device.

4. A Flexible Progress Window in VBScript

Posted: 19/07/2009

Reads: 169 (Daily Average 1.02)

Another popular bit of VBS code is some that builds a customisable progress window based around dynamically updating a web page that is automatically loaded into a browser.  The progress window allows the user to display messages and progress bars.

3. VBScript – Select a Folder

Posted: 28/03/2009

Reads: 278 (Daily Average 1.00)

The most popular VBS code post on the blog in the past year this one shows how to access a common dialog to select a folder and pass this back to the main code.

2. Controlling Spotify

Posted: 29/07/2009

Reads: 1172 (Daily Average 7.51)

At this point we see a big change in the number of reads of the posts.  This particular post’s popularity is most likely related to the popularity of Spotify.  The post describes a way in which an iPhone emulator can be used on a PC running Microsofrt Windows PC to control Spotify installed on an Apple Mac.

1. Excel – Dynamic Drop Down Lists with Full Validation

Posted: 15/03/2009

Reads: 1272 (Daily Average 4.36)

At the top spot is a post relating to Microsoft Excel.  It looks at how to overcome some of the limitations on dynamically populated drop down lists.  There are a number of other Excel related posts on the blog that people may find useful in combination with this and it has certainly made quite an impact with some of the spreadsheets I’ve developed for colleagues at work.  It seems as though that popularity is spreading to hundreds of others across the Internet.

So please have a look through these top ten (and others if you like) and I’d encourage you to leave a comment or rate the post.  If you like the content why not subscribe to one of the RSS feeds?

h1

Multiple Monitors

08/12/2009

Several months ago I got a Dell Latitude E6400 laptop to replace my overworked Dell Latitude D410.  Along with the laptop I got a new advanced E-dock which included two monitor outputs, so I recycled an old smaller monitor that had been relegated to the stores to have a dual monitor set-up.

I find the two monitor set-up really useful and I frequently have different applications in different windows as you might expect.  However I struggled with Excel as I would frequently want to copy data between large format spreadsheets and I would have loved to have one on each screen … but they simply opened as child windows and I Alt-Tab’d between them.

I guess I never really stopped to think about it, but one day my subconscious kicked in and I realised that if I chose not to open the files directly I could open multiple instances and then open a file in each.  Then each instance of the application can be dragged to a different monitor.  Hey presto a spreadsheet in each window.

h1

Excel: Last Entry

28/08/2009

I was challenged to work out a little problem someone was having with Excel today.  The expectation was that I could write some VBA to resolve it, but once I found out what the task was I quickly managed to put together a purely Excel function based solution.

The task was to pick the last entry from a vertical list of cells.  The list is sequential so there are no blank lines which makes the choice of algorithm to find the last entry quite open.   The algorithm I chose was based on a list length and vector approach.

Example

Example

So to start with I needed to know how many entries were in the list.  The COUNTA function will provide this.  This in effect tells us how many rows we have to step down to get to the last entry.  The OFFSET function allows us to carry out these steps based on the first entry in the list.  Because we’re starting at the first entry then we don’t need to move if there is only one entry, so we need to subtract one from the count to give us the number of “steps” to take from the first cell.

The screen shot illustrates how this is built up for a list in column A starting at cell A1.  The function we would use is simply =OFFSET(A1,COUNTA(A:A)-1,0,1,1)

However this does give us a #REF! if the list has no entries – which looks like an error if you give someone a blank list (essentially because it is).  So if you need to account for this, just include an IF.

=IF(COUNT(A:A)=0,””,OFFSET(A1,COUNTA(A:A)-1,0,1,1))

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
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.