Monday, July 01, 2013

Registry Hack - Add "Edit With..." Shortcut to Context Menu (Using Notepad++)

I've had to do this several times now, so it's time to write it down.  Here are the issues:

1. The existing 'Edit with Notepad++' context menu link generates an error on execution
2. Worse, it doesn't display at all through Multi-Commander

No like!

So here's how to fix it.  First, get rid of the existing context menu entry, which you'll find under HKEY_CLASSES_ROOT/*/shellex/ANotepad++64.  Now you're ready to add the new entry:

1. Go to HKEY_CLASSES_ROOT\*\shell.
2. Right-click shell and add a new key called "Edit with Notepad++" (or whatever you want to call it).
3. Now Right-click on the key you just created, and add a new key beneath it called "command".  The end result should look like this:

4. Within the command key, modify the default string to the full path of the Notepad++ executable in quotes followed by %1.  For example:

5. Go back to your original key (Edit with Notepad++ or similar) and add a new string value called "Icon".  Modify the value to the full path of the Notepad++ executable in quotes followed by ,0.  For example:

The changes will take effect immediately, and you should now have the item in your right-click context menu:

SQL Server 2012 Linked Server for MS Access

Here's the scoop: I've installed SQL Server, but I want to use Management Studio (SSMS) to query an Access Database.  I followed the directions, and also found out that the Jet OLEDB 4.0 provider is only available for 32-bit.  But now I'm getting this error:

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
Cannot initialize the data source object of OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "[server name]".
OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "[server name]" returned message "Unspecified error". (Microsoft SQL Server, Error: 7303)
There are some solutions to similar errors, but none of them are working.  What now?  Try running SSMS as an administrator.  In fact make sure it always runs with administrative rights by right-clicking the shortcut/executable, selecting properties, and checking the "Run this program as an administrator" box:

Once you do this, SSMS will have full rights to connect to your MS Access files (and any others you'd like to query).

Remove All Images From Excel Worksheet

So I have a spreadsheet I've extracted, and it has two columns of images in it (which I don't want):

Ok, so I'll just delete the columns like I would with any other data, right?

Hm.  That didn't work.  Are you really telling me that I have to select and delete these one-by-one? NO WAY!

Thankfully, there's a quick macro you can run to get rid of all images in a worksheet like this:

Dim s As Shape
For Each s In ActiveSheet.Shapes

Running this will remove all these pesky images in one shot.  No fuss, no muss.

Happy deleting!

Quickly Line Up Rows/Columns in Excel

Have you ever pasted a table into Excel and gotten results like this?

No like!

Wouldn't it be nice to line up and autofit all the rows and columns in one click?  Here's a quick macro that will allow you to do just that:

  Selection.ColumnWidth = 255 
  Selection.ColumnWidth = 9 
  Cells.EntireColumn.AutoFit 'fit the columns

Basically, it selects all the cells, makes the column extremely wide, then autofits the rows and columns.  The result is more like this:

Much better.  Enjoy!