0

I need to run multiple .vbs at a time. This is the only way I was able to find online: 

Dim objShell
Set objShell = Wscript.CreateObject("WScript.Shell")
objShell.Run "1.vbs"
objShell.Run "2.vbs"
objShell.Run "3.vbs"
Set objShell = Nothing

To do that I'd have create 1.vbs, 2.vbs and 3.vbs separately. Is there a way to input them all as part of one .vbs file? Something like 

Dim objShell
Set objShell = Wscript.CreateObject("WScript.Shell")

    Dim vbs1 as vbscript
    Dim vbs2 as vbscript
    Dim vbs3 as vbscript

    Set vbs1 =

    "Set objExcel = CreateObject("Excel.Application")
    objExcel.Application.Run "'C:\Users\test1.xlsm'!Module1.refresh"
    objExcel.DisplayAlerts = False
    objExcel.Application.Quit
    Set objExcel = Nothing"

    Set vbs2 = ' whatever code
    Set vbs3 = ' whatever code

objShell.Run vbs1 
objShell.Run vbs2 
objShell.Run vbs3 
Set objShell = Nothing

The purpose of this:

  1. I have ~50 excel reports with connections to SQL that need to be updated every day.
  2. To do that I've created a macro and added it to each of them. The macro refreshes connections/queries > refreshes pivot tables > removes the connections/queries > saves as a macro-free workbook in a specified location.
  3. I wrote .vbs scripts for each report. It just runs that macro in every Excel workbook.
  4. Instead of running every .vbs separately, I've created one main .vbs that references all prior created .vbs to run them at the same time.
  5. My question is if there's a way to do that without creating 50 separate .vbs files.
ln29st
  • 83
  • 2
  • 11
  • At this point, are you basically just combining 3 .vbs files into one? – BigBen Dec 13 '19 at 19:20
  • Put them one after the other in the file. Or see https://stackoverflow.com/questions/26089117/how-can-we-programmatically-know-the-synatx-error-using-msscript-ocx –  Dec 13 '19 at 19:26
  • 2
    VB is not VBA is not VBScript. They're not the same language just because they all start with VB, any more than a cat, a car and a carrot are all the same because they start with ca. Please use only the tags that are applicable to your post. Thanks. – Ken White Dec 13 '19 at 19:29
  • Correct, I'm trying to combine 3 into one in this example. I have many more (about 50) excel reports I need to refresh at a time, so trying to avoid creating 50 different .vbs for each – ln29st Dec 13 '19 at 19:30
  • 1
    What are you *actually* trying to do? Refresh a report? This seems like an X-Y problem. – BigBen Dec 13 '19 at 19:58
  • BigBen, I'm trying to automatically refresh multiple reports by running a single .vbs file that runs vba that refreshes queries/connections if that makes sense. – ln29st Dec 13 '19 at 20:03
  • `GetObject("C:\Users\test1.xlsm").RefreshAll`. Use copy and paste to do multiple files. None of your other code is necessary. –  Dec 13 '19 at 20:22
  • Mark, that could work, but I also need to refresh pivots, save all reports to a different folder as .xlsx without vba and connection. – ln29st Dec 13 '19 at 20:25
  • To me, it looks like you need to run a (the same?) macro in several Excel sheets and then do something with them. If so, you do not need to create and run several scripts. Just use one, and declare, or read in from FSO, the Excel sheets to work on as an Array and loop over them. If that is not what you are trying to achieve, I am probably misunderstanding your question. – Rno Dec 13 '19 at 23:45
  • Did you have a look at [ExecuteGlobal](https://www.vbsedit.com/html/25ebfa26-d3b9-4f82-b3c9-a8568a389dbc.asp)? – Wernfried Domscheit Dec 14 '19 at 11:45
  • Arno van Boven, Wernfried Domscheit, sorry for not being clear. I've added the purpose of this to the question. Don't think the sql refresh/pivots refresh/connection removal etc can be done through vbscript – ln29st Dec 16 '19 at 23:55

0 Answers0