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

Hi

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.

Jo
Jo SymingtonSenior Research ScientistAsked:
Who is Participating?
 
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
Maybe you can try something like this...

Option Explicit

Dim xlApp,xlBook
Set xlApp = getObject(,"Excel.Application")

'looping through all the opened Excel files and run macros from their module
For Each xlBook In xlApp.workbooks
	xlBook.activate
	xlApp.Run "AllInOneMacro"
	'xlApp.Run "EnterExCo"
	'xlApp.Run "ColumnDetails"
	'xlApp.Run "MacrosCompleted"
Next 

Set xlApp = Nothing

Open in new window

0
 
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
Answered the original question.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.