Posted under Knowledge Base & MS Office
Permalink
Tags Tutorial
I initially investigated office-js to store excel macros using angular and typescript, but in the end there was not enough in the way of working examples online, and the platform did not appear fully typescript enabled – there were a number of posts about missing capabilities in the typescript interfacing.
I then looked at the old-skool VBA macro recording and this was much more straightforward, even if it is somewhat legacy going forward.
- To start with, you need to enable the developer tab via File/Options. This opens the way to all the macro capabilities.
- From this tab, you can then click to record a macro, perform whatever actions you like, then click stop recording.
- When you click to record a macro, you get the choice of current workbook, new workbook, or personal macro workbook. I would also have liked the option to record to a designated workbook of my own, but you can’t do this.
- Fortunately, Macros are easy to copy around as source via the Visual Basic option on the developer tab. This opens a VBA view which shows all VBA from all currently open workbooks at once, so copying VBA between them is easy.
- I will need to come up with a place to store my macros. As I am using them to reformat different transaction sheets to merge into a master workbook, it looks like the master workbook would be the place. Also you can use VBA to open any existing workbook so I could use VBA to open the workbook to import, from VBA in the master workbook, and then run the desired macros on them depending on the type of the imported workbook (which would be selectable on my open dialog).
- Merging and editing macros, and copying between workbooks, is easy in the VBA view, as they all end up as VBA source. I intend to record a number of small atomic macros to do single actions. Each is saved as a separate method in a VBA module, so I can easily add another top level method to call them in turn etc. and add other logic. Very little VB/VBA knowledge is needed (as you might expect).
Whilst all this is perhaps a bit legacy compared with office-js (which itself does not appear to be fully supported for typescript and local use on all platforms), it is just so much less hassle than office-js for what I am trying to achieve.
Comments Off on Storing Office Macros