Excel Geeking: Sending A Selection To OneNote With VBA

It’s been a little while since I’ve geeked out with some VBA that would be anything worth posting, which means that the routine I wrote today was just that much sweeter.

I recently discovered Microsoft OneNote. We have it and at work and it is the singularly best kept secret we have. If you have the chance to use this little gem, I recommend it.

I won’t get all gushy about OneNote here, but you can look it up online. In a nutshell, it is a singular place to keep track of all kinds of things, like notes (duh), to do lists, snippets of things from the web, documents from Word, etc. You can hit Record in a meeting and it will record and save an audio file of the conversation, which you can listen to later to assemble your meeting minutes. You can share notebooks with people across the network or the web–the point is that it’s a great application. It’s a lot like Evernote, if Evernote were fully integrated with MS Office. And OneNote has a great mobile app so you can access your notebooks from your iPhone or iPad.

Okay, sorry, enough gushing.

What I really wanted was some method for sending pieces of Excel worksheets to OneNote without a lot of headache. Some method for sending a selection of the worksheet to OneNote with a single command or keystroke. Yes, I know I can highlight the selection, go to File, go to Print, change the settings to Print Selection only, change the printer to OneNote, then click print. That’s a lot of mouse clicks. Five in total, with more if you want to revert back to your normal printer.

Can’t I just use a button or keystroke to send something to OneNote without all the hullabaloo?

I can now.

The routine below is basically nothing more than the automation of all the mouse clicks I mention above. Except they’re tied into a keystroke I’ve created upon opening the workbook. So now, to send something to OneNote, All I need to do is hit Ctrl+Shift+N.

The main routine is below.



Sub PushExcelContentToOneNote()
'*******************************************************************************
' Description:  This will take the selected content and print it to OneNote, then
'               reset the printer back to the original printer prior to the routine.
'
' Author:       Scott Lyerly
' Contact:      scott.c.lyerly@gmail.com
'
' Name:                             Date:           Init:   Modification:
' PushExcelContentToOneNote V1      21-MAR-2014     SCL     Original development
'
' Arguments:    None
'
' Returns:      None
'*******************************************************************************
On Error GoTo ErrHandler

‘ Constant declaratios.
Const sONENOTE_PRINTER      As String = “Send To OneNote 2010 on nul:”

‘ Variable declarations.
Dim sOriginalPrinter        As String

‘ Get the original printer first.
sOriginalPrinter = Application.ActivePrinter

‘ Make sure One Note is the active printer.
Application.ActivePrinter = sONENOTE_PRINTER

‘ Print to OneNote
Selection.PrintOut Copies:=1, Collate:=True, IgnorePrintAreas:=False

‘ Reset the original printer.
Application.ActivePrinter = sOriginalPrinter

Exit_Clean:

Exit Sub

ErrHandler:

‘ Since the 1004 error number is too broad, we’ll check the error description instead.
If InStr(Err.Description, “ActivePrinter”) 0 Then
MsgBox “Excel cannot find the OneNote printer on your machine.” & _
vbNewLine & vbNewLine & _
“Operation cancelled.”, _
vbOKOnly + vbExclamation, “PRINTER ERROR”
Else
MsgBox Err.Number & “: ” & Err.Description, vbCritical, “MICROSOFT ERROR”
End If

Resume Exit_Clean

End Sub




To set the keystroke, add the following in the ThisWorkbook module.

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Application.OnKey "^+n", ""
End Sub




Private Sub Workbook_Open()
Application.OnKey "^+n", "PushExcelContentToOneNote"
End Sub



Easy-peasy.