Alternative solution for process that relies heavily on VBA in Microsoft Excel / PPT / Word

noslenwerd

Board Regular
Joined
Nov 12, 2019
Messages
85
Office Version
  1. 365
Hang with me a bit as this might take a while to explain.

I developed a process for my org that relies heavily on macros in excel, word, and powerpoint. I am the only one in the org who can maintain/update it, so we are looking for a solution that can be more intuitive to make sure it can continue to evolve. Whether that is a custom software we develop, or something commercially available we are fine with that. Looking for suggestions

To simplify the goal to input unique customer information, then based on the information entered it can then be used to open a powerpoint library to create a custom slidedeck, and populate a word document with custom information.

Below is a high level of what the process looks like now.

- Employee works a case with a customer (Let's say Drews Donuts), opens up a file called "case-loader.xlsm", macro is called that creates a new folder based on one field: Customer name, copies another excel workbook called case-template.xlsm into that folder, renames it to the customer name. ie: Drew-Donuts.xlsm. So now our path is something like C:\tempname\cases\DrewsDonuts
- Employee uses Drews-Donuts.xlsm to enter in relevant information about the customer into predefined cells (contact info, needs, solutions, website etc.)
- Employee clicks on a new sheet called 'export' in the Drews-Donuts.xlsm workbook, selects relevant topics for the customer call with wingding checkboxes. Let's assume the categories are Content Management, and Email Marketing. There are over 150 categories
- Employee clicks 'Create PPT,' macro looks for categories selected (Content Management, Email Marketing), opens up a PPT library (called master-slides.pptx), and finds slides with matching titles. Creates a new PPT file with those topics, and saves as customer name. In this case Drews-Donuts.pptx. So now we have C:\tempname\cases\DrewsDonuts\Drews-Donuts.pptx, and C:\tempname\cases\DrewsDonuts\Drews-Donuts.xlsm
- Employee clicks 'Create Word Doc,' from Drews-Donuts.xlsm and a a macro is called that opens word, calls a macro within word that finds the relevant tag. In this case ||contentmangement|| and ||emailmarketing||, macro then finds an html file stored locally that matches those names ie contentmangement.html, and emailmarketing.html. Inserts content from the html them into the Word Doc. Creating C:\tempname\cases\DrewsDonuts\Drews-Donuts.docm
- Macro from excel sheet (Drews-Donuts.xlsm) takes information from the customer (contact info, website etc), and looks for tags in the newly created word doc (Drews-Donuts.docm) in the content imported from the html files. ie: ||custname||, ||website||, get replaces with John Doe, johndoe.com. Traditional mailmerge would not work in this case due to a character limit, and styling limitations between excel and word.
- Macro in Word is automatically called that looks for html markup such as '<'b'>'bold'<'/b>', '<'i'>'italics'</'i'>' (importing from html file), and applies the style accordingly.

Again, the process for adding new topics, and new fields in the master xls file is very VBA heavy. Are there any SaaS solutions, or a locally developed software that might give us more flexibility? Maybe something using ASP?

Cross posted at: Alternative solution for process that relies heavily on VBA in Microsoft Excel / PPT / Word
 

Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop

Forum statistics

Threads
1,213,543
Messages
6,114,240
Members
448,555
Latest member
RobertJones1986

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top