I've been using VBA to automate Excel for a few years. Lately I've been having trouble sharing my macro-enabled workbooks with colleagues. Sometimes their virus-protection software deletes the files and I've even had gmail strip them out of my emails.
I've heard of Office-JS and using JavaScript to make add-ins, but this seems like too much overhead. I don't want to build and host an add-in, I just want to be able to effectively share my work in automating Excel with colleagues, both in my company (a university) and out.
I've thought about sharing the VBA code separately from the workbooks, but some of the people I'm working with are not the kind that can effective import code and forms etc.
Is there any advice for how I might be able to automate Excel without the headaches of hosting an add-in or working with macro-enabled workbooks?
CodePudding user response：
No, it is not possible to automate Excel in-proc. And I'll explain why... You can use any script languages like JavaScript from Internet Explorer to automate Excel (out-of-proc), but most of web browsers (where your script code can be run) are not aware of COM servers and this technology in general. So, it doesn't make any sense.
Office Web add-ins are multiplatform which means the code can be run from a web browser (O365) or Office desktop clients and etc. You have been given a valid advice - to develop an extension which can be deployed separately, you just need to transfer the workbook without any code.
You may also consider developing a VSTO based add-in - it can be a document-level or application-level add-in. In that case you need to create a separate add-in's installer. Web add-ins simplify deployments of your Office solutions a lot.
You may also think of Office Scripts. They are designed for secure, cloud-based solutions. But, currently, Office Scripts are only supported in Excel on the web. Read more about them in the Differences between Office Scripts and VBA macros article.
Finally, consider uploading your workbooks to a shared folder on any file server such GDrive or OneDrive and send a link instead. Hope it makes sense.
CodePudding user response：
JScript can and it's built in to Windows. VBScript can too and is also built in. Any COM programming language can control COM servers like Word etc.
One important thing to note for VBScript. When external programs run using late binding there is no access to constants. You enter the numeric value of the constant. EG you use 5 not wdBold. You look it up in Excel's Object Browser (press F2 in VBA editor).
Set myExcel = CreateObject("Excel.Application")
Msgbox myExcel.name
If you have excel installed copy lines above into a file called myscript.vbs. If not just type in it msgbox "hello world". Windows comes with the following languages built in. VBScript, JScript, It also come with compilers for JScript.Net, VB.NET, C# (see C:\Windows\Microsoft.NET\Framework\v4.0.30319 and jsc.exe, csc.exe, and vbc.exe). All can access COM automation. Windows Scripting Host runs VBScript/JScript outside of a browser - type in command prompt wscript /?.