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.

[knobhead]

I'd love to see this feature come alive. Would a bounty help? 🤔 🤑

[serkonda7]

@knobhead a bounty would definitely be motivating and might allow me to dedicate time for this feature.

[knobhead]

Great! I have no idea how to set that stuff up, but i'd chip in 50EUR or so

(I'd prefer IBAN/SEPA payment, however. paypal et al suck policy-wise).

[serkonda7]

@knobhead I wan't to give you the estimate, that I will start on this feature in October.
Payment with IBAN is fine. I will look if this can be setup with GH Sponsors. Otherwise just write me a mail pls.

[serkonda7]

@knobhead sorry for the long time without update. Other important tasks slowed the progress.
I have a almost working prototype for reading vba from a document that should be finished kinda soon™

Bringing the code back into the document is however a lot harder than I anticipated. Thus I will for now do not further work on that part.

[Beakerboy]

As an update on my side, I have a project that demonstrates a GitHub workflow to develop VBA. Devs save their code in text files in Git, and GitHub actions are triggered to lint the code automatically, and create an xlam when a release is tagged.

[itzTheMeow]

any update on this feature?

[knobhead]

I'm planning to follow this approach here:
https://code.whatever.social/questions/131605/best-way-to-do-version-control-for-ms-excel#2003792
(scroll down to find refined versions).
Adds a bit of code and complexity, but is fine for my workflow as long as serkonda7 is busy.

[serkonda7]

Due to personal reasons, I have to sadly admit, that I won't be able to actively develop new features for vscode-vba. So no native import/export for this extension developed by me:(

Tho I will still maintain this repo, moderating issues, making releases and merging PRs from others.

Regarding a export/import feature, there have been several workarounds and solutions posted here, that might work well for you.