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.
' 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: email@example.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
‘ 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”
MsgBox Err.Number & “: ” & Err.Description, vbCritical, “MICROSOFT ERROR”
To set the keystroke, add the following in the ThisWorkbook module.
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Application.OnKey "^+n", ""
Private Sub Workbook_Open()
Application.OnKey "^+n", "PushExcelContentToOneNote"
7 thoughts on “Excel Geeking: Sending A Selection To OneNote With VBA”
This is real value for time spent.
6 clicks to send to OneNote, 3 to reset printer = 8 clicks every time.
2 lines declare variables, 4 lines of active code = 6 lines written once (I don’t count error trapping or comments as running code. They are essential but should never run if the sub is used correctly).
This is why I love VBA, it automates everything and reduces human input error. As you said, a sweet routine. Great job Scott.
I’ve been using One Note for three years now. It’s saved my sanity while dealing with a merger and a complete business system change and all the meetings that come with those events. I got my boss and a some fellow employees on it. We now use it for some core business processes to make certain information available to all who need it, rather than just tied up in accounting.
I often use Snipping Tool (which comes with Office 2010) to grab bits of the screen and allows you to highlight things before pasting into emails and other programs. That has been a huge benefit during the recent business system change where we needed to quickly send messages to others to show what we were seeing. I see people who aren’t that sharp using computers have really grabbed onto this one.
This macro looks very handy for some things. But I can’t make it work! I had to change this line to include an equal sign to make it even run.
If InStr(Err.Description, “ActivePrinter”) = 0 Then
I just can’t get it to print using the Selection. It always just throws an error. If I change the print line to the following, I can get it to print an entire worksheet to One Note. Everything I know says it should work the way you’ve go it!
Worksheets(“sheet1”).PrintOut Copies:=1, Collate:=True, IgnorePrintAreas:=False
I replying from my phone so pardon the typos and brevity…
What you might need to try is changing the constant. It’s set up to print to OneNote based on my installation if Office 2010. If you have 2013, it would likely be different.
If you do have Office 2010, you might need to write Application.ActivePrinter to the immediate window to make sure you have the printer name exactly correct. I’ll try and post an update for that later tonight.
All that said, not sure why Selection won’t print. I’ll take a look at that too.
I did some research on my problem printing with the above code using Selection. It seems Excel 2010 has a history of not liking “IgnorePrintAreas:=False”. Deleting that allowed me to print to One Note just the way it should. I found a comment that says “ignorePrintareas is incompatible with printing a selection. Not sure why it worked for you!
Another learning experience for me. Now I can enjoy a Saturday night.
Is there any way to choose which tab within the OneNote section it prints to? It seems like it just opens up the dialog box in OneNote to then manually choose. Also, if you know a way to print (i.e. paste) from excel so I retain the spreadsheet formatting instead of a picture, that would be much appreciated as well. Thanks in advance!