Windows 8.1 CD DVD Drive not visible in File Manager

How to: Windows 8.1 CD Drive, DVD Drive not visible in File Manager.

Issue:
After installing Windows 8.0, Windows 8.1, the CD/DVD drive does not appear in My Computer, FileManager, Windows Explorer or in the Control Panel under devices.  CD or DVD drive is missing or not recognized by Windows or other programs.  ...and yet the CD worked during the install.

Other Symptoms:
Error:  The device is not working properly because Windows cannot load the drivers required for this device (Code 31).

Error:  A driver for this device was not required, and has been disabled (Code 32 or Code 31).

Error:  Code 19,  Code 39, "Windows successfully loaded the device driver for this hardware but cannot find the hardware device (Code 41).

Reason:
Unknown.  But symptoms appear if you upgrade the Operating System (Windows 8.0 to 8.1); installed a new operating system; possibly un-installed Microsoft Digital Image.


Solution:

Microsoft suggests 5 different solutions, but this is the one that has worked for me on multiple machines.

Synopsis - Add this DWord32 Registry Key and Value: 

HKLM\System\CurrentControlSet\Services\atapi\
\Controller0\EnumDevice1 = 1 (Dword32)


1.  Confirm you are logged into the Windows Desktop as an Administrator  (you cannot be a standard user for these steps).

2.  Press Windows Logo Key + R on keyboard.
Type "Regedit"  (no quotes); press Enter.

Alternately, from the Windows 8 Start Page, click the background, click the down-arrow-icon on lower-left; type the word "Regedit".  Run this program.
 
3.Tunnel to this registry key:

hkey_local_machine\System\CurrentControlSet\Services\atapi

4.  Right-click "atapi",

Select "New, Key"
Type "Controller0"   (Controller-zero, no quotes; all one word)

5.  On detail side, right-click the new Controller0 key,

Select "New", DWord32
Type "EnumDevice1"  (no quotes, no spaces, number-one); press Enter

Right-click EnumDevice1, click Modify
Type 1 (one) in the value box, "Hexidecimal"

6.  Close the Registry Editor, Restart the computer


Reference:
http://support.microsoft.com/kb/314060/en-us

Windows 8.1 Upgrade not in Windows Store

How to: Windows 8.1 Upgrade not available in the Windows App Store
How to: Windows Update hangs - Downloading at 0%
How to: An Account with administrator privileges

I ran into a variety of problems as I tried to upgrade a Windows 8.0 machine to Windows 8.1.  Here were the steps needed to resolve each issue.  

Overview:
Windows 8.0 users can upgrade to 8.1 for free.
 
Unfortunately, the upgrade is only available on the Windows App Store.  This means you cannot use a local machine account to install and you must painfully download the upgrade for each machine (you cannot easily download the upgrade one time and distribute to multiple machines).


Issue:
The Windows Store does not show any "Updates".  

The Windows Store does not show any of your apps for update; no locally-installed updates are listed.
The upper-left corner of the Windows Store does not show e.g. "Updates (21)".
The Windows 8.1 "Update Windows" is not visible in the App Store.

 
Solution:
The Windows App Store will not show updates until the underlying Windows Update features (e.g. Windows Installer) is patched with the latest bug fixes. You will find this problem is especially true if you restored an older image while rebuilding a machine. 

1.  Open the Windows Control Panel, select "Windows Update"
2.  Manually click "Check for Updates"
3.  Install all important updates and reboot the computer when done.

Results:
The Store should show "Update to Windows 8.1 for Free" as an available install.  If you have a Volume License, MSDN or Technet license, or a preview edition of Windows 8, the App Store install method will not work.  See the end of this article for other details.
 

If the update is still not visible, confirm you are logged in with a Windows Live account (and not a Local account).  Most people have a Live Account.  For details on how to tell, see this section: "You need an Administrator account...", below. 

Issue:
Windows Update Hangs - downloading updates stays at 0%

While trying to apply your first round of Windows Updates, the update begins but makes no progress, remaining at 0% - Installing Updates...., with no apparent activity.

Solution:
1.  Wait a respectable amount of time - say 5 or 10 minutes, looking for a %-status change, then
2.  Click Stop Installation (Cancel) on the download.
3.  Reboot the workstation gracefully.
4.  Re-open the Control Panel, select Windows Update and click "Check for Updates" again.  The update should proceed normally.  I have no explanation on why this needs to be done on a first-time load of Windows Update.  This is especially true on a new PC or a newly-imaged PC.

Once updated, reboot the workstation, even if it does not prompt.

Expected Results: 
On the second attempt, the update should begin downloading and installing. 

If the install hangs at some other percentage, note if it is installing the .dot-net framework 4.5, or 4.5.  These take a long time to download and install -- often taking 30 to 45 minutes.  Be patient.

Next Step:  Once updated and patched, open the Windows App Store and look for the Windows 8.1 Update; it should be available.


Issue:
You need to use an account with Administrator Privileges in order to install.  

When installing the Windows 8.1 Update from the Windows App Store, you may be prompted with a message "use an account with Administrator privileges in order to install."


Solution 1:
Confirm your User Account has Administrator rights.

Likely, the machine has multiple users who can login to the workstation.  By default new user accounts created after the first Windows install are "standard," not administrator accounts.

1.  From the top-right of the Start Menu, click your account name and change the login to the person with administrative rights, typically the person who first booted or installed the machine.  Use this account to open the Windows Store and Install Windows 8.1.

Note:  If your account is already an Administrative account, see Solution 2, below.

Alternately, use these steps to promote your current account to an administrator:

1.  Open the Control Panel*, select "User Accounts".

2.  In your account, click "Change your account type"  (a shield-icon-menu); you will be prompted for the administrator's credentials, the next step.

 
3. On the security screen, choose the administrator account (typically the account used when the workstation was first booted or first installed).  Type the credentials; this will promote your standard user-id to an administrator.



4.  Important: After typing the administrator's password and setting Administrative rights, gracefully reboot the workstation before attempting to run the Windows App Store install.  The promoted account does not take effect until that user logs out and back in again and a reset seems to give better results.

Next Steps:
Once your account has been promoted to Administrator, or once you have logged in with an Administrator's login, open the Windows App Store and launch the Windows 8.1 upgrade again.

*Opening the Control Panel
Open the right-edge Charms menu, choose "Settings, Control Panel", or follow these steps to place the Control Panel on the Start Menu.

In Windows 8.0, go to the Start Screen.  In an area below the tiles, "other-mouse-click" the background" and choose "All Apps".  Type the word "control".   Other-mouse-click the Control Panel tile and choose "Pin to Start".  The Control Panel is now on your Start Menu.  Press Esc to dismiss the All Programs Icon screen.

In Windows 8.1, go to the Start Screen; Click on the background.  In the lower-left, click the down-arrow to expose all icons.  Type the word "control".  Other-mouse-click the Control Panel tile and choose "Pin to Start"

Note:  If a non-Windows Live account is promoted to Administrator, it will still fail to install the Windows 8.1 upgrade.  See Solution 2.


Solution 2:

Use this solution if Solution 1 does not work.  The issue:  Only Microsoft Live Accounts can use the Windows App Store.  Secondly, the computer must be registered as a "Trusted Computer" from Windows Live.  Details, below.

Background:  If the PC's login account is a "local account" (and not a Microsoft/Microsoft Live account), you will not be able to use the AppStore and you will not be able to download and install Windows 8.1.  To see if your PC is a Local account, open the Charms menu, select "Change PC Settings" (bottom), select Accounts, then "Your Account".  Look under your UserName.  If it says "Disconnect" -- this is a Windows Live account; otherwise, it is a Local account.

Many People, when starting up a new PC, create a Windows Live account without realizing it and Solution 1 will have worked.  But if you created a "fake" Windows Live account (with a bad phone number and fake email), or never created a Windows Live account, you will not be able to install from the App Store.  These steps will work around this issue.

Decision:  Do you already have a Windows Live account, perhaps built on another PC?

If yes, do these next steps.  If not, follow the "Live Account Does Not Exist" steps:

Steps: A Live Account Exists:
A Windows Live account, such as one used to install another PC, or as a second account on this PC, can be used to install Windows 8.1. 

Check to see if you have a second login for this machine:
a.  From the main Start Screen, click the Profile-icon in the top-right. 
b. Log out and login as the Windows Live profile (this will show up as a second user who can login and is likely the account used to install and build this PC.  If a second account does not exist, see the next set of steps.
c. If the second account exists, use that account to login, then install Windows 8.1. 

If no second Login, but you have a Windows Live Account from another machine:
If you do not have a second account, but you have a Windows Live account, perhaps used on another PC or laptop, then follow these steps to enable that account on this PC.

- From your Local Administrator's account, open Windows Control Panel, "Users."

- Create a new user account on this PC (steps not detailed here), and use the Windows Live account to establish its credentials.  Note: As you build the account, nothing will happen on the screen; that account will not become active until used at least one time, as documented next.

- Important:  Return to this User's Control Panel settings "Change the account type" in order to 'promote' this user to Administrator.  See the top of this article for illustrated steps.

- Log out from the Local account and Login as the new Windows Live user.  This builds the profile and activates the account.

- Open the Charms, Settings menu.  Choose Change PC Settings" (the link at the bottom of the Settings screen).  On the left-nav, click "Users".  On the detail side, click "Trust this PC" (or if not available, "Verify this user". 

- Once this is done, logout and back in with this account.
 
- Start the Windows 8.1 install.


Steps: A Live Account Does Not Exist:
If a second account does not exist or you do not have a Windows Live account, create one now.

1.  From your current local (non Microsoft account), use Control Panel, Users. 
Create a new user.

Make this a Microsoft Live account, (do not create a local machine account).  When creating the account, you must use a legitimate email address and a legitimate phone.

2.  From the main Windows 8 Tile screen, click the profile-picture in the upper-right and login as the new account.  This creates the profile, but this profile does not have enough rights to install.  

3.  Sadly, you must logout as this user and log back in with the original Local Administrator's account.

4.  Open Control Panel, Users.  "Change Other Account."  Modify the newly-built Windows Live user.  Using the steps from the top of this article, promote this user from a "Standard User" to "Administrator." 

5.  Even more saddness:  Logout and re-Login as the new Windows Live user.

6.  Open the Charms Menu, choose "Settings".  Click the "Change PC Settings" (link, bottom of charm menu).  This will open into a new Metro-style Settings Screen.

6a.  On Left-nav, click "Users"  (user accounts)
 
6b.  On detail side, just under your profile picture, click "Trust this PC".  Notice, you have to "Trust this PC" while using a Windows Live account and the Internet must be active.  This is different than validating your copy of Windows.  If the PC has already been trusted, you may see a "Verify User"; click this instead.

Microsoft will send a text message to your Windows Live registered phone number or will verify with the registered email (this is why you can't fake this).  If it sends a text message, type the last four digits of your phone, when prompted.  Then, from the text message, type the 7-digit authentication number, as sent.  This confirms the trust.

7.  Return to the App Store.  Windows 8.1 should install.

Practically speaking, if you want to update any apps in the app store, you will have to use the Live Account.  Microsoft is forcing you to register and login your desktop.


Issue:
You do not know the Administrator's account / password
Solution:
Recover the password online by re-setting your Windows Live Password.  From another machine or another account with access to the internet, go to this link:

https://account.live.com/password/reset

An email will be sent.  (This assumes your administrator account is a Windows Live account and not a local machine account and assumes you can get to your email account from a browser via webmail.)

Other Possible Issues:

If your Windows 8 was originally installed from these sources, using the Store to update will fail.

Windows 8 Enterprise  (See your system Administrator for the update)
Windows 8 Volume Licensing (See your system admin)
Windows 8 MSDN or Technet (download a new ISO and install from scratch)
Windows 8 Preview

Your comments on this article are welcome.

Related Articles:
Delete Windows.old folder after upgrading to 8.1 - saves about 4G of disk space.

Delete Windows.old after upgrading to 8.1

How to: Delete Windows.old directory after upgrading to Windows 8.1

Issue:
After doing an in-place upgrade from Windows 8.0 to 8.1, File Explorer will not delete the Windows.old folder and prompts for numerous security permissions.

Caution:
Only perform these steps if you no longer need the 'Windows.old' folder and have recovered all old documents from the previous Windows version.  The Users (user profiles and their associated Documents, Music and Video folders) will also be deleted.  In other words, if you are still opening the "Windows.old" folder to retrieve files, backup or move that data first.



Solution:
Delete the folder using File Explorer's Disk Cleanup utility

1.  Launch File Explorer (from Desktop, click File Explorer or from the Start Menu charm-menu, click "Search", type "file explorer".

2.  On the Tree-side, locate "This PC"  (aka, "my computer")
     Other-mouse-click the C: drive, choose "Properties"

3.  Click "Disk Cleanup"; this will take a few moments to load.
4.  Click "Cleanup System Files"  (this will take another few moments to load)

Click for larger view; "X" to return

5.  In the check-box list, click "[x] Previous Windows Installations"

6.  In the [More Options] tab, not illustrated here, I also recommend clicking System Restore and Shadow Copies "Clean up".  When prompted "Are you sure you want to delete all but the most recent restore point?", click "Delete"

7.  Click OK and confirm Delete files.

Results:
Windows.old will be removed, freeing up about 4G of disk space.

Microsoft SystemSweeper - Antivirus

Windows Defender Offline (formerly Microsoft Security Essentials - MSE ).  A bootable CD for cleaning viruses.  Updated 2013.11. 



This article has been retired.  See this up-to-date Keyliner article:
Keyliner - Virus Cleanup Steps


>Historical:

When cleaning viruses, it is best to boot from a non-infected disk in order to do the cleanup and the easiest way to do this is to boot from bootable CD.  Microsoft and other vendors now have free, bootable CD's, that clean even the most stubborn viruses. Because you are booting from a guaranteed, non-infected operating system, and because it has full-control of the hard drive, with no locked or in-use files, it gets unprecedented access to the disk and it can clean the most stubborn infections.




Building and using bootable CDs are easy and reliable. If your machine is infected, I now recommend running these utilities prior to any other virus scanning steps.  You should run these utilities from multiple vendors.

Recommendations:

1.  Build bootable CD's from a non-infected computer.
2.  Build the disks when needed; old disks are obsolete.
3.  If offered to build a CD vs a bootable USB drive, use the CD (Viruses can re-infect USB).

4.  Build CD's from each vendor, below. Some vendors can catch viruses that other vendors miss.
 
5.  With each vendor's program, cancel the default quick scan and run full-scans. These are time-consuming, taking several hours each.

6.  When done with bootable disks, launch Windows and then download and run this utility:

Download MalwareBytes: Malwarebytes
Run the Free Version.
If offered to install a Demo "Pro" version; decline and run the free (optionally, buy the professional version).  This scan will take several hours.

7.  Then download and run this additional Microsoft Utility from within Windows as a double-check:

http://www.microsoft.com/security/pc-security/malware-removal.aspx


Concerns:

If you have a newer Windows 8 or 8.1 with a UEFI disk, the CD's from non-Microsoft vendors will not work and I do not have a good work-around.  (UEFI are security-enabled BIOS boot drives and bootable CD's cannot reach them - at least as near as I can tell.) 

To see if you have a UEFI disk, boot into the BIOS and look at the boot system.  Alternately, load Control Panel, Administrative Tools, Computer Management.  On left-Nav, open Storage, "Disk Management" - wait a few moments.  Hover mouse over the Disk 0 partitions, looking for an "EFI System Partition".


Download and build CDs from each recommended vendor:

A.  Download Microsoft Security Essentials - MSE

For Windows 7, Vista, Windows XP:
http://windows.microsoft.com/en-US/windows/what-is-windows-defender-offline

For Windows 8, 8.1 and newer,
http://windows.microsoft.com/en-us/windows/what-is-windows-defender-offline-beta


- Use Microsoft's Internet Explorer to download
- Always download and use the latest version
- It will build the CD automatically; follow the on-screen prompts or see the steps below.
- Most Windows 8, 7 and Vista users should choose the 64-bit version.
- XP users should choose the 32-bit version

B.  Download Kaspersky Lab's "Rescue Disk"

This will not work on UEFI disks.
http://support.kaspersky.com/us/viruses/rescuedisk

- Click the Download Kaspersky Rescue Disk link.
- This will write an ISO file, which is a CD disk image.
- From Windows 7, follow these steps to write the ISO file to a CD.


C.  Download AVG Rescue CD

This will not work on Windows 8.x UEFI disks.
http://www.avg.com/us-en/avg-rescue-cd

- Click the AVG Rescue CD Free Download link; download the ISO version.
- See these keyliner steps to write the ISO file to a CD.


MSE Easy Steps: Build the CD

1. Preferably, from a non-infected computer, click the link above and choose the 64-bit or 32-bit version, depending on what OS was installed on the infected machine. If in doubt of the version, see Microsoft's documentation in the linked page. In general, unless you know otherwise, use these:

32-bit for Windows XP
64-bit for Windows 7 - most likely
32 or 64 for Vista; could be either; try the 32-bit

The download is a small stub called msstoolxx.exe where xx=64 or 32.

2. Save the download-exe to a known location on your disk.
3. Open the folder where you downloaded and run the executable.

This builds the image and it will take about 30 minutes, depending on your Internet connection speed. You will be prompted to build either a CDR, USB thumbdrive. I recommend the CD because not all machines can boot from a USB thumb-drive. The image-build is automatic.

Ideally, build the image from a non-infected computer, but if this is not possible, try from the infected machine. If you are downloading from an infected computer, rename the downloaded EXE to a random name before running because you know the viruses will figure this out and will try to stop you.

Using the Image:

Insert the CD and boot the computer, choosing "Boot from CD" when prompted.
The scan will take several hours, depending on the size of the disk. The process is completely automatic.


If your machine does not boot from CD

Summary: Cold-boot the computer and enter the BIOS configuration screens (hardware/BIOS settings, often pressing F2 or F10 as the machine boots). In the BIOS menus, change the "Boot order", allowing the CD to boot before the hard disk. Save the configuration change (typically with an F10=save) and try booting again.

See this Keyliner article for additional details. Booting from a CD or DVD


Caveats to Think About

• Download and build the bootable image when needed. Old copies are obsolete. Microsoft continuously updates the CD with the latest virus signatures. Because it is a CD, it cannot update itself.
• It can only clean viruses that Windows Defender knows about. If it fails to clean the infection, consider re-building this same disk a few days later. Microsoft updates their virus signatures several times per day.  Consider the steps in this keyliner article: Removing Win7 Anti-Virus
• None of these products replace the need for real-time virus scanning.  See Microsoft Security Essentials

Final comments:

If you have read my previous articles on these topics, cleaning a virus while running on an already-infected machine is like fixing a car's engine while driving. While the infection rages, you have to trick the computer while cleaning and the steps are difficult and vary, depending on the virus.

Booting from a guaranteed-clean operating system is an ideal way to catch a virus. The virus is completely disabled during the scan and Microsoft has full-control of the system. I wish all virus-scanning vendors could use this same design - it vastly simplifies the process.

Once cleaned (and presumably the machine is useable), I still highly recommend running other virus tools, such as MalwareBytes and SuperAntiSpyware to double-check. As much as I like MSE, no single virus scanner catches all the bugs. If you have one virus, you have others and you will have to run multiple tools to make sure.  See the related articles, below, for the steps on how to use these other programs.

Multiple Scanners:
Do not leave multiple scanners installed at the same time.  I recommend using Windows Security Essentials (Microsoft's free real-time virus scanner).  On several machines that I have worked on, both MSE and the heavily-advertised Mcafee Security Scan Plus (free) are running on the same machine and has caused numerous problems.  Uninstall one or the other.

With this thought, if your machine was running Windows Defender and you were still infected, then Microsoft's offline virus scanner may not help because it missed it in the first place. But I still believe MSE is one of the best tools on the market -- and it is free.
Technical Note:
The bootable image loads a run-time copy of Windows. Unfortunately, the bootable image does not allow you to do anything else. You won't be able to copy data files or run other virus scanning products. It would be neat if you could run other cleanup tools, but Microsoft locked this down. Hopefully, they will re-consider this.

Related Products:

Microsoft is also distributing a related product called "Microsoft Safety Scanner". This is a single executable that does not need to be installed and you might be able to run this when no other program will work. However, this design is not as good as the bootable version described earlier in this article. If you have a netbook without a CD, this may be worth a try. The Scanner (msert.exe) is only valid for 12 days, then it expires. This forces everyone to download the latest and greatest version.


Related articles:
Microsoft Security Essentials
Removing Win32 Cryptor
Removing Win7 Anti-Virus - Recommended steps for all viruses
Removing Personal Security Virus
Securing Windows 7 from your Children
Booting from a CD / DVD

Other recommended virus scanners:
MalwareBytes: Malwarebytes

SuperAntiSpyware: superAntiSpyware
Choose the Free Edition. Despite its suspicious name, this is a legitimate program.
Rename to xxSuperAntiSpyware.exe before running.

Interesting article on spear-phishing attack:
http://blogs.technet.com/b/mmpc/archive/2011/05/31/when-spear-phishers-target-security-researchers.aspx

Windows Explorer shows Red X

How to: Windows Explorer shows red-x on some website graphics (png), but other images display properly.  Firefox, Chrome and other browsers exhibit the same symptoms.  Windows Explorer, Firefox, Chrome, etc.. 

Symptoms:

Windows Explorer shows a red x on some web page images but others appear correctly.  Other browsers have the same problem.  This is a problem, especially with Windows XP.

Right-Mouse-Clicking the image, and selecting "View Image" displays an error (unrecorded; comments welcome).

Problem persists even if Firewalls (Norton, McAfee, etc.) are disabled.
Problem persists even if virus scanners are disabled.

Diagnostics:

1.  In a separate browser window, locate a page with the Red-X.

2.  Discover the type of graphic file using one of the following methods (.PNG, .JPG, etc.).  The method depends on what browser or browser version you have.

- Other-mouse-click the image, choose "Properties".  Note the "Type".

- Other-mouse-click the image, choose "Save Picture As" or "Save Image As".  Note the filename's extension.  Cancel the File-Save-As.

(Note: The top image on this page is a PNG file.  If it displays as a small 1cm (1/4") red-X, you are experiencing the problem referenced particularly in this article.  If the Red-X is a larger red-x, the size of a postage stamp, then PNG files are not having this but other solutions are discussed later in this article.)

3.  Confirm on other Sites

Visit other sites with the same problem.  If all file-extensions are the same (this document assumes ".PNG" files), continue with the remaining steps. 

If certain images are not showing on one site, but the same file-extension shows in other web pages or web sites, the author of that website has a bad link in their page design.  In other words, if "(PNG)" files work on other sites, but not on this one, this is not your problem and you should not follow the steps in this article.

If you have a mixture of different graphic type failures, then this document is not the proper solution.

If all graphics, of all types, are not displaying, see below, "Internet Explorer 10"

PNG Solution:

Assuming all PNG files are not displaying, but others, such as JPG, JPEG are displaying properly, continue with the steps in this solution.

A.  From Keyliner's public GDrive, download one of the following registry merge files, depending on your operating system:

PNG_FileAssociation_XP.reg

PNG_FileAssociation_Windows7.reg


(For Vista, Windows 7, Windows 8, Windows 8.1, use the Windows7.reg file.  These are ASCII text files.  Once downloaded, you can open the files with Windows Notepad to read their contents, prior to executing.  Compliments to Ramesh Srinivasan for creating the files.)


B.  Locate the file in your download directory. 

C. Right-mouse-click and choose "Merge". 

D.  Close all browser sessions, and re-open.  Confirm Red-X's are gone.

E.  If some Red-X's are still a problem, re-open your browser and delete your Temporary Internet Files (Cache).  Details pending.

What if not PNG?

If the Red-x's are not PNG files, I do not recommend merging the registry keys above, although I suspect no harm will happen if you do.  You could build your own version of the registry merge using these rough steps:

From another computer with the same Operating System (which does not have this problem), open the program "Regedit.exe" (Start, Run, "Regedit.exe" - see this article if your RUN command is not exposed.) and export each of the keys to a separate Registry Merge file.  This will build multiple REG files and each would have to be run separately.  Copy the REG files to the bad computer and merge each.

The steps for this are beyond the scope of this article.  Leave the author a comment on what type of file you are missing and I can build the merge file for you.  See Email address on Right-Nav.


What caused this problem?

Unknown.  Likely another graphic editor took control of this extension and you have since un-installed that editor.


Other Possibilities

This Microsoft (Windows XP) article discusses this same problem.  Be aware the registry merge above re-constructs this key, along with others.  You may wish to try this method first, as it is less intrusive, but also the Microsoft solution may not fix all problems with the file type.

http://support.microsoft.com/kb/915158



All Graphics Blocked with Internet Explorer 10

If all graphics (JPG, PNG, etc.) are blocked, on all web sites, and you are using Internet Explorer 10, consider this solution:

A.  Launch IE 10.

B.  Select top-menu Tools, "Internet Options"

(To expose the "Tools" menu:  find a "grey" uninhabited area near the top of IE's screen, staying inside of the IE Window.  Other-mouse-click, choose "Menu Bar")



C.  In the [Advanced] tab, under "Multimedia", click [x] Show Pictures.


Comments Welcome.

Scroll Bars missing in Adobe Acrobat Reader

Scrollbars missing in Adobe Acrobat Reader (version XI version 11, Acrobat Reader DC).  Vertical scroll bar missing.

Problem:
Vertical scrollbars missing when viewing a multi-paged PDF file.

Reason:
Adobe is by default assuming you have a touch-device but I have no idea why Adobe would set this as a default.  Regardless of a touch-screen, a scroll-bar is useful on any device, no matter what method is used for scrolling.   


Quick Solution for current Document:

This change only affects the current viewed document.  See Permanent Solution, below, for a better method.

A.  Acrobat Version 11 and older:
      Within Reader, top-menu, choose "View", Show-Hide,
      Uncheck [ ] Touch Mode

      If the top-menu is not visible, press F9.

      If the "Touch Mode" option is not available, especially in newer version of
      Reader (DC), see the preference setting, below.


Permanent Solution - All Documents, All Versions of Acrobat Reader:

1.  Within Acrobat Reader, select Top-menu "Edit", Preferences
     If the top-menu is not visible, press F9.

2.  In the Left-Nav Categories section,
     Choose [General]

3.  In the Basic Tools detail section,
     Change "Touch Mode" from "Auto Detect" to "Never"

4.  In the Left-Nav, [Documents] section,
     Uncheck [ ] "Allow documents to hide the menu bar, toolbars and windows controls"

5.  Close Acrobat Reader, and re-open a pdf to test.


Other Recommended Acrobat Reader Options:

I recommend making these additional changes to Acrobat's default settings.

a.  In Left-Nav [Page Display],
    Set Page Layout to "Single Page". 
    Set Zoom to a default value, such as "85%" 
    (literally type the value, even if not on the pull-down selection list)

b.  In [Forms], set "Auto-Complete" to "Off". 
     This is a security measure.

c.  In [JavaScript], uncheck [ ] Enable Acrobat JavaScript.
     This is a security measure and I doubt any documents you read will be impacted.

d.  In [Search], change "Maximum Cache Size" from 100MB to 10MB,
     Then click "Purge Cache Contents". 
     The default value is obscenely large.

e.  In [Usage Information], consider turning off 'Yes, I would like to share anonymous..."


Hiding the Annoyingly Stupid Right-Pane tools bar:

For Acrobat versions 11 and older (this does not work for Reader DC)

1.  Confirm Windows Explorer (File Explorer) shows hidden and system files
     (steps not detailed here).

2.  Tunnel to C:\Program Files (x86)\Adobe\Reader 11.0\Reader\Services


3.  Delete these 2 files:
    DEXShare.spi
    DEXEchoSign.spi

     If these files are not visible, confirm Step 1.

For Acrobat Reader DC, there does not appear to be a way to hide the right navigation bar.  Perhaps it is time to consider the Foxit Reader?

Related:
Keyliner Article: Time to remove Adobe Acrobat Reader 
With all of the problems with Acrobat Reader, I made the change.

Reset Linksys Wireless Router Password

Discussion: How to reset a wireless router's password.  Or, how to reset a wireless password without having to reset the router to factory defaults.

Every few months I get asked this question:  How can I discover my wireless router's password.  Or, more accurately, how can I reset the wireless password without resetting the router to factory defaults.

The short answer:   You can't -- not unless you know the router's admin password.

(Nick Park's Wallace and Gromit)

The longer answer:

* If you know the router's Admin password:

You do not have a problem.  Log-in to the router and view the wireless passwords.  (See first-time-setup, below for steps)

** But the real reason you are reading this article is probably because you have forgotten both the Admin and the Wireless access passwords.  This puts you at a distinct disadvantage.

Without the router's admin password, the only way to recover the Wireless password is to reset the router to factory defaults and re-configure all wireless devices. In other words, rebuild the network from scratch.


The real answer:

If you need to reset to factory defaults, follow the instructions in this excellent keyliner article -- skipping the hardware steps a, b, and c, jumping directly to the router-reset in Step 1.

• Linksys EA2700 Router - First Time Setup

These steps work basically the same with all brands of routers - with a minor difference in the initial IP address and default password.

Sounds scary, but it is relatively easy, provided you are some-what computer cognizant and are not afraid of following those instructions.  If you are familiar with the steps, it takes about a half-an-hour to an hour to rebuild the network, plus another minute or two for each connecting device. You will have to revisit Xboxes, Wii's, netflix devices, TV's, laptops, tablets, thermostats and cell phones to all recognize the newly built network.



What if you made a backup of your Router's Configuration (saving as a .cfg file)? 
Can I recover from this?

If you knew your Admin password, you could recover the config file - provided you could log into the router and restore it -- in other words, a catch-22.  And good luck opening the (linksys) .cfg file in a text editor; the file is encrypted, as it should be.  The real problem with this method is nobody ever makes a backup of their router-config (nor do I), so likely, this isn't even a possibility.  Readers have posted comments about other brands of routers, below, but I have not seen evidence that the passwords are visible in clear text and am suspicious.

Okay, I get it.  Any other hints?

Step 8 from my Router-First-Time-Setup article, deserves to be repeated:


"The passwords should not be lost or forgotten.  I recommend recording these values and store in an envelope near the router or use a luggage tag:


Router Address:           192.168.200.1
 
Broadcast SSID: _______________________

Starting DHCP Address:  192.168.200.100

Admin Password: __admin /______________

24G Wireless Password: ________________

5G Wireless Password: _________________

Guest Wireless: _______________________


Example:
Broadcast SSID:  keyliner
Starting DHCP Address: 192.168.200.1
Admin Password:  airplanes5g
24G Wireless Access Password:  airplanes24
5G Wireless Access Password: airplanes5g - or other password
Guest Wireless Access Password:  airliner





Why the Secrecy?

The router's admin password and the router's wireless are encrypted and if you could hack them, everyone could.  By definition, wireless things are a security nightmare to begin with.  The vendors and the operating systems on your computers, phones, and other devices, have taken great lengths to make sure this stuff is secured.  From all my research, they've done a reasonably good job.


Sorry there is not a good answer to this problem.
T.

Multiple JPG Edits Degrades Quality

Question: Does editing an JPG image multiple times degrade the image quality, cause shifts in color or loss of sharpness?  The answer is yes, even if compression is turned off. Although you can tell your editor to save JPG uncompressed, it still introduces compression and the image quality degrades with each generational edit.  This article explores loss of quality during multiple editing iterations.

The Experiment:

As an experiment, I took a simple, mostly black and white image, and sent it through ten generations of edits, storing the file uncompressed.  At each step, I checked for changes in color and sharpness.  What I found was each generation had a degradation in quality, with the most pronounced being the first.

The Test Image:

A simple letter-form 'abc' with a horizontal line was chosen because the edges are more descernable than a photograph and can be accurately and reliably reproduced.  The image was originally built in PaintShop Pro X5 and was stored in a non-compressed PSPimage format, then converted to JPG for testing.

Note: This blogging tool only accepts JPG images and has already introduced its own compression.  Because of this, the red is not a pure red in the illustration above.  See below for a more accurate description.


Image details:
Original, uncompressed 'abc' as a .pspimage:
Size: 488 x 458px, 195,000 bytes

An ICC Color profile was not applied
All colors preserved without a loss in quality

Red color palette:
Red = 255
Green = 0
Blue = 0

In the test picture above is a small, one-pixel, pure-red dot just below the letter 'b''s bowl.  The dot is pure red and is not anit-aliased.  Along with this dot are several other anti-aliased pixels, illustrated below, and each will be monitored for changes.  Note the red line marking the base of the font.  This line is anti-aliased and is not, nor expected, to be a pure red.  Magnified 3000%, the areas of interest are shown in this illustration.

Compression

If allowed, all picture / photo-editors, by default, compress images -- they will do this with both their native and JPG formats.

For example, in PaintShop Pro (PSP), both the .pspimage and .jpg menus have default compression settings.  For these tests, compression was turned off during the initial File-Save-As.  Adobe Photoshop has similar settings. 

Compression Settings - Click for larger view

When the test file was saved at various compression levels, the file-sizes were:

Test Image File Sizes:

.pspimage - Original	195k  (non-compressed)
.pspimage - default compression lzh	16k  (image not shown in this post)
.jpg - Generation 1	14k
.jpg - Generation 5	17k  (note larger filesize due to artifacts)
.jpg - Generation 10	17k  (artifacts also shifting in color)

The author recommends turning off compression in all images. In the case of a .pspimage, the default compression is simply too aggressive and with JPG's, the space saved is miniscule.  

In PSP, change your defaults by opening any image and do a Save-As in each format (.pspimage, .jpg, .Tif, etc).  Click "Options" and choose your compression settings.  PSP remembers these settings for all subsequent images, until changed.  At the same time, consider disabling ICC profiles, in both the save-as and File, Preferences menus.


First Generation .jpg

Saving from the original, uncompressed .pspimage to .jpg, dropped the file size from 195k to 14k - a remarkable drop in size.  Even though PSP was told to not compress .jpgs, by their very nature, does some compression.  Here is the first-generation results, at 3000% magnification:

It is evident that the one-pixel red is no longer red.  Its RGB values should be
R=255,
G=0,
B=0,

but it has already digressed to
R=103,
G=65,
B=64

-- more of a brown than a red.  The first illustration in this article is that first-generation file uploaded to this blog entry.

Also notice the anti-aliasing pixels, they have shifted to brown and the red-line has numerous pink artifacts 1 pixel above and 2 pixels below, which can be seen as a pink shadow under the red line.  As more generations are introduced, these artifacts will multiply and experience their own color-shifts.

5th Generation .jpg

Opening the first-generation .jpg, I made a minor editing change, not visible in the magnification, saved and closed, then re-opening and repeating four more times.  The fifth generation edit shows another fairly dramatic shift in color. 

The Red Pixel shifted to
R = 84 - a large drift from Red
G = 72
B = 74

Other anti-aliasing pixels had similar shifts.  The red-line has more artifacts, extending 4 pixels below and 2 pixels above.

(Note: The editor disguises some of the compression until the file is actually closed.  This is why the file was closed and re-opened during each pass)


10th Generation .jpg

The 10th generation edit shows these, less dramatic results -- after all, most of the red has been compressed out of the image.

All pixels danced around the previous values, with minor color-shifts downward.  This generation showed more artifacts as the previous artifacts drifted from their original colors. 


Side-by-Side results

 

(Again, this blogging tool only accepts JPG and by definition, the red-line has already shifted colors.  In reality, this uploaded image is a second-generation .jpg image.  The left-half is now a third-generation and the right is a twelfth.  And there is a problem with color shifts caused by your monitor, which are not corrected by an ICC profile.  In any case, the original editor shows a bold, new red line, that is pure red, in all of its glory.)

What does this mean?

Numerous edits to a .jpg will soften quality and mute colors.  This was a stark example and these changes are more difficult to detect in a normal photograph.  Photographs are not as compressible as this black, white and red image, but there is compression none-the-less.

There are other caveats with this experiment.  Other photo-editors may behave differently, but I suspect they all do nearly the same thing.  Finally, PSP did not have a zero-percent compression setting for jpg files -- the best it could do was 1%.  I suspect all JPG's must be compressed.  Consider the first generation save, even at 1%, still dropped the file-size from 195K to 14K -- clearly it is being compressed even though I moved the slider-scale to the far-left.

Recommendations:

For the best quality photos, follow these recommendations

• Do not allow the camera to save as .JPG - even at high quality.
• Save images as RAW.
• Confirm the photo-editor can read the camera's proprietary RAW format.
  ---------------------------------------------------------------------------------------------
• Edit photographs in the editor's native format
  (for example, in PaintShop Pro, use .pspimage, Photoshop PSD / PSB).
• Turn off native compression in the editor.
• Convert to .JPG as a last-step, if sending in email or posting online; keep originals.
• If editing in .JPG, turn off file-save-compression!

Of course, this impacts file-sizes and large files can be impossible to edit on older computers or older 32-bit operating systems. 

Non-compressed file-sizes can be painful:

Consider a recent series of photos I took.  5 exposures for one scene.  Each exposure was stored as RAW, then HDR'd into a final image.  Look at these large file sizes -- they they will have their full fidelity:

Image-1    17MB  (16 megapixels, stored RAW, no compression)
Image-2    17MB - various exposure compensations, 2-5
Image-3    17MB
Image-4    17MB
Image-5    17MB
HDR Image - 114 MB -- for one image!

Read about this large HDR image in this related article:
Stanley Forest Burn Photo - imageLiner.blogspot.com


Conclusion:

This experiment was done in ideal conditions, using no compression and with a relatively simple image.  Now imagine if you allowed Paintshop Pro .jpg images to have a default 7 to 20% compression each time you saved the file!  Compression (and loss) would be rampant. 

An addition to this article showing an actual photograph is pending.
Your comments are welcomed.

Related links:
www.imageLiner.blogspot.com

Cellphone will not turn on; battery unexpectedly dead

Android Cellphone won't turn on and the battery is unexpectedly dead.

Symptoms:

• Cellphone will not boot or turn on
• The phone was warm or hot to the touch all day
• Battery appears dead even though it may have been fully charged the night before
• Hard booting the phone fails

• After charging the phone for a few minutes, it will then turn on
• Once charged, it behaves normally, with no battery issues

Likely problem:

A program or background task is running amok and is in a loop.  The CPU / Processor is running at full-speed, consuming all power.  The phone will feel warm or hot to the touch while this is happening, even if the screen is turned off and the phone is not in use.

Workaround Solution:

Restart the phone (press and hold the power-button and select Restart). 
If the battery is too dead to boot, charge the phone for a few minutes before attempting to power-on. Once powered on, note the battery level.

Restarting the phone kills the errant task.

Root Cause:

Locating the failed application will be difficult unless caught in the act.  Likely, this is not a predictable or re-producible event and your phone may be dead before you realize what happened.


In my case, I suspect the phone's built-in Camera app was the problem.  I had accidentally launched the camera earlier in the day and then turned off the screen and set the phone down without actually closing the program.  The phone was not used for the rest of the day, but I noted it was warmer than usual.  By the end of the afternoon, the phone was completely dead and would not power back on.  After a short charge, it recovered.  By this time, the Camera app was closed.  This problem was not reproducible.

Surfing the web for this problem shows all kinds of suspected applications can cause this problem, indicating it is not an application problem (e.g., not really the camera-app, etc.).  Instead, it is probably an OS task-scheduling issue and it can manifest itself in a variety of apps.

Other comments:


Do not bother using Advanced Task Manager or other such apps because with all reasonably-new Android operating systems, these programs cannot actually kill the task.  However, there is a paid application "Watchdog Task Manager" by Zomut, LLC, which shows which processes are hogging the system and it might be of interest, however, I suspect monitoring with Watchdog will not provide much useful information.

Your comments on this issue are welcome.

Related Articles:
Recommended Android Applications
Importing Android Phone / Address Book
HTC One X Review

Message: Your G-mail has been hacked

Synopsis:  GMail has been hacked is a scam

An SMS text message from number 18184735086 (1-818-473-5086, or other numbers),

Message #90261: Your G-mail has been hacked. Text back to VERIFY to take a call to reactivate your account. 

Or Your GMail Profile has been compromised.

Or Your Gmail has been compromised by hackers.  We need to call to verify your identity.  Reply with 'READY' when you are ready to take the call.

This is a scam.

Besides the fact that Google would never spell their service as "G-Mail" (it is Gmail), and would an official message from Google have such terrible English?  And would Google use the word "hacked?" - of course not.  With those observations aside, if Google did detect your account was compromised, they would shut it down and make you come to them to re-activate.  Like your bank, they do not send messages about account information.  All of these clues are flags that say ignore this message. 

What should you do:

1.  Nothing.

Do not reply to the text message.
Do not even bother changing your Gmail password.  You were not hacked.
Do not bother reporting the scam - it is fruitless.

This is a 'phishing' expedition, nothing more.

They are looking for information.  The perpetrator is building a database of likely users.  Your number was randomly generated and they hope you have a Gmail account.  Hotmail and Yahoo users can see similar messages.

Still Paranoid?

If you want to make sure your account was safe, log in.

If your account were hacked, the first thing they would do is change the password, locking you out.  Being able to login indicates nothing happened.  When logging in, do not use any links provided in another text message or email -- instead, go directly to "www.gmail.com" - this way you won't arrive at a spoofed-login screen.

Consider checking your account's last activity.  From the main Inbox, look at the bottom footer.  Click "Last Account Activity: Details".   This gives a full report.

Optionally, go to "Account" settings by clicking the upper-right pull-down near your account picture's thumbnail.  Choose "Account Information."  On the displayed screen, note the last login date (this is the login prior to this one) and note the country where it was logged in from.  If this all seems reasonable, once again, do nothing.

Google has more on Last Account Activity with more in-depth reporting:
https://support.google.com/mail/answer/45938?hl=en 

Related Article:
Confirm and Protect your Gmail Account with these easy steps


What if you Replied:

From around the web, those who did reply to the text message, report another text saying, "please enter the verification code -44- when we will call you."  There might be a message about your Voice Mail being setup or other such nonsense.

All that happened is you confirmed your phone number is accepting text messages and a gullible person responded.  Your name and number will be sold the the highest spam-bidder.  Expect a lot of Viagra messages.

One person, after replying, reportedly got a call from "Gmail Support" and was charged $99 to unlock the account and install "lifetime protection." Oh my gosh!  No!  She was completely ripped off and adding insult to injury, she gave away her Visa card and the farm. 


Google 2-Step Verification

When I first saw this message, I did a double-take, and then laughed.  I use Google's 2-step verification.  My account could not be hacked -- even if they knew my user-id and password, they can't log in.  

With two-factor authentication, I use my normal User-ID and password and then a few seconds later, Google sends a text message to my cell phone.  In a secondary Google login screen, I type the numeric code from the text message.  Only then can I open my account.  This technique works with both smart and non-smart cell phones.

It works like this: 
    Login with something you know (your credentials)
        + something you have (your phone)

The only way to get past this is to kidnap me and my phone.  Details on Google's 2-step verification can be found here: link:  Two-step verification

Update:  2014.11  -- Google now has an app, "Google Authenticator", which is faster and better than an SMS text message.

What if you don't have your phone?  You can't login.  However, when you first engage this service, Google provides a short list of longer emergency codes that only you know.  Print these and file in a drawer should you ever need them. 

Other GMail Protection Steps:
Keyliner Article:  Protect your Gmail Account.

Other details:

The phone numbers sending the text can come from literally hundreds of different locations.  Here is a sample phone number list from around the web:

2102016341   210-201-6341
2622084748   262-208-4748
3317257397   331-725-7397
3605620248   360-562-0248
4174131642   417-413-1642
5087841859   508-784-1859
6465048392   646-504-8392
7075066468   707-506-6468
7243157540   724-315-7540
8032655725   803-265-5725
8082655725   808-265-5725
8649771320   864-977-1320
9142364339   914-236-4339
9142364339   914-236-4339

The Reply message can be a variety of keywords, including
-SENDNOW-
-READY NOW-
-VERFIFY-
-REPLYNOW-
-SEND CODE-, etc.

All the changes in the message are to work past spam filters and to make the message sound unique.

Related Article:
Confirm and Protect your Gmail Account with these easy steps

Other articles of Interest:
Cleaning Windows Startup Programs (streamlining your boot times)
Using Microsoft's free virus scanner (MSE)
Speeding up slow USB devices

Interesting article on GoogleTwo-Step hacking.  This does not dim my appreciation for what Google has done:  Bypassing Google's Two-factor Authentication

Not that you will need this, because your account was not compromised:
Google Account Compromised:  https://support.google.com/mail/answer/50270?hl=enhttps://support.google.com/mail/answer/50270?hl=en

Gmail Protection Steps

GMail Protection Steps - If you suspect your GMail account was hacked, or if you want to confirm you have a rock-solid Gmail profile, use these steps. 

If your account were compromised (and you can still get into your account settings), look at these areas for common hacking techniques. 

Related Articles:
If you got a text message "Your Gmail has been hacked" or "Your Gmail account has been compromised," do not bother with the steps in this article; instead see: SMS Message: Your GMail has been Hacked.


1.  Confirm recent Login History

From the main Gmail Inbox, look at the bottom footer.
Click "Last Account Activity: Details".
Confirm the login activity seems reasonable.

Click for Larger View; "X" to return

2.  Confirm Connected Applications and Sites

Make sure hackers have not inserted a new program or site into your Gmail environment.

Open Account Settings
(Click pull-down arrow next to your account-picture (upper-right corner on Gmail main screen),
then choose "Account").

Click left-Nav, "Security"
In "Connected Applications and Sites", click "Review Permissions"
(When prompted, type your password to open the screen)

Confirm all connected sites, apps and services seem reasonable.
Revoke Access if you have doubts.


3.  Check Account Access

a.  From Gmail, click the Gear Icon (upper right corner)
b.  Choose "Settings"
c.  Click top-row tabs, "[Accounts]"

d.  Confirm "Grant Access to Your Account" does not list other Gmail accounts.
e.  Confirm "Mark conversations as read when opened by others"


4. Check Forwarding

a. In the "[Forwarding and POP/IMAP]" tab
    Confirm you are not (auto) forwarding messages to another email address.

b.  In [Filters]
    Confirm there are no unexpected Filters (which can also be used to forward emails)


5.  Enable Google 2-Step Verification

Consider enabling Google's Google's 2-step verification.  With this, your account cannot be hacked -- even if the perpetrator knows your username and password. I have done this myself for better than a year and this is highly recommended.

With two-factor authentication, I login with my normal User-ID and password and then a few seconds later, Google sends a text message to my cell phone.  In a secondary Google login screen, I type the numeric code from the text message.  Only then can I open my account.  This technique works with both smart and non-smart cell phones.

It works like this: 
    Login with something you know (your credentials)
        + something you have (your phone)

The only way to get past this is to kidnap me and my phone.

What if you don't have your phone?  You can't login.  However, when you first engage this service, Google provides a short list of longer emergency codes that only you know.  Print these and file in a drawer should you ever need them. 

Setup Steps:
a.  From your Account Profile (click pull-down next to your Account Picture)
b.  Choose "Accounts"
c.  On left-nav, click "Security"
d.  2-step Verification "Edit"

Details on Google's 2-step verification can be found here: link:  Two-step verification



Related Articles:
SMS Message: Your GMail has been Hacked

Google Account Compromised
Google has these instructions if your account were hacked and the password was changed:
https://support.google.com/mail/answer/50270?hl=enhttps://support.google.com/mail/answer/50270?hl=en

Excel Parse First Name, Last Name

How To: Reliably Parse FirstName, LastNames in Excel using a User Defined Function (UDF Macro). A single cell may contain a person's full name, as in "John Q. Smith".  Use Excel to parse out the constituent parts: FirstName, LastName, MidName, Suffix and the salutation.  New version 2013.06.11 includes support for prefix-Salutations and multiple suffixes + minor bug fixes.

Preamble:
Parsing a person's name into sub-parts is difficult. There are issues when the person's first-name is an initial plus their second name, as in "J. Alan Smith".   Other names, such as Last Names, may be composed of two words, as in "Alan Van Doran", "Bobby Mc Farland" or "Jill Saint John".  A standard parsing routine may confuse "Van", "Mc" and "St." as middle-names and would assign only the "J" to J Alan's first name.

The macros referenced in this article see through all types of naming issues and are able to parse most (English) names into their constituent parts:

FirstName
LastName
MidName
Suffix (Jr., Sr., PH.D, III, MD, etc.)
Prefix / Salutations (Mr., Mrs, Senator, Cpt., The Right Reverend, etc.)


What it Handles:

These routines correctly interpret all manner of English (and likely other language) names, including those with:

• First-letter initials, punctuated or not  (J. Smith, J Smith)
• First-letter initials, with a second name assumed as first= ("J. Alan")
• Two-letter firstnames (JP Morgan)
• Two-letter first names plus mid-names (JP Alan Morgan)
   
• Hyphenated first and last-names
• Names, such as Van, Vander, Mc, Mac, Saint, Le, Le', De, etc.
• Common Suffixes (Jr., Sr., "J. Smith III", PH.D, PHD, MD, M.D., DDS, etc)
• Common Suffixes, punctuated with/without commas (J. Smith, Jr. returns "Smith")
• Multiple-word suffixes, punctuated with at least one comma are detected properly
   
• Names with extra embedded / internal spaces
• Three, four and five-word names (Mary-Anne Lynn Miller, etc.)
• Single-word names ("Smith", "Cher", assumes LastName)
• Can separate common and uncommon Salutations, such as Mr., Mrs, Lt., Fr., Reverend, etc
• All names are returned in the upper/lower case, as typed.

Where this routine struggles:

There are some areas where even humans have a hard time interpreting.

• Names such as "Mary Ann Smith", where "Mary Anne" is the first name.  Ann will be tracked as a middle-name. 
  
  However, "Mary-Ann Smith" will parse Mary-Ann as a first name.  If you have control over the original data and can insert a tilde~, "Mary~Ann Smith" will parse as "Mary Ann" as the first name, with the tilde removed.  This is a programming trick which might be useful.
   
• Similarly, "Kathy Smith Jones" will parse as a mid-name "Smith" and a last-name "Jones". 
  
  Hyphenated last names, such as "Kathy Smith-Jones", will parse correctly with the last name as "Smith-Jones". 
   
• Common multiple-word suffixes, without a leading comma are detected with less accuracy.  Uncommon suffixes, such as many educational-degrees, are not detected and will be incorrectly treated as last-names.  However, this logic is easily modified.  I would appreciate a comment with all of the possible suffixes.

Other comments:

If the source last name has a special character (~ tilde), "Kathy Smith~Jones", the last name will be parsed as "Smith Jones" (with the tilde removed) - this is a programming trick that may help you if you have control over the name assembly. 

If the name has a middle-initial, the last name will always parse correctly, no matter how long or how poorly punctuated, as in "Kathy Q. Smith Jones".  Middle initials are detected as single-character values, with or without punctuation (Q, Q.).



Here are some normal and unusual names this routine can handle:

John Smith
John Q. Smith
John Q Smith, Jr.
John Q Smith,Jr  (No space after comma)
John Q. Martin Smith, III  (simple suffix)
John Martin Jr. (simple suffix, no comma)
John Martin, Jr. MD  (suffix with leading comma: "Jr. MD")

Smith (Missing first and midnames, detects as last name)

J Smith
J. Smith
J. Alan Smith
J Alan Smith
J.P. Smith  (first-name = "J.P.")
JP Albion Smith
JP Albion Smith PHD
 
John Mac Neal (LastName = Mac Neal)
John Mc Neal (LastName = Mc Neal)
John O' Brian (LastName = O' Brian, O' Leary, etc.)
   John  Mc   Neal MD  (extra embedded spaces)
   (various Le', De' and other such names, with/without punctuation)
John Van Nuys  (LastName = Van Nuys, also Vander, Von, etc.)
Mac Hetherington (LastName = Hetherington, not Mac Hetherington)

Mary-Anne Jacob Smith MD
Tom Smith, M.D.
Mary Ann Nichole Smith (Ann as middle name; this may or may not be correct)
Mary Q Anne Nichole Smith Barney (a mess. LastName = Anne Nichole...Barney)
Mac Von Nuys, Jr (lastName = Von Nuys)
Lori Vander Hoff (LastName = Vander Hoff)


Numerous Prefixes are detectable:
The Hon Rev Thomas Smith  (First name Thomas, Last name Smith)
Cpt. John Smith
Mr. Allen Jones


Overview:

With the code referenced in this article, you can write your own Excel functions.  For example, you may be familiar with Excel's =Sum().  With Excel's macro language, you can write a function, such as "=ReturnLastName()", essentially inventing your own keywords.  These are called User Defined Functions (UDF). 

Even with no macro experience, the code from this article can be downloaded and added to your spreadsheet, ready for use, in under 5 minutes.  This can be done without writing a line of code. Once installed, the macros can be applied and used like any other Excel function.  Today, I used these very routines to parse a 200,000 row spreadsheet.

See this article for a tutorial on how to build and use User-Defined Excel Functions or follow these step-by-step instructions and use the code as-is.  

Functions:

=ReturnSalutation()
=ReturnSuffixName()

=ReturnFirstName()

=ReturnLastName()

=ReturnMidName()

Also referenced are utility functions, such as "=SuperTrim" and "=ReturnLastWord", which are found in the A800_UtilStrings.bas module, downloaded below.



Installation and Use

Source-code can be downloaded from the Keyliner public Gdrive site  (the code is too ungainly to post in a blog).  You are welcome to use these routines in your personal or commercial projects; as a courtesy, please leave the author comments in the code.

1.  Download two files from this link:
You do not need to register or login in order to download.  Highlight the files, then click the 'Download' button on the top ribbon-bar.

From this link, download these two files; both are required for this article: 
Keyliner Public Gdrive - Excel Macros

A240_ParseNames.bas
A800_UtilStrings.bas


2. Save the downloaded files to any directory, such as

C:\Data\Source\CommonVB\A240_ParseNames.bas
C:\Data\Source\CommonVB\A800_UtilStrings.bas

(or any other directory of your choosing)

3. Launch Excel and Enable Excel Macros.

Microsoft considers all macros a security risk and this can be a nuisance.
Do the following to enable the macros.  This is a one-time step for all macros.

• Launch Excel; click the Orb (formerly the File Menu); choose Excel Options
• Click Trust Center, Trust Center Settings
• Macro Settings
• Enable All Macros
• Older versions of Office have similar settings.

See also this article, which summarizes these steps:  Enable Excel Developer Options

4.  Open the sheet where you need to parse Names or open a blank worksheet to test the routines.

5.  Expose Developer Options

Office 2008
a.  Click the "orb" (or File Menu), Excel Options 
b.  On the left, choose "Popular"
c.  Click [x] Show Developer tab in the ribbon
d.  Click OK and return to the sheet

Office 2010
a.  Click File, Excel Options, "Customize Ribbon"
b.  In the second-column ("Customize Ribbon: Main Tabs"), check [x] Developer
c.  Click OK and return to the sheet


6.  Import the Downloaded Excel Macros
a.  Within the sheet, start the VBA Macro Editor by pressing Alt-F11.  The VBA Macro Editor will open in a new window.

b. On the tree-diagram, illustrated below, select your sheet ("Book1"), then:

• Other-Mouse-Click (Book1),
• Choose "Import File"
• Browse and select the previously-saved ".bas" module,
  e.g. C:\Data\Source\CommonVB\A240_ParseNames.bas
• You will see the code appear under the "Modules" folder (not illustrated)

c.  Repeat the steps directly above

• Import C:\Data\Source\CommonVB\A800_UtilString.bas  (the second macro file)

7. Close the Visual Basic editor by clicking the editor's "X". 

This returns you to the sheet and the macros are ready to use. 

Note: All the macro editor did was "attach" the code.  You are welcome to review the code from within the editor. 

Important: The macros will not save with the sheet unless you follow the Office 2007/2010 Save warning, documented near the end of this article.




Initial Testing

A.  Type a name "John Q. Smith" in cell A1.  Press Enter to commit.
B.  In Cell B1, type this formula:

=ReturnFirstName(A1)

Note:  You are not "running" these macros; they are executed as a function within the sheet.

You are done!
 
Note these other routines:
=ReturnFirstName
=ReturnMidName
=ReturnLastName
=ReturnPrefix
=ReturnSalutation

Also of interest, these general purpose utilities:
=SuperTrim (very cool)
=ReturnFirstWord
=ReturnLastWord (and others)


Dependencies:

The A240_ParseNames macros has an inter-dependency, where the =ReturnMidName routine depends on the =ReturnFirstName and =ReturnLastName.  In other words, you cannot parse the Mid name without first parsing the First and Last names.  This was done for efficiency and saves those routines from re-parsing previously parsed fields.

Because of this, build your parsing fields in this recommended order, with these formulas (technically, fields can be in any order, but the MidName formula may end up pointing to a column to the right - which is okay.).  See below for parsing prefix Salutations.

1.  Parse the Suffix first (this properly detects missing or blank suffixes)
=ReturnSuffixName(celladdress)

2.  Parse the FirstName next, using
=ReturnFirstName(celladdress)

3.  Parse the LastName next, using
=ReturnLastName(celladdress)


4.  Parse the MidName last, using
=ReturnMidName(celladdress, firstname, lastname)
Note the inter-dependencies; this field uses two other fields as input

For example, with a name typed in cell B3 (John Q. Smith, Jr.), build the =ReturnSuffixName formula in cell C3.  Continue with the other formulas, finishing with the =ReturnMidName in cell F3, as illustrated:

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

Once the formulas are built, you can "fill-down" columns C, D, E, and F, to calculate other names in the list. Commonly, once parsed, you would copy all of the names and Paste them using "Paste Special, Values only" -- converting them from formulas to fixed text.

If you were inclined to look under the hood, you would see the ReturnMidName macro's signature line in this VBA code.  Note the three fields passed into the routine:

Click illustration for a larger view; click right-X return

Prefix Salutations Dependencies

Although uncommon in most listings, you can parse prefixed-salutations from a cell ("Mr, Mrs., Dr., etc).  An ending space, before the first name is required as a delimiter.  These routines properly handle missing prefixes.

Examples:

Mr. John Q. Smith
Mr John Smith, Jr.
Reverend John Smith
The Hon Mrs. Mary Anne Jones
Pvt. Mary Horne


Common prefixes, such as Dr., Dr, Mr., Mr, are supported with and without periods.  But most multi-word honorifics, such as "The Hon. Mr." do not check for full punctuation (for example, two periods are not tested).  If needed, this behavior is easily changed.

=ReturnSalutation( )
The ReturnSalutation verb could not be efficiently incorporated into the original =ReturnLastName, =ReturnFirstName functions, and as-such, it was written as a stand-alone module within the A240 library.  Because of this, you will not be able to run =ReturnLastName, =ReturnFirstName routines against a prefixed name.  The salutation must be removed first.  This is easily solved.  Consider this scenario:

Column A:  "The Hon Mr. John Q. Smith"

Column B: =ReturnSalutation(A)   Resulting in "The Hon Mr."

Column C: =if (Len(B)=0, C, Mid(A, Len(B) + 2, 999))    Resulting in "John Q. Smith";  the +2 skips the trailing space and starts at "J" (John).

For all LastName, FirstName routines, point them to the non-prefixed Column C.

This concludes the tutorial on how to use these parsing routines. 


Possible Macro Errors:
If you get a "#NAME?" when typing =ReturnLastName, the library was not linked in properly or was not linked to this particular sheet (the macros are only available to the sheets where they were copied).  Review the steps above.  If a routine, such as "SuperTrim" or "FindLastWordPos" fails, confirm you linked in the A800_UtilStrings library.

Possible Errors:
Compiler Error, Variable not found
Starting in Excel 2010, Microsoft is much more particular about having to declare variable types, especially if "Option Explicit" is used or if, in the VBA Editor, Tools, Options, [Editor], [x] Require Variable Declarations.  The Author's code worked properly in Office 2007 and earlier, but failed in 2010.  New versions of the code has been uploaded, fixing this problem.


Excel 2007/2010 Save Warning:

When Excel 2007/2010 saves the sheet, it will save it as an ".XLSX" and will strip the macros out as it saves (Microsoft is doing this as a security precaution.)



When prompted: 'Do you want to save as a macro-safe) workbook', click "Yes"  or optionally, do a File-Save-As, and choose "Excel Macro-enabled Workbook (*.xlsm).

Caution:
If you modify the Macros and save the sheet, only this sheet will see the changes.  From the macro editor, re-highlight the A240 and A800 routines and "Export" them back to the 'C:\data\Source\CommonVB' routines, where they were originally stored.  This way, the changes can be used in other spreadsheets.


Related articles:
Excel Macros to Parse City State ZipCodes
Excel Macros to return First Word, Last Word, Supertrim, Count Words
Writing Excel UDF functions
Excel VLookup - A complete Tutorial
Parsing Salutations and Salutation lists

Excel Coloring Alternate Rows
 Enable Excel Developer Options

Originally published: 2011.10.22