3

We have a macro that takes approximately 20 minutes to complete. We've got a c# winform that our user has access to - it contains a "Run Macro" button. I'd like it so that the user can hit the button and then actually close the form and then in 20mins time see the output from the macro in a pre-defined directory.

Is it simply a case of creating a delegate and letting the delegate run the macro or is the above not a trivial matter?

Couple of ideas which might be viable:

  • Just hide the form after the user presses the button? Drawback is that the macro will still be tying up the user's cpu within an instance of Excel?
  • Could the winForm on UserX's pc somehow kick-off a program to run on our 'Excel' server? This sounds far fetched but maybe someone can help.
  • Similar to the above maybe on the Excel server I could create a small daemon console app that is scheduled to run every 2 seconds and check a table on a shared db to see if any macros need running. The user's winform could update this control table and the daemon then goes to work.
whytheq
  • 34,466
  • 65
  • 172
  • 267
  • 1
    Do you have any experience at all with automating Excel from .net? Will the workbook with the macro be open already? I imagine you could look into using `Application.Run` to achieve this. – Tim Williams Jun 09 '12 at 23:59
  • No, you cant you must have the C# app left open as it will have a handle on the Excel process. Do you want me to go into more details, just check last couple of my questions.. VSTO, Excel ones. Let me know if you want me to research you the answer? – Jeremy Thompson Jun 10 '12 at 00:57
  • + 1 @whytheq: Good question :) – Siddharth Rout Jun 10 '12 at 02:07
  • @TimWilliams I have some experience using the excel interop library. – whytheq Jun 10 '12 at 13:17
  • @JeremyThompson alright Jeremy - if you could expand or provide a link to you previous useful answer it would be appreciated – whytheq Jun 10 '12 at 13:18
  • @AlfalfaStrange I'm just planning at the moment but I have some experience with c# and realise that sometimes things are more complicated than they first appear. Therefore I'm hoping to get an answer which will atleast give me an idea of problems I might encounter. – whytheq Jun 10 '12 at 13:21
  • 2
    This shows you how to run a vba macro from C#: http://support.microsoft.com/kb/306683 – Tim Williams Jun 10 '12 at 16:57
  • @TimWilliams thanks - problem is that this will still be kicking off a 20 min macros on the user's pc. I'd like the user to be able to run the macro and see the results 20minutes later but not have it running on their pc. (Question has creaped a little bit Tim - apologies!) – whytheq Jun 10 '12 at 17:00
  • @whytheq - if your don't want the macro to runs on the users machine then yes you'd have to run it elsewhere ;-). What does your "Excel server" look like? And perhaps a more obvious question: what is the macro doing that takes 20min to complete? Is it possible it could run much faster with a bit of tweaking? – Tim Williams Jun 10 '12 at 21:05
  • @TimWilliams hmmm ....I've been tweaking these routines for about 10yrs mate! The ones that take a while to complete are creating 40 xlsx files and saving them to varoius locations on out file server. Each xlsx file has 10 sheets - each sheet with thousands of calculations that have to be exexcuted before making values only and saving. As our environament developes I'm moving as many of these calcs and aggregations to MDX and SQL but it's not an over night process! – whytheq Jun 11 '12 at 09:31
  • @TimWilliams - also Tim this question is quite hypotheical; "what if the macro runs for 20mins". Actually even if it runs for 2mins I'd rather it didn't have to run on an instance of excel on the user's PC. If the user interferes with that instance then there will be problems. – whytheq Jun 11 '12 at 09:33
  • 1
    Some suggestions here for running a process remotely: http://stackoverflow.com/questions/428276/how-to-execute-a-command-in-a-remote-computer – Tim Williams Jun 11 '12 at 15:08
  • @TimWilliams you asked what the Excel Server looks like - just an old server IT don't use anymore with MS Office installed + other basics. Then got a profile created which is always logged on and running any tasks. +1 for link – whytheq Jun 11 '12 at 19:34

1 Answers1

3

Yes it is simple.

Use C# to launch a VBS File. The code will be something like

System.Diagnostics.Process.Start(@"cscript //B //Nologo c:\MyScript.vbs");

The above code will launch the VBScript with no prompts or errors and no shell logo.

Next Step is to create a vbs file.

Open Notepad and paste this code and save it as a MyScript.vbs file

Dim xlApp, xlBook

Set xlApp = CreateObject("Excel.Application")
'~~> Change Path here
Set xlBook = xlApp.Workbooks.Open("C:\Test.xls", 0, True)
xlApp.Run "TestMacro"
xlBook.Close
xlApp.Quit

Set xlBook = Nothing
Set xlApp = Nothing
Siddharth Rout
  • 147,039
  • 17
  • 206
  • 250
  • very familiar ... I've been using lots of vbs files to run macros but I must admit I'm trying to move away from vbs files. Why not open Excel and run TestMacro from within the c# exe? – whytheq Jun 10 '12 at 13:14
  • 2
    @whytheq: Because then you will have to wait for 20 mins for the macro to finish ;) – Siddharth Rout Jun 10 '12 at 17:37