0

At work we have an Excel VBA tool that we use on a regular basis to generate reports. Runtime can be quite long due to the number of reports we have to generate. Typically the way we cut down on runtime is we split up the report list into four groups, made four copies of the reporting tool, then ran group 1 on server 1 using copy 1, group 2 on server 2 using copy 2, etc so that they were all running simultaneously.

Wondering if it's possible to run them all on one server instead of four? I.e. have VBA start one tool, then start another in another instance, and so on. It seems like when I try to do it, only one can run at a time (although the multiple instances open fine and all the tools will run, just not simultaneously. Let me know if this isn't clear, thanks!

theholyhandgrenade
  • 80
  • 6
  • 5
    You're essentially trying to achieve multithreading. VBA (COM actually) is single-threaded. You can *simulate* MT by launching your Excel instances from VBScript. – Mathieu Guindon Nov 03 '16 at 20:25
  • 1
    Another option would be to trigger the report generation from `Workbook_Open` and then use shell a bunch of instances. VBScript is probably cleaner though. – Comintern Nov 03 '16 at 20:37
  • 1
    @Mat'sMug - VBA, but not COM. You can have an [MTA](https://msdn.microsoft.com/en-us/library/windows/desktop/ms693421(v=vs.85).aspx) in COM, but VBA is restricted to STA. – Comintern Nov 03 '16 at 20:40
  • @Comintern right. *takes foot out of mouth*. – Mathieu Guindon Nov 03 '16 at 20:46
  • This is relevant: http://stackoverflow.com/q/5721564/4996248 (close to being a duplicate perhaps) – John Coleman Nov 04 '16 at 00:55

1 Answers1

1

As alluded to above, you need to start 4 separate of Excel. See the link below for all details.

https://blogs.office.com/2013/06/03/opening-workbooks-by-running-separate-instances-of-excel/

Normally, you open Excel spreadsheets by either double-clicking on them in Explorer or by navigating to them from inside Excel. Using either of these two methods will result in the spreadsheets opening in a single instance of Excel.

See the link below for additional details of running multiple instances of Excel.

http://www.online-tech-tips.com/ms-office-tips/excel-open-a-new-instance/