How to port OneNote 2010 vba scripts (hosted in a XLSM file) to work in Onenote 2016

2
votes

I would need help to get unstuck in porting from Onenote 2010 to OneNote 2016 an Excel VBA host toolkit I made

TLDR:

a) what object libraries should I actually see/reference if I want to use Excel 2016 VBA host to automate OneNote 2016 (desktop app)? I seem to be able to link only to Onenote 12 object library, there is a V15 libray but yields "name conflict" error when I try to reference even if I unclick the rest. Is my installation wrong? (Win10+Excel/ON 2016 32 Bits)

b) how the vba call to getpagecontent() should be in OneNote 2016, in particular what is the schema parameter, a string constant?, should I care?

Longer version

In https://github.com/jceresearch/onenote_vba_host (public) , I maintain an Excel spreadsheet with a few scripts to do things like exporting in bulk pages as an individual doc/pdf, including its embedded files alongside. Also does auto numbering of pages, and auto adding things in pages. It adds a menu in the ribbon you can see there the options.

The code is messy but works with Onenote 2010 fine. I will need soon to work in 2016.

The key part of the 2016 code is in the class code, to call the OneNote up:

Set app = New OneNote12.Application

In my 2010 code/installation it is already OneNote14.Application, but the version of office 365 I have allows me to refer to the Onenote12 object library only.

There is also a "Microsoft OneNote 15.0 types" library but when I click it it yields error of name conflict, even if I remove the old library. There is also an extended type library that again Excel doesn't let me refer to.

So current referenced libraries are:

  • Microsoft Office 16.0 object library (needed for the extra ribbon element added)
  • Microsoft OneNote 12.0 object library
  • Microsoft XML, V3.0 (I had to downgrade from the V6 that Excel suggests by default)
  • Microsoft Forms 2.0 Object library
  • Microsoft VBScript Regular Expressions 5.5

With these, I can run the macros and call the OneNote app, even get the list of notebooks and sections.

However, OneNote crashes miserably when the host tries to run:

app.getPageContent sNodeID, sXML, piAll

I tried a few things like bringing just the basic data but same results, also tried various other notebooks, pages, sections.

I assume the issue may have to do with the old version of the object library and/or that in the documentation of getpagecontent there is a mention to using the xsSchema parameter (xs2013 etc). See https://docs.microsoft.com/en-us/office/client-developer/onenote/application-interface-onenote

Any ideas welcomed, and btw, if someone wants to help improve the toolkit, more than welcomed, get in touch.

Thanks!

1
excelvbaonenoteonenote-api
A name conflict means the library can't be fully loaded without colliding with something that's already in the global namespace (from another referenced library) - would it work to use late-binding instead of referencing the type library? – Mathieu Guindon
Referenced type libraries should also include the VBA standard library, STDOLE, and the Excel type library. BTW kudos for this - OneNote not being a VBA host application means you're likely in mostly uncharted territory. Good luck! – Mathieu Guindon
Many thanks Mathieu, I will give it a try. Haven’t tried yet late binding because it gets even trickier to develop and debug... no tooltip help, etc, like sailing to unchartered territory with heavy fog. – whydoesntwork

1 Answers

1
votes

Here is the solution I found by trial and error:

a. I made a silly mistake I didn't realise till I tested things in isolation. My class name I believe clashed with one of the newer libraries (clsOneNoteHandler possibly). Changing to a unique name seemed to solve most of the problems, notably preventing the load of the latest object library and ultimately making OneNote crash.

b. I managed to link to the Onenote 15.0 object library (not the extended one), and that seems to be compatible with my 2010 code. I noticed it runs noticeably slower (same machine after re imaging to windows 10 with office 2016 32 bits).

c. I Still kept the Microsoft XML, V3.0 library referenced and syntax to parse the onenote XML content, and works fine as far as I can see.

I need to keep regression testing it, but to me the most important feature was to to export pages as docs and its embedded files, and that still works in Onenote 2016.

Hope this will help someone somewhere there.

Regards and thanks for the comments!