-1

I have a pipeline in azure that runs a script once per month. The script invokes a VBA. The problem is I can't run this VBA in azure, since in order for the script to run it requires a copy of excel. Is there any way to automate the process of executing a VBA either within azure or somewhere else and then grab the resulting excel files? I'm open to any ideas. Where else can I run VBAs external to azure and then draw those files into azure blob storage.

Thanks

bmfy131
  • 41
  • 5
  • No, read the documentation: https://support.microsoft.com/en-us/office/work-with-vba-macros-in-excel-for-the-web-98784ad0-898c-43aa-a1da-4f0fb5014343 – HackSlash Aug 31 '22 at 23:11
  • @HackSlash saw this but was hoping there might be a workaround. So basically there isn't a way to make a container/VM, install a copy of excel on it, then run the excel VBAs there? It looks like my only option is to simply have my local machine run this externally, and then push the files to blob storage. – bmfy131 Sep 01 '22 at 00:06
  • 1
    This sounds like an https://xyproblem.info/ ... If you explain what you are trying to do we can tell you how to do it. I'm sure there is a better way to get where you are trying to go. – HackSlash Sep 01 '22 at 00:14
  • @HackSlash I thought I already explained it. I need to run excel VBAs somehow externally or if possible internally to my system. As far as I can see there is not a way to run VBAs inside the cloud, so the only other option is to run it on my local machine unless someone else has another idea. – bmfy131 Sep 01 '22 at 00:29
  • Is there a reason it has to be done using VBA? – braX Sep 01 '22 at 00:59
  • 2
    @bmfy131, nobody "needs to run Excel VBA." You "need" to accomplish some task through automation, and based on your assessment, you believe VBA is the best option (which may be correct). What Hackslash is saying is there may be a better way to accomplish whatever your real objective. I'd guess that's probably true. Typescript or Powerapps is probably a better avenue. – pgSystemTester Sep 01 '22 at 01:25
  • @pgSystemTester Thanks, I get what you are saying that I am taking a stap at maybe the wrong solution which is why I came here. Can I run power app in the cloud though? Unless there is a way to convert VBA to a python script and not have to use the excel program to perform that, that could work too. – bmfy131 Sep 01 '22 at 16:01
  • If you can find the correct question to ask you might find it was asked before: https://stackoverflow.com/questions/64401750/how-to-read-an-excel-file-stored-in-azure-storage-as-a-blob-file – HackSlash Sep 01 '22 at 20:38

1 Answers1

0

First, use Windows Task Scheduler to run the excel every month and use macro to save the result in some network place, onedrive or sharepoint; or sent it by email; or push into some database, etc... Second, use power automate and office script every month to handle the result file if necessary.

Abel Wong
  • 191
  • 4
  • Although this isn't exactly what I am looking for I appreciate the effort. It appears to be the only option I have this it seems impossible to run excel in the cloud (very surprising that is the case). It appears in order to run a VBA this must be done on a desktop copy of excel. So that leaves me with this option, run a cron-job/windows task scheduler and save the results to the cloud db. – bmfy131 Sep 01 '22 at 13:55
  • barf. If you need a windows service, write a windows service. Whatever you are doing through Excel VBA can be done without Excel. If you really want to use VBA you can compile your VBA code down to a real executable using https://twinbasic.com/ You can still automate Excel from twinBasic, if that's the output format. – HackSlash Sep 01 '22 at 15:44
  • @HackSlash would this twinBasic software be able to be hosted inside the azure cloud, intake existing excel files in a blob container and then output them back out as excel files? – bmfy131 Sep 01 '22 at 16:05
  • @HackSlash also throughout the website they say this library is a "work in progress" this does not seem like viable solution. This release looks like it could potentially be unstable and full of bugs. – bmfy131 Sep 01 '22 at 16:16
  • 1
    Now you're just beginning to explain the X behind your XY problem. Why are the files in blob storage? What are you doing to them? Without these details nobody can help you get to where you are going. twinBasic makes native executables so you would still need a computer to run them on. You can use Azure CLI, PowerShell, and Rest API to access blob data. So you likely need a PowerShell solution to pull the data down, process it locally, and push it back up. – HackSlash Sep 01 '22 at 17:39
  • @HackSlash Right so twinBasic won't work there. The requirement is that this program has to entirely hosted in a cloud server. The reason I am using blob storage is that it appears to be the only option in Azure for storing the excel format data, if there are others maybe that is a better solution. I am thinking of re-writing the VBA logic in openpyxl. But now the issue is once I have an .xlsm in blob storage, how do I read it out and process it in openpyxl. – bmfy131 Sep 01 '22 at 19:52
  • The only option for storing the excel data... From what? You still haven't explained what the hell you are doing. You're seeing the same problem no matter what tech you use, python, vba, twinbasic because those are all locally run programs. It sounds like you need a cloud native solution which is entirely different tech stack. https://azure.microsoft.com/en-us/solutions/cloud-native-apps/ – HackSlash Sep 01 '22 at 20:04
  • @HackSlash OK the idea is to ingest data coming from an external data source, so my system listens for incoming excel files, when a new one comes into their system it gets sent to our blob storage db which triggers a validation scrip to run. The other part of this system requires a script to run once per month, and that will send out to a list of clients information through email, but in order to generate the information needed for those email clients I need to run this VBA was attached to the original excel - so now I am at the point of finding a solution to run those VBAs. – bmfy131 Sep 01 '22 at 20:26
  • What is "your system" and can you leverage its validation script? That's where you want to run your code. What kind of script is it? – HackSlash Sep 01 '22 at 20:33
  • @HackSlash the system is the configuration of either Synapse notebooks linked into a Synapse pipeline, or through Azure Functions, either way I will have a timed trigger and a storage event trigger. Basically files will intermittently be submitted into the data portal we listen to. The validation script is nested inside either a Synapse notebook or Azure function and runs when a storage event trigger happens, then it takes that incoming excel file and runs validation logic checking fields for invalid data. If it's a good file it gets saved to a db, otherwise it gets deleted. – bmfy131 Sep 01 '22 at 21:05
  • Synapse notebooks are made for processing data. Everything you need is right there! https://learn.microsoft.com/en-us/azure/synapse-analytics/spark/apache-spark-development-using-notebooks – HackSlash Sep 01 '22 at 21:44
  • @HackSlash how? it processing data but not excel, and wont run VBAs. Back where I started. – bmfy131 Sep 01 '22 at 21:55
  • You just said your Synapse Notebook can check Excel fields for invalid data. What's the problem? Extract the data at that step. Use it. NO VBA required. Contact the person who wrote the Synapse Notebook code. – HackSlash Sep 01 '22 at 22:48
  • Ingest data from external data source, compare with data in original excel file, validate, sent to database, run regularly. I think use a combination of power automate and office script can handle those requirements. The problem is you need to give up your vba program and rewrite it into power automate and office scrpt. – Abel Wong Sep 02 '22 at 03:18