Excel to Word VBA. Macros blocked by enterprise admin. What path to go down now?

[noslenwerd]

With the help of many on this board, over the course of the last month I create a pretty elaborate process using Excel/Word VBA for my org at work. I put over 40 hours into it, and was slated to save my 24 peers around 30 hours of work on a monthly basis (720 hours total monthly for those keeping score).

The process did the following:

• Collects/formats data entered into Excel365
• Creates a list of cells that are populated which is scrubbed and prepared for transmission to Word.
• Based on checkboxes selected in Excel365, list out the local folder of over 100 topics used in our customer reviews
• Opens an instance of Word. Populates data using find/replace
• Looks at file locations (ie: C:\Worksheet\topic1.html) listed in the Word365 Document, and imports the content in the local html files.
• Uses Word VBA to apply styling to the html data (ie: <b>bold</b> would then show as bold).
• Saves a new instance of that Word Document as CompanyName.docx

The problem is I got the dreaded alert below while trying to install the process on a co-workers machine. I am not sure I will be able to use macros with our corporate policy. Which as a sidenote is a bit odd to me because the process worked on my machines that have the same settings.

So what next? I do have a background in computer science, but haven't programmed in over a decade. My main question is:

What program language is similar to VBA, that will be able to execute a similar process for what I have listed above? I am willing to create an actual executable program, that does the following:

• Open and instance of word, and populate a large variety of data that can be stylized (including hyperlinks, images, styling of text etc).
• Collect data entered for a particular customer.

The new process can circumvent the need for Excel, but I do need to allow the end user to view this data in Word.

Any advise on where to go from here would be incredibly appreciated.

This has been cross posted at vbaexpress.

 

[kennypete]

Since you want something "similar to VBA", if your Enterprise policy doesn't block it, VBscript may be a solution for you. I too have Word macros (but not Excel macros) disabled on my work laptop, yet VBscript runs fine. Of course, if VBscript is dsiabled this will be a non-starter, but there's a fair chance it won't be.

Looking at one requirement solely:

• Uses Word VBA to apply styling to the html data (ie: <b>bold</b> would then show as bold).

The VBscript can do this, and the code will look familiar/similar for you, to illustrate:

1. A Word file, C:\Users\Home\Documents\VBS\demo.docx, with just this in it as plain text:

Demo
<b>bold</b>
End

2. A .vbs with this code:

Set objWord = CreateObject("Word.Application")
objWord.Visible = True
Set objDoc = objWord.Documents.Open("C:\Users\Home\Documents\VBS\demo.docx")
Set objSelection = objWord.Selection
Const wdReplaceAll = 2
With objWord.Selection
    With .Find
        .ClearFormatting
        .Replacement.ClearFormatting
        .Replacement.Font.Bold = True
        .Text = "[<]b[>][!<]@[<]/b[>]"
        .Replacement.Text = ""
        .Wrap = wdFindContinue
        .Format = True
        .MatchWildcards = True
        .Execute ,,,,,,,,,,wdReplaceAll
    End With
    .Find.ClearFormatting
    .Find.Replacement.ClearFormatting
    With .Find
        .Text = "<b>"
        .Replacement.Text = ""
        .Wrap = wdFindContinue
        .MatchWildcards = False
        .Execute ,,,,,,,,,,wdReplaceAll
    End With
    With .Find
        .Text = "</b>"
        .Replacement.Text = ""
        .Wrap = wdFindContinue
        .Execute ,,,,,,,,,,wdReplaceAll
    End With
End With

...when saved and run will replace the <b>bold</b> with bold.

Check out some other examples at Microsoft Word VBScript Script samples