serkonda7/vscode-vba

Office and VS Code synchronization (macro export/import)

serkonda7 opened this issue · 18 comments

serkonda7 commented

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
Pluckerpluck commented

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 commented

@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 commented

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 commented

@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 commented

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 commented

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 commented

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 commented

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.

  • 👍1
knobhead commented

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

  • 👍1
serkonda7 commented

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

  • 👍1
knobhead commented

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 commented

@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.

  • 👍1
serkonda7 commented

@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.

  • ❤️1
Beakerboy commented

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.

  • 👍1
itzTheMeow commented

any update on this feature?

knobhead commented

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.