Monday, October 25, 2010

Windows 7 Streamline Start Menus

Howto: Windows 7, Windows Explorer Settings and how to streamline the Start Menu. Tutorial showing how to simplify and de-clutter the Start Menu. The second half of the article describes other Windows settings of interest. This tutorial covers many inter-related items and will take time to study.

See these related articles:
Windows 7 Explorer Settings
Windows 7 - Stop UAC Nags while editing Icons
Cleaning Startup Programs


Windows XP and Vista users should jump to this article: Streamline Windows XP / Vista Start Menu.
Contents:
  • Simplify the Side Menu
  • Recommended Explorer Changes
  • Cleaning up, tidy up Start Menus
  • Create a UTIL folder
  • Deleting Unneeded Folders
  • "Above the Line" Pin Permanent icons
  • Pin Documents to the Start Menu

1. Simplify the Side-menu:


Simplify the Start-button's right-side sub-menu, by removing those items you do not need. For example, I save all data, music, photos, in C:\Data (rather than in my profile's "MyDocuments" folder -- this way other users on the same machine can access the files). Because of this, I do not need the My Documents, My Video, My Pictures, etc. You may need some folders, but not others.


a. Click Start, Control Panel, "Task Bar and Start Menu"

In the Start Menu tab, click "Customize"
In the vertical list, consider (unchecking/aka "Don't Display this item") the following items.

Pick and choose the ones you want to hide. For example, on my systems, all data is stored in C:\Data (not in MY Documents). Because of this, I hide almost all shortcut menus. See the bottom of this article for more discussions on C:\Data.

= Documents (if you use C:\Data) - Don't display this item
= Favorites - Don't display this item
= Music - Don't display this item
= Personal Folder - Don't display this item
= Pictures - Don't display this item
= Videos / Recorded TV - Don't display this item

In each case, pick and choose the ones you want to remove.
Click OK, OK when done.
Click on the Start Menu to see the results.

2. Recommended Windows Explorer Changes

Click Start, Control Panel, "Task Bar and Start Menu"
In the Start Menu tab, click "Customize" (same menu as above or Other-mouse-click Start Menu, Choose Properties, [Start Menu], Customize)

These changes are recommended and make the following steps easier:

where [ ] means uncheck and [X] means check:
  • [ ] "Help" (Uncheck; declutters the Start Menu)
  • [ ] "Default Programs" (Uncheck)
  • [X] "Run" (Expose the Run command
  • [ ] "Highlight Newly Installed Programs" (Uncheck)


3. Clean up and Organize the Start Menu:

Simplify your life and consolidate or delete unneeded programs and groups from the Start Menu. The illustration below shows a relatively cluttered Start Menu; yours is likely far worse. Notice it is long enough to require a scroll-bar.

"Before"

4. Begin the Cleanup
a. Open Windows Explorer. Tunnel to this path (these items are visible because of the Explorer changes made previously):

C:\ProgramData\Microsoft\Windows\Start Menu


(click illustrations for a larger view; click right-x to return)

b. Delete icon "Default Programs" (you will never use it)
c. Delete icon "Windows Update" (it is in the Control Panel)
d. Leave "desktop.ini"

5. Build Utility Folders

Create a new UTILITY folder by following these steps

a. Using Windows Explorer, tunnel to (same location as above)
C:\ProgramData\Microsoft\Windows\Start Menu\Programs
b. In the "Programs" folder (detail side), create a new "Util" (Utility) sub-folder by 'other-mouse-clicking" in the detail-side, choose "New Folder"
Name: "Util"

This is where you will put seldom-needed utilities and system tools. While in the area, notice the 'Programs\Accessories' Folder.


c. Using Windows Explorer, *Cut and Paste* the following common, but seldom-if-ever-clicked utilities to the UTIL folder. From the Windows Explorer detail-side, highlight these program icons and cut-and-paste them into the UTIL folder (if present):
  • Adobe (Acrobat) Reader
  • Windows Anytime Upgrade
  • Microsoft Security Essentials
  • Any other Virus software
  • Microsoft Silverlight

Similarly, highlight, Cut and Paste these icons, placing them into the Accessories folder:
  • Windows DVD Maker
  • Windows Fax and Scan
  • Windows Media Center
  • XPS Viewer
  • PowerPoint Viewer
  • QuickTime Viewer

My rule is this: If it is a geeky utility, one that your spouse our children would never want to see, put it in the UTIL folder. Other programs, such as Acrobat, PPT Viewers, etc, which are needed, but never directly clicked, put them in the ACCESSORIES folder. I consider Accessories "end-user" tools. The goal is to tuck them out of the way.

6. Move other (vendor) folders...:

With an eye towards other folders in the Programs group, ask yourself if they are really needed. While still in the "Programs" folder, locate the following subfolders and icons. If they seem useless or useful-but-never-clicked, consider moving them to Util or Accessories folders.

Examples to move into UTIL:
  • Administrative Tools
  • IIS 7.0 Extensions
  • Intel Storage Matrix Manager
  • Microsoft Mouse
  • Microsoft Security Essentials
  • Microsoft Silverlight
  • Maintenance
  • Nvidia Corporation
  • Tablet PC
  • Dell Support Center
  • Any Backup Programs you may have purchased
Examples to move into Accessories:
  • Dell Webcam Manager
  • Desktop Gadget Gallery
  • Creative Webcam
  • Adobe Acrobat Reader (icon or folders)
  • Roxio
  • Windows DVD Maker (icon)
  • Windows Fax and Scan (icon)
  • Windows Media / Media Center (if you don't use this)
  • Windows Anytime Upgrade
With each, cut-and-paste from the main "Programs" area into either UTIL or Accessories. You can, if desired, copy either the entire folder or just the icons within. Do not worry, the cutting and pasting (or even deleting) does not harm the installed programs.

Reasoning: You will never click the Acrobat viewer to open a PDF; instead, you will always double-click the PDF; the viewer opens automatically. Tuck these other seldom-used programs in a location that is out-of-the-way. Or, better yet, delete them; see below.


7. Get Rid of Unneeded Folders:

Folders, such as "Microsoft Office" or "Corel Paint Shop Pro Photo X2" do nothing but waste your time, making you click through a useless folder. The folders are often little more than an advertisement. What you really want is the program-application icon that lives inside.

Consider these example folders, such as "Corel Paint Shop Pro", "Microsoft Office", "WordPerfect Office". Once the single-executable icon within these folders is moved to a better location, the entire folder can be deleted -- removing another folder cluttering the Start Menu:


Deleting icons does not hurt the underlying features.

Steps:
a. Using Windows Explorer, tunnel to the "Programs" folder (same as before).
C:\ProgramData\Microsoft\Windows\Start Menu\Programs

b. Open Folder "Microsoft Office" (or any other application folder of interest)

c. Copy only the icons you use (e.g. "Paint Shop Pro" or "Microsoft Excel") and paste them one folder above, into the "Programs" folder. Don't bother copying Help Icons, Registration, or others that you have never clicked.

These icons will appear in the root of the ALL PROGRAMS menu. (See below for how to "pin" a copy on the Start Menu itself)


d. Test by clicking Start, All Programs. You will see these icons at the root of the folder.

e. Delete, yes delete, the original holding-folders. For example, delete the entire Microsoft Office Folder, WordPerfect Office, etc. -- Once you have saved the icons you care about, do not be afraid to delete those useless folder structures.

f. Consider moving some icons from the Accessories folder to the Programs folder, making them easier to find and more accessible:
  • Calculator
  • Paint
  • Snipping Tools
  • Sticky Notes

Another example of a corporate advertising folder: Acronis True Image Backup - the Shame

Here is the Acronis True Image Backup StartMenu folder-structure. Notice how they insulted you twice by tucking their most needed icon two-layers deep. Inside is only one icon that is of interest -- the main program icon. The rest of the icons are available from within the program (e.g. Help, Register, etc.).

Copy or cut the icon and paste it into the Programs folder (or Util). Delete the extraneous folders and all of the icons within:


You will find things like Help, Websites, Registration Links, Uninstall icons.
Be merciless and delete this crap.

Here is my machine after the cleanup with a much shorter "All Programs" list. Notice there is no vertical scroll bar. Click illustration for a larger view; click Back to return.


"After"

Deleting useless folders without fear. Delete things like Help, Uninstall, etc. Most of these functions are available from within the application or a control panel; there is no need to hog space in the menu system. Consolidate those icons you want to keep in Util or Accessories.

Here is Windows Explorer view of the same cleanup:



8. Permanent Icons: Pin

Continue by taking your most common, favorite applications and adding them to the first Start menu. With Windows 7, adding a program or icon permanently on the initial Start Menu is easy with a "Pin".

Icons can be added "above the line" in the Start Menu -- giving a quick link to your most commonly-used programs. This is faster than clicking the Start Menu, Programs. With this, all you need to do is click Start.

Benefits
  • Fixed, non-changing list
  • One click from the Start Menu
  • Place your most needed programs within easy reach
  • No need to click "All Programs"
By default, when you launch any application, Windows 7 tracks the most-recently-used programs and places them in the first Start Menu. This list can seem random and the entries can shift and change. You may prefer a more predictable list and you may want to get to this list quickly, without having to click "All Programs."

In older versions of Windows, these icons would have been placed manually in the Start Menu, and this was commonly called "above the line." In Windows 7, this feature has been automated with the PIN menus. In the illustration, see how WordPerfect, PaintshopPro, Excel, Notepad and Access are all clearly visible in the Start Menu:


To add a program to the Start Menu, do the following:

a. Locate any program or icon on the Desktop or in an existing Start Menu. For example, WordPerfect or Microsoft Excel.

b. 'Other-Mouse-Click' and choose "Pin to the Start Menu"

c. To test, click Start.

Another illustration showing the same idea:


Note the new program(s) is listed at the top. You do not need to click "All Programs." The most commonly-used applications are one-click away from the Start Menu. Only place your most common applications in this list to avoid clutter.


9. Roaming User Icons (optional discussion)

Some manually-created (desktop) icons are not visible to other user-accounts, or when you look in the "Programs" folder, you don't see the icons there, but they are visible in the Start Menu.

These steps are useful when you create "document and data icons" and wonder where they went.

Windows keeps track of two sets of Start Menu icons: One for the 'All User/machine' and a second for the 'current user.' The Start Menu blends the two into one list.

In general, I recommend moving icons from the Current (Roaming) user to the All User's (machine) list; this way they are visible to other users on the same machine.

Click illustration for a larger view; click right-x to return

To Move the Icons from the Roaming Start Menu to the Machine Start Menu:

a. Tunnel Windows Explorer to

Users\(your userid)\AppData\Roaming\Microsoft\Windows\Start Menu\Programs

(Any icons you find in here are only visible to your login-id and many of these icons should be stored with the others built earlier.)

b. Locate, cut and paste these recommended icons:
Paste into (it helps to open an second Explorer window)

C:\ProgramData\Microsoft\Windows\Start Menu\Programs
  • "Command Prompt" to Util
  • "Notepad" to Accessories
  • "Narrator" to Accessories
  • "Magnifier" to Accessories
c. Then delete these folders from the Roaming Profile Start Menu:
  • Accessories (the entire folder)
  • Administrative Tools
  • Games
  • Maintenance
  • You must leave "Startup" and "desktop.ini"
Don't worry about deleting the Accessories folder -- there are two of them, one for the machine and one for your account and the Start Menu blends the two. Because of this, there is no harm in deleting the Roaming profile version.

10. Cleanup the Default Users Folder (optional)

Now that these icons have moved to the (All Users / Machine Start Menu), stop this pollution from bothering any other new User Account by making these strictly-optional changes:

a. Tunnel to

Users\default\AppData\Roaming\Microsoft\Windows\Start Menu\Programs

b. Delete folder "Accessories"
c. Delete folder "Maintenance"
d. Leave folder "Programs"
e. Leave folder "Startup"
f. Within folder "Startup", you may find programs, such as Dell Dock Run. Delete it if you don't use this type of feature.
With these changes, all newly-created user accounts will be pre-cleaned as they are created.

11. Favorite Document Icons in the Start Menu

I have certain documents that I always need to open and they would be handy in the Start Menu. In other words, by clicking the document in the Start Menu, it launches the application and opens the specific document.

Windows 7 is not as graceful in building these types of icons as I would wish (it was easier in XP and Vista). This step is optional and somewhat complicated. Follow these steps.

a. Using Windows Explorer, locate your document name (an access database, spreadsheet or other document).

b. Highlight the document name.

c. In the Explorer's address bar, other-mouse-click (illustrated) and choose "Copy Address as Text". Memorize the document name (e.g. Names.mdb); it is not copied as part of the address.



d. Move to the Windows Desktop, other-mouse-click, choosing "New, Shortcut"

e. Click Browse.

Locate the underlying application (e.g. Microsoft Access, Microsoft Excel, WordPerfect, etc.). You are looking for the actual EXE name. For MS Office, tunnel to "C:\Program Files (x86)\Microsoft Office\Office12\MSAccess.EXE" (Excel.exe, etc.)

f. While the dialog is still displayed,

- type a space and a quote (quote is required)
- Paste the previously-copied path name by pressing Ctrl-V
- Type a closing backslash, followed by the document's name, which you will have to type from memory; then close the quote (illustration shows an Excel sheet):

e.g. .."C:\Program Files (x86)\Microsoft Office\Office12\MSAccess.EXE" "C:\Data\Proj\Names.mdb"


The resulting icon will be on the desktop.

g. Cut and paste the icon, placing it in

C:\Program Data\Microsoft\Windows\Start Menu\Programs

h. Rename the icon from "...copy" to any name of your choosing (e.g. "Name and Address List").

i. Optionally, "Pin" the new icon to the Start Menu. Once Pinned, you can delete the "Programs" icon, if desired; the Pinned copy will remain.

* To test: click the Start Menu (and or All Programs) to see the new icon.


12.Using a C:\Data Folder


This part is an admittedly advanced topic and is optional.

Why C:\Data?

On my computers, I always store all data in a folder called "C:\Data" -- this includes all pictures, music, documents, spreadsheets, macros and even program preferences. Basically, if it is something I created, downloaded, or customized, it lives in the Data folder.

As I upgrade from computer-to-new-computer, I simply copy the C:\data folder -- and all data is moved to the new machine. If multiple users have accounts on the same computer, their data also lives in this same folder. This way family members can get to the photos, music, etc. Of course, this makes backups easier too.

This is the final cleanup from Step1, where the Side-Menus were simplified. In that step you optionally hid "My Pictures", "My Music", etc. Here is where you build new locations for those types of data.
Only do this step if you have decided to not use C:\Documents and Settings (aka C:\Users\Your user-id\My Documents) to store your data. If so, continue with these steps.
Building the Data Folder:

a. Open Windows Explorer, tunnel to (or make) a folder called C:\Data.

b. Within C:\Data, create these sub-folders:
  • Download
  • DB (database)
  • Graphics
  • Music
  • Personal
  • Photos
  • Prefs
  • Presentations
  • Projects
  • Spreadsheets
  • WP (word processing)
  • C:\Data\WP\Macros

Change Program defaults:


This is a bit tedious. Launch each of your applications, such as Microsoft Word, WordPerfect, your photo-editors, etc. Within each program, locate the setting or preference that controls the "default data directory". Change it to an appropriate C:\data folder.

For example, setting a default data directory for Microsoft Word (2007):
  • Launch Microsoft Word
  • Click File (orb), click button "Word Options"
  • Choose the "SAVE menu
  • Change "Default file location"
    from C:\Users\yourname\Documents
    to C:\Data\WP
  • Within "Advanced", scroll to the bottom of the list and click the "File Locations" button
  • Change Clipart Pictures to "C:\Data\Graphics"
To test, exit and re-open Word. File-Open, should be C:\Data\WP.

Change other Microsoft Office Products:

Similarly, change other Microsoft Office Products. Each will have to be touched one-time. In some programs, you may need to create a dummy-document before you can open the Options menus (e.g. MS Access, PPT).

Change Windows Media Player's Default Directory:

This is a great example of a program that should not store its data in the user's roaming profile.
  • Launch Media Player
  • Click top-menu-bar "Organize"
  • Click Layout, check "Show Menu Bar"
  • In top menu Tools, Options, "Rip Music", change Rip Music location to C:\Data\Music

Change Word Perfect's Default Data Directory:
  • Launch WordPerfect, Tools, Settings, Document
  • Set Default Document folder to C:\Data\WP
  • Set Graphics: Default Graphics Folder: C:\Data\Graphics
  • Set Default Spreadsheet Folder: C:\Data\Wksheets
  • Set Database: C:\Data\DB
  • Set Default Macro Folder: C:\Data\WP\Macros

Change Corel Paintshop Pro's Default Data Directory:
  • File, preferences, File Locations: C:\data\Graphics (or Photos)

Change ITUNES default Directory:
  • Launch Itunes
  • Select menu Edit, Preferences, Advanced tab
  • Change the itunes media folder location
  • (Copy your previous library's location to this new folder)

In other words, where-ever possible, tell each application where the Data folder is. Once this is done, you can create new user accounts, fix corrupted ones, etc., without loosing access to your data. The folders are also easier to share.

The drawback to this design: the documents are not secured from other users on the system. However, without physical security, there is no security and your documents were not really safe anyway.

This completes this tutorial on Start Menu Cleanup



Related articles:
Windows 7 Explorer Settings
Windows Folder Locations
Windows 7 Initial Look

Dell XPS M1530 64-bit Drivers
Cleaning up Start Menu Programs (Speeding up a boot)
Slow PS/2 Keyboards and Windows 7

Saturday, October 9, 2010

PrizeOrama - Raffle Ticket Selection in Excel

Excel How-to: How to write a Raffle-ticket prize selection spreadsheet. This is the "PrizeOrama" sheet which you can also download. See also this more improved program: Keyliner PrizeSelect.

My sister called with this problem: Her son's grade school has 300 students and each are eligible to win prizes based on school and volunteer work. Students can earn 1 or more chances (much like a raffle ticket). She went on to say last year they wrote each student's name on a slip of paper and tossed them in a hat -- but with many students earning 15 or more chances, she said she'd never do that again.

Having raffle-like drawings can be easily automated in Excel with macros. If you have never written an Excel macro, this will be fun project and the instructions below should take less than an hour to write. When you are done, you will have a random name selector.

You can also download a completed version using this link.

Click on this Keyliner Public GDrive link:
PrizeOrama Excel Spreadsheet Download

Author's Note: Since this article was written, I developed a much more sophisticated prize selection / prize drawing program, which can be found at this link: Keyliner's PrizeSelect. This program is also free.


The completed data-entry sheet looks like this:

Winners are calculated in a result-sheet that can be displayed in a variety of different ways:



Constructing the Initial Sheets

1. In Excel, open a new workbook and name two of the sheets as "Names" and "NameResults". These names will be required by the macros. (Other-mouse-click the default sheet, "Sheet1" and choose Rename.)



2. As illustrated at the top of this article, type column headings on row 4:

A4 = "Name"
B4 = "Tickets"

Cell A5 (e.g. "Bobbie" contains the first test record)
Cell B5 contains the count of the number of tickets Bobby earned.

Continue to build a half-dozen test records.

(Optionally, if you are using numbered Raffle-tickets, type the lowest-numbered raffle-ticket in the first two cells (A5, and A6), then build a simple addition formula and fill-down to the largest-number; No need to put in a ticket-count, "1" is assumed.)

e.g.
65444 (in cell A5)
65445 (in cell A6 - to get past the Range-name in cell A5)
=A6+1 (in cell A7)

2a. Highlight cell A5 and "Range-name" the cell as "NameAnchor".

Select Formulas, Define Name
Name the cell "NameAnchor"

3. Expose Excel's Macro Development Tools:

a. Click the Microsoft Office Orb (the old File Menu), choose the "Excel Options" button at the bottom of the screen. In the "Popular" section, check "[x] Show Developer tab in the Ribbon"


4. Build an initial Macro by following these steps:

a. From Excel's top menu "Developer," click "Record Macro"

b. For the Macro Name, type "SelectWinners" and "store macro in 'This Workbook'". No need for a shortcut key. Press Enter and the macro will begin recording, noting the small blue square in the lower-left of the screen.

c. Click the mouse in Cell A5 ("Bobbie") - just so the macro can record something.

d. On the toolbar, click "Stop Recording"


5. Open the Visual Basic editor (Macro Editor):

a. While still in the sheet, press Alt-F11 (Visual Basic VBA editor).

b. On the tree-side, tunnel to "Modules, Module 1". This is where the code that drives this program will live. You will see a Range-Select statement in the macro; this is what you recorded in the previous step.

At any time you can close the macro-editor (clicking the "X"); changes are automatically saved and you are returned to the sheet. Press Alt-F11 to return to the editor.


c. Above the "Sub SelectWinners()" line, insert a few blank lines and type these three statements:

Option Explicit
Public iRecordCount as Integer
Public iEntryCount as Integer


It will look like this:


For technical reasons, all macros should have an "option explicit" line -- even though Microsoft does not default it. The other two statements declare variables.


6. Write the main "SelectWinners" Routine:

This routine is the longest routine in the program and it will take a few minutes to type. As you type, indent with spaces and make the code look pretty. Comments (green) begin with tic-marks and long-lines can have line-continuation characters:

(click image for larger view; click right-x to return)

Still within the same sub-routine, complete the code with these remaining statements. Both the code above and this code lives within the Sub/End Sub statements:


7. Write the Private Sub A100_ProcessRecords() routine:


a. Below the "SelectWinners" 'End Sub' line, press Enter a few times to insert blank lines, then literally type these commands in order to build a new routine:



The details within the "Do Until / Loop" will be filled in a moment. For now, just stub-in the routine.

b. Now fill in the Loop details for the A100 routine (this code lives within the Until/loop statements):

(Click for larger image; click "right-x" to return)

8. Write the A200_SortNameResults routine:

Continue with the next routine, which is typed below A100's "End Sub". You are almost done:



9. Write the ClearPreviousResults routine:


Building a "Select Winners" Macro Button

As illustrated at the top of this article, it is handy to have a button that kicks off the macro. Build a button and attach it to the "SelectWinners" macro with these steps:

Close the macro editor and return to the original spreadsheet.
Click on the "Names" tab and follow these last steps:

1. Using the top-menu, "Developer"

a. Click the "Insert" tool and then the "button" Forms Control Tool. Be sure you do not select the ActiveX version.


b. Once the button is clicked, move the mouse into the sheet and click-and-drag an outline of a button somewhere on the form where you want a "Select Winner" push-button. Let go once the outline is in place.

c. When prompted for a macro name, choose "SelectWinners", then "OK". Note: You can Control-Click the button to resize, move and change the button's properties.

Alternately, you can run the macro manually by clicking the top "View" menu, Macros, Run Macro. Choose "SelectWinner" from the list.

Testing

To test the macros, confirm you have test (student) names in cells A5 and down. For a few of the records, type a "Ticket" count of 5.

Click the "Select Winner" button.

Open the "NameResults" tab (bottom tab) to see the randomly-selected winners. Note that those people with more than one ticket are represented multiple times in the list. Each has a random number, but all numbers were sorted randomly. If you have three prizes to give away, choose the top-three names. Remember, the results list shows all eligible people in one list -- with every ticket represented.

Click "Select Winners" a second time to re-randomize the list.

Saving the Sheet

If you are using Excel 2007/2010, sheets with Macros must be saved in a special way. Select File (orb), Save-As. Change the filetype from "Excel Workbook (*.xlsx)" to "Excel Macro-enabled Workbook (*.xlsm)" - you must do this or the macros will be stripped out of the sheet.

When you next load the sheet, you will be prompted to "enable macros". See Microsoft Help for additional information on how to always allow macros on this sheet.

Description of Steps:

This article is not intended to teach macro programming, but here is a brief description of the program's logic.

The main module, "SelectWinners" calls A100_ProcessRecords.

A100_ProcessRecords starts in cell A5 (the first name) and moves one record at-a-time down the list. With each found record, it copies the (student's) name to the second "NameResult" sheet. If the student has more than one ticket, their name is copied multiple times.

As each name is copied, it is assigned a random number.

Once all the names are copied, the second sheet is sorted numerically by the random number. Note how the sort routine knows how many records were loaded and it uses this count to highlight the sort records.

Once sorted, the top name should be awarded prize #1, second name prize #2, etc. Usually, the grand-prize is awarded third. The downloaded version of this sheet (see below), contains other interesting features.

Disqualify a person by putting a negative number for the count (-e.g. -1). With this, their name stays in the list, but they do generate a random number and in effect become ineligible for the raffle.

If you need to have the person's name and phone number, type both in column A.

Downloadable Version:

This spreadsheet is available on Keyliner's Public GDrive site. You can download a complete sheet, with all macros, free of charge and you are welcome to use this routine as you please. If you use the sheet, drop me a note; I'd like to hear from you.

PrizeOrama Excel Spreadsheet download


The downloadable version contains several other interesting features, not documented here, along with full instructions on the features and use. My apologies for not having cut-and-paste code in this article; I've never figured out how to make those types of textboxes....

Other comments:
  • Remember, only click the Select Winners button one time -- all winners are randomized and calculated at the same time.
  • If a person has multiple tickets or the person is listed multiple times in the list, they have multiple chances to win and can win multiple prizes.
  • At this time, the sheet does not allow a person to win "only once" - even if they have multiple chances; this is a future enhancement.
  • The downloaded version has many more features than this article discusses.

Related Links:
PrizeOrama Excel Sheet Download
PrizeSelect - a more complete raffle ticket program

Enumerator - File-exist testing program

Utility Download: Enumerator.exe - A batch driven file-exist utility that can set Return-Codes against a list of files. Handy for testing batch downloads.






I needed to test a directory to see if 30 mainframe files were downloaded properly. If all files were present, then a batch scheduler would kick off the next step in a process. Testing this many files in a standard BATCH program would be tedious. To help out, I wrote a simple program called the "Enumerator."

Enumerator is a DOS (console) application which accepts list of passed filenames. If each filename in the list is found, the program sets a return-code of 00. This code can be tested in a downstream batch process, as illustrated below.


This is a free program, no license or registration required.
Developed in C#.
Runs as a DOS or Windows program.
You are welcome to download and use.



Enumerator.exe

From the Keyliner Public GDrive site, click this link and open the folder:
download Enumerator.exe

When saving the file, the location does not need to be on the path.
Instructions for Use:

Command-line Summary:
Enumerator.exe /? Help
Enumerator.exe c:\path\parameterfile.lst

1. Using an ascii text editor (Notepad), build a test input-parameter file, listing one filename per line. For example C:\data\parameter.lst:

;Test Enumerator Parameter File
;use Semicolons for comments

;Wildcards are supported


C:\data\testfile1.doc

C:\data\testfile2.xls
D:\subfolder\*.xls
\\server\share\folder\somefile.txt


Close and save the file to a known location. For example, "C:\Data\Parameter.lst"

2. Open a command-prompt (DOS Prompt); Start-Run, "CMD".

3. Change to the directory where Enumerator.exe was saved. For example:

CD "\Program Files\Util"

4. Type this command, all on one line:

enumerator.exe "C:\data\parameter.lst"

Use quotes if the filename or path contains embedded spaces.
Press enter to run.


Results:

DOS ErrorLevels
00 = All files detected
89 = One or more files were not discovered
99 = Startup/parameter error

The DOS screen SYSOUT will also report a summary of the findings. Be aware this may not be visible in a batch process, depending on how it is called.


Optionally launch in this manner:
"C:\Program Files\Util\Enumerator.exe" "C:\data\parameter.lst" >c:\data\log.txt

where results are recorded to a log.txt file, which can be reviewed with Notepad. Return-codes are set as before.


TESTING in a BATCH File:

Consider this sample batch file (C:\data\test.bat) for testing:

@Echo off
CLS
@echo Enumerator %1 %2 %3
@Echo.
enumerator.exe "C:\data\parameters.txt"
IF "%ERRORLEVEL%"== "99" GOTO PARMERR
IF "%ERRORLEVEL%"== "89" GOTO FAIL
IF "%ERRORLEVEL%" == "0" GOTO GOOD

:ParmErr
Echo Bad parameter file
Echo Use Enumerator.exe for syntax
Echo.
PAUSE
GOTO END

:Fail
Echo.
Echo Some files not found
Echo.
PAUSE
REM Remove PAUSE statement for production
GOTO End


:Good
Echo.
Echo All files accounted for....
Echo.
PAUSE
REM Remove PAUSE statement for Production
GOTO END

:END


Additional Notes:
The processor running enumerator must be logged in as a user with adequate rights to see the files being tested.

Leave a comment if you found this program useful.