OfficeTipsAndMethods

One of the more useful ‘little enhancements’ in Windows 7 is the search box at bottom of the Start menu. (Keep in mind that I am writing about my first impressions of Windows 7 after switching from Windows XP Pro. Some of the features mentioned in these articles may well have been introduced with Vista but I skipped Vista and went directly to Windows 7 when it was time for a new operating system and computer.)

The Start Menu search box provides another way to search for your digital content, whether the content you are trying to find is an application (program) or a document (`data`file.) You don’t have to remember the application you used to create the file (if it is a document) or even the full name of what you are looking for. In fact you can even search for a particular phrase.

You will see the search box at the bottom or the start menu, when you click the Start Button. Simply click into the search box and start to type the first few letters of the name of the item you are looking for.

Can’t recall the actual name? All is not lost  as long as you can recall at least part of the name or a distinctive phrase in the document’s content.

Type in what you recall. Windows immediately starts looking for matches in its indexes. The more characters you type, the more refined the search will be.

Here’s an example. I did some work a while ago on a demonstration of the critical path for a project management seminar I facilitate. There were several files involved but I couldn’t recall their exact names. Don’t worry if you don’t know what a critical path is. The point here is that I was able to use the search box to track down the files I needed.

In the graphic, you can see that I typed ‘critical path’ into the search box. Notice how the search utility has returned a list of several documents.

The list includes a folder I created to house the relevant files, an html (web page) file, some Excel files, and two OneNote notebooks which made some reference to my search phrase, ‘critical path.’ Some of the document names include the phrase, ‘critical path.’ Some do not, but somewhere in these documents, the phrase ‘critical path’ appears and so they appear in the list of search results.

In Windows 7 – Libraries, I discussed the virtual explosion in electronic storage capacity. The downside of large storage capacity is that it is easy to save a file one day and then not recall where you located it when you need to work with it several days, weeks, or months later. Try the search box the next time a file eludes you. It will quickly become a valued friend.

In Windows XP you could pin shortcuts to your favourite applications to the start menu. Windows 7 has taken that concept a couple of steps further. I’m lumping the discussion of this set of Windows enhancement under the title Pinning Applications to the Task Bar because I became aware of that capability before I found that Start Menu shortcuts share some features with taskbar shortcuts.

Windows XP had a Recent Documents folder on the Start menu. Well-behaved applications added the name of each document you worked on with the application. That way it was possible to re-open recent work without a lot of hunting. Recent Documents worked well enough, unless, you worked a larger number of different of, say, Word documents, regularly and perhaps only the occasional Excel Workbook.  In situations like that, the Excel Workbook may have been forced off the Recent Documents list because the list contained a large number of Word and other non-excel entries. In that case, it took just a bit longer to open the file you want to work with because you would first have to open the application and then locate the document you want in the application’s internal recent documents list.

In Windows 7, if you pin an application to the task bar, you will always see a shortcut to the application on the task bar. You don’t have to have it pinned to the start menu (although you can, if you wish). The secret lies in right clicking the application icon on the Task Bar.

The graphic shows the shortcut menu I get when I right-click the Excel icon that I have pinned to the taskbar. Notice that the list is divided into three parts. The recent section lists the last few Excel workbooks I have worked on. Just as with the Windows XP Recent Documents folder, this list can fill up so that if I work on enough different workbooks, the one I want may no longer appear in this list.

That’s where the top section comes into play. Right now, I have three workbooks pinned to My Excel recent documents list. I edit the Spammers one almost daily so it is not likely to disappear. The other two, on the other had, I may not need to work with for extended periods. I have pinned them to this menu by right-clicking on the one I want and selecting Pin to the list. You don’t even need to right click an entry to pin it. When you point you mouse at an entry, you will see a push-pin icon on the right. Click that icon and the document is immediately pinned to the list.

In my opinion, this feature is a great innovation. Instead of having a single Recent Documents folder, Windows 7 gives you separate recent document lists, each dedicated to a single application.

This idea carries over to the Start Menu. However, instead of requiring you to right click an application icon, the list is available as a cascaded menu that appears when you hover over a Start menu item. Look of the little black triangle to the right of the application name.

Here is a graphic of my Start Menu where I have hovered my mouse over the Microsoft Excel entry. Notice that this is the same recent documents list that I opened by right-clicking the Excel taskbar icon.

On a document by document basis, the application pinning and dedicated recent documents lists feature of Windows 7 won’t save you much time per document. But if you work on a large number of different documents from a variety of applications, those tiny per document savings can add up leaving your more time to do productive work on your documents and less time just trying to track theme down so you can work on them.

Electronic Storage is More Affordable than Ever

Personal data storage costs have plummeted over the years. I recall receiving a promotional flyer in 1985 promoting a 5 Megabyte hard drive for $5,000.00. In today’s dollars that price translates to a bit over $9200.00 according to the Bank of Canada Inflation Calculator. If you convert that price to a cost per 1000 bytes, it works out to about $1.84. Okay, so what does that mean in everyday terms?

Since a byte is roughly equivalent to the storage space required for a single character, and the nominal average size of a word in the English language is 5 characters, you could store roughly 200 words for that $1.84 in that 1985 megabyte drive. So, for you students the cost to store a 2000 word essay would be just over $18.00

Fast forward to 2010. These days, the storage capacities of hard drives are frequently expressed in terms of gigabytes and terabytes. A one terabyte drive has roughly 200,000 times the storage capacity of that one 5 megabyte drive. (My math skills fail me when I am working with such large numbers but I think I am in the right neighbourhood. Terabyte drives are now available retail at prices in the $200.00 (and less) range.

Using the $200.00 price, that means that the 2000 word essay that once would have cost about $18.00 to store, now costs a tiny fraction of a penny. A 200,000 word thesis would have a storage cost of around 4 cents.

So what does all of this have to do with Windows 7. Because data storage is dirt cheap (sorry there is no other word for it) we tend to store more and more documents electronically. Many people are store vast amounts of music, video, and still photographic images, desktop publications, artwork, project plans, spreadsheet, databases, and much, much more. In short, many people are storing large numbers of documents.

Finding What You Need – Staying Organized

Well, storing all this ‘data’ is find but the stored files are absolutely worthless unless you can find them, quickly and when you need them. And that’s where Windows 7 Libraries come into play.

Quote of the Day:

If you have built castles in the air, your work need not be lost; that is where they should be. Now put the foundations under them.

-Henry David Thoreau

Most windows users are at least somewhat familiar with the folder system and concept that goes back to Windows 95. To all intents and purposes, documents are stored in folders. Folders themselves may be stored in other folders. Think of the hard drive as a huge file storage warehouse. In that warehouse, there are many file storage rooms. In Windows terms, these rooms are referred to as top-level folders. Inside each of these rooms are filing cabinets (another layer of folders). Inside each filing cabinet are filing drawers (yet another layer of folders) and inside each of these folders are individual documents.

The analogy breaks down a little once you get to the filing drawer level of the physical example. You wouldn’t have a filing drawer within a filing drawer but in the virtual model any folder can theoretically contain yet more folders.

How I Organize My Documents

There are a number of possible ways that you can organize your files. Systematic organization is the key to finding that elusive Word document, or Excel workbook when you need it. The system I’m going to describe works for me. You may have a different approach. If that approach works for you, great! After I describe my current system, I will talk about how Libraries in Windows 7 helps me keep my current system yet makes it easier to find and use the important files I have stored.

I use the Documents (Windows 95 – XP, My Documents) folder as the central location for all my files. Within the documents folder, I have one folder for each type of activity that I am involved with. Part of my work involves the development of custom Access databases and Excel workbooks for clients. My business name is Argee Services so I have one folder in the Documents folder named Argee Development.

Now, when I start to work on a project for a new client. I will create a folder within the Argee Development folder, using the Client’s business name as the folder name. Within that folder, I have one folder for each project I have developed or am working on for that client. Each project folder will have a similar set of folders and documents, including an archive folder for saving interim versions of the project as development proceeds. 

Getting There Isn’t Half the Fun – Windows 7 Libraries to the Rescue

While the system I have described helps keep my client files organized, using the File Open dialogue to locate and open a particular file can be a bit tedious to say the least. I need the files for work in progress project to be readily found. There is less urgency associated with completed projects. In earlier versions of windows, I had a folder named Work in Progress and that folder would contain shortcuts to current projects.

That meant having to create a shortcut to the project and then move the shortcut to the Work in Progress folder. Windows 7 Libraries make this process easier to manage.

Now I have a library named (you guessed it!) Work in Progress. When I start a new project, I add the project folder to the Work in Progress folder. The actual project folder remains ‘physically’ in the Argee Development\Client folder structure. But in the Work in Progress library the project is a top level folder. Working with this virtual folder ‘feels’ exactly like working with the actual folder that ‘lives’ somewhere else in my computer system.

To me, the Libraries concept resembles the Clipart Organizer and Windows Media Player playlists in previous Windows versions. Neither the Clipart Organizer nor the Media Player stored any actual files. A Media Player playlist was actually just a list of paths to the media files. So a playlist could include media files from several different Windows folders.

Similarly, a Windows 7 Library does not contain real folders. It contains shortcuts to the real folders that you want to include in the library. The icon is a folder icon, so it ‘feels’ like you are working with the actual folder. Once you have added a folder to a library, you don’t have give any thought to the actual location of the folder. Just use the library to find the folders and its files when you need to work with them.

Released in October ‘09, Windows 7 isn’t exactly ‘hot off the press news’ but I’d like to share with you my initial impressions of Microsoft’s current operating system offering. I have been using Windows 7 on a daily basis for about the last three weeks. Quite frankly, I am very impressed despite one or two drawbacks.

Let’s start with the positives. Features I really like include (in no particular order):

• Libraries
• Start Menu Search Box
• Application Pinning to the Task Bar
• Document Pinning to Application Task Bar Icons
• Open Window Preview Thumbnails

Quote of the day:

Opportunity is missed by most people because it is dressed in overalls and looks like work.

- Thomas A. Edison

Feature that is a pain, but probably necessary

• User Account Control Settings

… and the negatives (in order of personal impact):

• Networking with non-Windows 7 Machines
• Device Incompatibility

In my next few articles, I will discuss the reasons I like or dislike these features and the impact they have on my daily computing

  Since I “discovered” OneNote in the Office 2007 suite, it has become an essential tool for me on a daily basis. Microsoft introduced the application with Office 2003 but I completely missed it there.

How does OneNote work for me? Read my article posted on Tuesday in the MVP Award Program Blog and in the Office Blog.

All I can say is, if you haven’t used OneNote yet, what’s keeping you?

Unhide Row A or Column 1

You may recall this article that talked about how to recover Row A or Column 1 if you have hidden them. Yesteray, Deborah Dalgleish in her Contextures Blog  shows an even easier way to do select a hidden first row or column. Have a look at her short video in this article.

Excel on TechRepublic

Along with this article you will find some excellent links for productivity boosting Excel shortcut key. Well worth a visit. You may need to take out a free membership to view or download some of the articles.

Quote of the Day

The future is an opaque mirror. Anyone who tries to look into it sees nothing but the dim outlines of an old and worried face.

 

- Jim Bishop

Excel, PowerPoint, and Word 2007 all have Recent Documents lists on the Office Menu. You can set an option for the number (up to 50) of document titles to be displayed on this list. It’s a handy way of getting back to documents that you have recently worked on. You don’t need to remember where the document is. Just click the name  in the Recent Documents list.

Quote of the Day

Ultimately, the only power to which man should aspire is that which he exercises over himself.

– Elie Wiesel

Even with the option set to retain 50 documents on the list, however, a document that you use only occasionally may be forced off the list if you work with a large number of different documents. Tracking down files that you use only once in a while can be challenging, to say the least.  Did you know that you can mark a document name so that it always stays on the list?

To the right of each document name, there is a grey pushpin icon. If you click that icon, the icon will change so that it looks like it is pushed into a corkboard and the colour will change to green. A document name with the green pushpin will always stay on the Recent Documents list, regardless of how many other documents you open and close or how long since you have opened the document.

The name will not always be on the top but it will be in the Recent Documents list. So, if you only open that Greeting Card list once a year and want to find it easily next year, click its pushpin icon on the Office Menu. The next time you go to open it, that once a year document will probably be at the bottom of the list but it will still be there waiting for you to click its name so you can get ready to send next years cards.

Windows Live Sky Drive is about to get better!

Talk about coincidence. A few days ago I needed to set up an Excel workbook to collect some basic information from several people scattered around the globe. We are working on a project involving the transfer and update of several hundred small documents from one World Wide Web site to a related but different site.  Because we need to make small changes to the documents in the process, they need us to work on them one at a time. The problem some of us saw, of course, was how to keep each other informed about which documents we were individually working on at the moment, which were completed, and which remained to be worked on and moved.

The tracking method had to be simple and accessible to all of us regardless of where we live in the world. Our timing is just a little off it seems because Office 2010 is in the wings, about to be released and it seems to me that and Access 2010 Web Application would be an excellent solution.

Two things prevented me from trying that approach, however. First, we needed to start the project sooner rather than later. Second, I am not up to speed with Access Web Applications yet. Fortunately, an alternate solution that we can apply in the meantime does exist: an Excel workbook hosted in Office Live Workspace.

Quote of the Day

Education is when you read the fine print. Experience is what you get if you don’t.

-Pete Seeger

Office Live has been available for several years. This service allows you to  store and share documents online. So once I had set up a workbook with the document lists for our team all I had to do was save the file to my Office Live account in a folder for the project. Then I used the sharing feature to add each of the team members as an editor for the project folder.

We have been using the workbook for several days now. To each of us, it appears that it ‘lives’ on our local system and that’s because we are doing the actual editing in our respective local Excel installations. Here’s how it works. John decides to work on document A. He goes into Excel and opens the Office Live workbook. If he isn’t logged into the Office Live account, he will have to enter his user name and password. Then except for a brief pause while the file downloads, the workbook opens in John’s copy of Excel.

He then finds the title of the document he intends to work with, marks an X in the In Progress column and closes the workbook. Of course he answers yes to the Save dialogue to save the document.

A few minutes later, as luck would have it, Mary decides to work on the same document that John is now working on. When Mary opens the Office Live workbook and finds the document title, she will see John’s X in the In Progress column. Knowing that someone else is working on the document, she can choose a different one and record that she is now working on that one.

Now, the coincidence that I mentioned at the beginning of this article isn’t about John and Mary deciding to work on the same piece at the same time. That’s a coincidence all right but an even more significant one happened for me this morning. I had already decided to write this article. Then in my email this morning I receive an RSS copy of this blog article from the Microsoft Office 2010 Engineering team, Accessing your Office files from any computer with Windows Live SkyDrive. This article links to a Excel oriented article, Collaborative Editing Using Excel Web App.

So it seems that the kind of document collaboration using Office Live Workspace that I had set up is about to get even simpler. I should explain that the Web App approach will be using SkyDrive, a Windows Live Service. The system I described uses Office Live Workspace. I have had both Office Live Workspace and SkyDrive accounts for some time but until now, I used the SkyDrive account simply for files that I wanted to make available for others to download. Soon, I will be able to use my SkyDrive for collaborative documents as well.

New at the Access Wiki

Access Wiki moderators are hard at work moving UA Forums FAQ and code archive content to the wiki. This will add many new articles to the Wiki’s growing content. There are some 850 code archive items alone. Since UA started in 2002 there have been more than 1,940,000 view and download of code archive content.

The Microsoft Office 2010 Engineering Blog has announced a May 12, 2010 worldwide launch date for “the 2010 set of products, including Office 2010, SharePoint 2010, Visio 2010, and Project 2010” for business users. Office 2010 will be available for consumers on-line and in retail outlets in June 2010.  If you want to have an sneak preview look at what is coming you can download the beta at www.office.com/beta.

I have been poking around the Access 2010 beta for the last while and I think it is going to be a nice version to work with. In case you are wondering, the Ribbon stays. In fact, I believe that the Fluent User Interface as the Ribbon is officially, will now be found throughout the Office suite. The Office Button has been replaced with a File tab on the ribbon. This tab leads to the backstage of each application.

Quote of the Day

Perhaps the most valuable result of all education is the ability to make yourself do the thing you have to do, when it ought to be done, whether you like it or not; it is the first lesson that ought to be learned; and however early a man’s training begins, it is probably the last lesson that he learns thoroughly.

- Thomas H. Huxley

The single-most exciting new development as far as Access is concerned is that it is now possible to develop Access Web Databases and publish them to SharePoint. Ryan McMinn present this new development in this video.

If you are interested in general Office development, have a look at John Durant’s presentation.

Pej Javaheri and Steve Tullis present Excel and Excel Services 2010 developments in this video.

All in all , Office 2010 looks pretty exciting with lots of new ‘stuff’ to learn but with big paybacks for taking the time you will spend on learning.

As a design principle, I apply a naming convention when creating new database objects. For example, field names begin with a lowercase letter. Recently, I was putting together a small application in Access 2010 beta so that  I could gain a little familiarity with the new Access version that will be released later this year. In a weak moment, I accidentally named some of the fields in a new table with uppercase first letters.

In the grand scheme of things, this is not a particularly serious problem but inconsistently formed names lend an unprofessional look to an application. When I tried to correct the names I discovered an inconvenient quirk. After replacing the first letter of each field name with its lowercase equivalent, saving and closing the table and then re-opening it again in design view, I found that the first letter of each field name had changed back to uppercase.

No matter what I tried, I couldn’t get the lower-case letter to ‘stick.’ That’s when I turned to my favourite forum for help. Thanks to UtterAccess VIP member (and Access MVP) datAdrenaline, I quickly had a reasonably workable solution.

Quote of the Day

I have never been especially impressed by the heroics of people who are convinced they are about to change the world. I am more awed by those who struggle to make one small difference after another.

- Ellen Goodman

All I had to do is change the first letter of each field name I wanted to modify, save and close the table, re-open the table and replace whatever I had entered as the first letter of the temporary field name back to the lowercase letter with which I wanted to start the name. Step by step this is the method I applied:

• open the table in design view
• pre-fix each field name with a single letter
• delete and replace the original uppercased letter
• save and close the table
• reopen the table in design view
• remove the leading character for each field to correct name
• save and close the table

This problem occurred specifically in Access 2010 beta so it may be a non-issue when the new version is released but if you run into similar situations perhaps a similar approach will help you out of a bind “when all else fails.”

New articles in Access Wiki

Links

CamelCase

Access: What is it?