Saturday, December 17, 2016

How to Move Excel VBA to a Production Environment?!

I was thinking of a quick-and-dirty solution for moving VBA to a server, but found it was essentially shot down by Microsoft a long while ago:

Considerations for server-side Automation of Office
https://support.microsoft.com/en-us/kb/257757

I have some other ideas of using an Excel VBA metaphor (in essence a stack of sheets) within a Node.js module, perhaps implementing a set of functions similar to Google Apps Script (GAS) to reduce the learning curve. A simple a way to move Excel VBA (which is very productive in a corporate Excel environment) to a web front-end for low volume self-serve production use is needed.

Any idea has dozens if not more people around the world thinking about it - maybe someone has already solved this problem and I can just use it?

There are postings on Stack Overflow to figure this out, but consolidated here for quick review. Here's an example of calling an Excel VBA Sub and Function from PowerShell. 

PowerShell code:

1:  $objExcel = new-object -comobject excel.application  
2:  $sFile = "c:\users\dsides\sides-work\powershell\TestCall.xlsm"  
3:  $objWorkbook = $objExcel.workbooks.open($sFile)  
4:  $objWorksheet = $objWorkbook.worksheets.item(1)  
5:  $objExcel.run('TestSub1', 'Word1 ', 'Word2')  
6:  $sRetVal = $objExcel.Run('TestFunc1','Word1 ', 'Word2')  
7:  Echo $sRetVal  
8:  $objExcel.quit()  


Excel VBA code:

1:  Option Explicit  
2:    
3:  Sub TestSub1(sString1 As String, sString2 As String)  
4:    MsgBox (sString1 & sString2)  
5:  End Sub  
6:    
7:  Function TestFunc1(sString1 As String, sString2 As String) As String  
8:    TestCallFunc = sString1 & sString2  
9:  End Function  

Looked at a few ways to put PowerShell/VBA behind a web front-end, but it's a bit too hacky and feels like it would be too fragile for a production environment. Here are a couple of links (not hard to find):

http://systemfrontier.com/powershell

http://www.poshserver.net/index.html


No comments: