troubleshooting Question

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

Avatar of Jo Symington
Jo Symington asked on
VB ScriptMicrosoft ExcelMicrosoft ApplicationsMicrosoft OfficeVBA
2 Comments1 Solution582 ViewsLast Modified:
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
ASKER CERTIFIED SOLUTION
Join our community to see this answer!
Unlock 1 Answer and 2 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 2 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros