noslenwerd
Board Regular
- Joined
- Nov 12, 2019
- Messages
- 85
- Office Version
- 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
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
