Office and VS Code synchronization (macro export/import)

[serkonda7]

What problem does the feature solve?
Keeping office macro project and a project with plain text source files is quite hard right now.
One has to either do manual copy and paste or rely on (unmaintained) 3rd-parties or some custom hacks.

Therefore some code should be developed, to solve this task.

Requirements

General

• Should be written in JS/TS for optimal integration into VS Code
  • High-quality solutions in other languages could be integrated temporarily until a port happens

Document to Source (Read)

Requirements

• Read the vbaProject.bin from macro documents
  • The documents may not be encrypted
• Uncompress it into plain text
• Write this text into one file per module/class
  • Extraction of code in Form files is not required initially
• Read the code from all these files and compile it back into a vba project

Source to Document (Write)

Requirements

• Read source code from a directory
• Compile it into a new vbaProject.bin
• Replace a office document's vba project with the new one

Optional

• Backup the old vba project

[serkonda7]

https://docs.microsoft.com/en-us/openspecs/office_file_formats/ms-ovba/575462ba-bf67-4190-9fac-c275523c75fc?redirectedfrom=MSDN

[Pluckerpluck]

Just adding information here, but XLWings (which is still maintained) provides this functionality. It's not perfect but it works as a stop-gap until this extension implements similar.

https://www.youtube.com/watch?v=xoO-Fx0fTpM

It's a little oversold (omg! line numbers!), but it does autosync. With better autocomplete in VSCode this could be really powerful.

---

I will also point out, that XLWing's method (which I assume is not modifying the file directly) is likely superior to trying to modify the Excel file directly. The ability to edit VBA live with the file open is incredibly powerful.

[serkonda7]

@Pluckerpluck thanks for sharing this. It looks quite promising however for VS Code a JS/TS implementation is needed to be fully portable and compatible.

[jozefizso]

Hi, would something like https://github.com/NetOfficeFw/vbamc compiler help with this?

We have source code in .vb files and those are compiled into Office macro document.

[serkonda7]

@jozefizso thanks for the suggestion. Although I don't think it's very helpful.
If I understood the docs correctly, your compiler creates a new document without copying non-macro contents.
Also for this extension a functionality to extract code from docs if required too.

[jozefizso]

The tool creates final document from a basic empty template but it is quite easy to change it and use any document as the source.

[sancarn]

See @Beakerboy's vbaProject-Compiler for this feature :). Written in Python which isn't super ideal for VSCode, but the python could be compiled to an executable exe for the sake of this project.

[Beakerboy]

My project is not quite done, but it’s getting there and I’m willing to take advice, testing, and pull requests from any potential users. The linked project takes the raw text sources and creates all the streams. I have another project with the goal of creating OLE files from a set of streams.

[serkonda7]

See @Beakerboy's vbaProject-Compiler for this feature

@sancarn this looks promising.

I lack the resources to work on new features or integrate this into the extension right now.
If you want to fiddle around a little, maybe i can provide some assistance.

My (initial) approach would be to add a command to the extension that downloads/updates the compiler. And another command that will execute it using a Python version found on the users system.
This might be a little bit easier than setting up a continuos build system.