Avatar of Jo Symington
Jo Symington
 asked on

VBS code to get an already open excel file and also to ruin multiple vba macro from within that file.


I'm new to vbs coding and vba coding.  My vba codes work but I have to use vbs also, and this is where I fall.

I am using a bit of equipment that runs its own type of script, but you can import some vbs script to do external functions.  I have two vbs scripts that my bit of software uses.  The first is for it to open a pre-desired file to export a series of results into.  This file is an .xlsm file and is opened in Read-only mode to prevent people from writing over the master copy (of which I have several backups).  The results are exported between 3-9 times depending on how many raw data analyses are generated (operator decides this at start of a run).  Once the software has finished pasting the results I then call on a second vbs script which finds the already open excel workbook and runs a series of macros.  This is where I run into problems.  

Initially the code I was running was reporting that the excel file was already open, but I couldn't use the word 'Activate' as in vba code.  I should now say that I cannot combine the vbs files to run as one as there is code in my software that has to run before it does analyses.  I got around this using GetObject but before I could get the vbs to run a series of macros on the file with the pasted data in, but now using GetObject, it will only run one.  I'd really like it to run all of them especially the vba that creates a timestamped backup of the file should the user do anything wrong.

Here is the vbs code I am using (I am confident my vba coding is right and it has been tested fully) any lines with ' are obviously not run:

vbs script 1:
Option Explicit
Dim xlApp, xlBook
Set xlApp = CreateObject("Excel.Application")
Set xlBook = xlApp.Workbooks.Open ("C:\DataAnalysis\TestBook.xlsm")
xlApp.Application.Visible = True
Set xlBook = Nothing
Set xlApp = Nothing

vbs scipt 2:
Option Explicit
Dim xlApp
Set xlApp = getObject(,"Excel.Application")
'Set xlApp = xlBook.Workbooks.Activate("C:\DataAnalysis\TestBook.xlsm")
'xlApp.Application.Visible = True
xlApp.Run "AllInOneMacro"
'xlApp.Run "EnterExCo"
'xlApp.Run "ColumnDetails"
'xlApp.Run "MacrosCompleted"
Set xlBook = Nothing
Set xlApp = Nothing

The problem I have is it means only one Excel file can be open as I am not specifying the filename (I cannot work out how to do this for vbs script 2).  Sometimes colleagues may come into the lab and open another excel file.  The second problem is, as I say, vbs script 2 only runs the first xlApp.Run macro from the GetObject.  I'm wondering if I have to reiterate GetObject before running each XlApp.Run due to me not specifying the filename?  But that seems a patch for a shoddy script which can be written better in the first instance.  For example, if I ran the xlApp.Run in vbs script 1 it can run multiple xlApprun commands.  Or could I enter something along the lines of 'Set xlApp = ActiveWorkbook' having just used 'GetObject', there'll only be one Excel book open anyway (it'd still be better if I could activate that window specifically).

Another query just out of interest.  Initially the vba macros I had the vbs running ran fine, except for the ones it called that had a 'call' command within the vba (a vba within an vba).  Does vbs allow you to run a vba with a call command in it?  All macros are within the same excel file and they run Ok directly from a button in excel using vba.

Help would be greatly appreciated.

VB ScriptMicrosoft ExcelMicrosoft ApplicationsMicrosoft OfficeVBA

Avatar of undefined
Last Comment
Subodh Tiwari (Neeraj)

8/22/2022 - Mon
Subodh Tiwari (Neeraj)

View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
Subodh Tiwari (Neeraj)

Answered the original question.
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy