Click here to Skip to main content
15,880,503 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
I am not very computer savvy but wanted to know if there is a way that I could print different worksheets in Excel (each client has their own worksheet) to an specific individual location in One-Note (each client has a receipt section in their own file) automatically.

I am currently using this VB code

Sub SelectPrinter()

Application.ScreenUpdating = False
'Create variables
Dim ws As Worksheet


'Loop through all worksheets and save as individual PDF in same folder
'as the Excel file
Application.Dialogs(xlDialogPrint).Show
For Each ws In ActiveWorkbook.Worksheets
If ws.Name <> "Support Provision" Then
If ws.Visible = True Then
ws.PrintOut
End If
End If
Next
Application.ScreenUpdating = True
End Sub

Just not sure how to modify it to Open One Note and then if there is a way to open specific files in One Note.

What I have tried:

Sub SelectPrinter()

Application.ScreenUpdating = False
'Create variables
Dim ws As Worksheet


'Loop through all worksheets and save as individual PDF in same folder
'as the Excel file
Application.Dialogs(xlDialogPrint).Show
For Each ws In ActiveWorkbook.Worksheets
If ws.Name <> "Support Provision" Then
If ws.Visible = True Then
ws.PrintOut
End If
End If
Next
Application.ScreenUpdating = True
End Sub
Posted
Comments
Richard MacCutchan 4-Apr-21 7:22am    
The xlDialogPrint dialog should list OneNote as one of the available printers. So you just need to select that and then tell OneNote where to save the printout.
RedDk 4-Apr-21 14:21pm    
I'm no expert using OneNote, it was only recently that I even got the desktop app to respond with anything other than a double error message by hitting the RESET button under Windows 10 "app" control, but I'd say selecting that "print" using VBA in Excel, particularly through a loop WITH OneNote as your target, is going to be extremely unlikely to be easy ("Print to OneNote"). It's clear enough in Excel that you can do this manually but finding the Add-In name or the Office tlb or other library, accessible or not through Object Browser of VB Editor in Excel is an experience which is not for the faint-hearted. Does xlDialogPrint list OneNote? If it does be prepared to GET a dialog poping up FOR EACH AND EVERY item you wish to send to the individual locations. My experience is that that pop-up can't be made invisible or silent or in any other way suppresed even in a loop from VBA.

[EDIT]
From: https://www.excelforum.com/excel-programming-vba-macros/468018-controlling-the-cancel-button-in-xldialogprint.html

res = application.Dialogs(xlDialogPrint).show
if res = False then
' dialog cancelled
exit sub
End if

Looks plausible?
[/EDIT]


This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900