1

I'm trying to create and run a VBScript to run a macro I've created (PVT_Paste_Macro.xlsm) on Excel files without opening each individual Excel file (I have 528 files!) Here is my code:

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

After running the code, I get the following error:

Script: C:\Users\Owner\Desktop\VBA for macros - PVT.vbs
Line:   2
Char:   1
Error:  Unknown runtime error
Code:   800A03EC
Source: Microsoft VBScript runtime error

I run the script while the macro file is open. The macro file, the (test) Excel file I'd like to run the macro on, and the VBS script are all located on the Desktop.

Can anyone help? Thanks.

Gina
  • 11
  • 1
  • 2
  • Is this the exact code you are running (I think not given that you have 528 files, you don't have one vbs script per file, do you)? And if it is not in fact the code you are actually running, don't you have the same problem as [here](https://stackoverflow.com/a/23362922/11683)? – GSerg Jun 27 '19 at 15:14
  • It could be an issue with the bitness. Try to run either `%WINDIR%\wscript.exe "C:\Users\Owner\Desktop\VBA for macros - PVT.vbs"` or `%WINDIR%\SysWOW64\wscript.exe "C:\Users\Owner\Desktop\VBA for macros - PVT.vbs"` in a console (`cmd`) – Florent B. Jun 27 '19 at 15:24
  • You could also try to open the workbook in a separate call: `objExcel.Workbooks.Open "C:\Users\Owner\Desktop\PVT_Paste_Macro.xlsm` followed by `objExcel.Run "Module1.PVT_Paste_Macro"`. – Florent B. Jun 27 '19 at 16:40

1 Answers1

2

The code does not work, because it does not like the fact, that the excel file, containing the code open before the code is executed:

enter image description here

Using your code, try this simple steps and make sure it works:

  1. Created a Script.xlsm file on the Desktop.
  2. Added the following in Modul1:
Sub FromExcel()
    MsgBox "FROM EXCEL"
End Sub
  1. Called it from a Script.vbs like this:
Set objExcel = CreateObject("Excel.Application")
objExcel.Application.Run "'C:\Users\UserName\Desktop\Script.xlsm'!Modul1.FromExcel"
objExcel.Visible = True 'Or "False", depending on what you want.
objExcel.Application.Quit
Set objExcel = Nothing
  1. Profit

As a way around, if you want to run the "macro" from an opened Excel file, then something like GetObject would be useful:

Set objExcel = GetObject("C:\Users\UserName\Desktop\xl.xlsm")
objExcel.Application.Run "Modul1.FromExcel"
objExcel.Application.Quit
Set objExcel = Nothing

However, this is probably an XY problem - there should be a better way of doing the whole story, e.g. running the "macro" from a single independent Excel file, which opens the other Excel files in the desktop and processes them, without the need of VBScript. Probably check these:

Vityata
  • 42,633
  • 8
  • 55
  • 100
  • Does the xlsb file need to be open in the `objExcel.Application`? I suspect that trying to `Run` from a file that is not open will give some error. – David Zemens Jun 27 '19 at 14:49
  • 1
    @DavidZemens - I was using `xlsb`, by default, it works with xlsm as well.I am using `objExcel.Application` to mimic the code of the op. – Vityata Jun 27 '19 at 14:54
  • 1
    What is the difference between your code and the OP's, apart from the path to the file? – GSerg Jun 27 '19 at 14:57
  • @GSerg - well, I provide the VBA code as well and I verify to the OP that the code is working :) – Vityata Jun 27 '19 at 14:58
  • 1
    @Vityata, is your answer to this post to say that there's no issue just because you couldn't reproduce the error? – Florent B. Jun 27 '19 at 15:06
  • 1
    @FlorentB. - my answer says that for about 70%. The other 30% say that the error is probably in the non-shown VBA code, thus helping the OP to debug better. – Vityata Jun 27 '19 at 15:08
  • Might want to describe situations that could cause the underlying VBA code to throw an error, for instance, anything that relies on an open instance of a workbook (e.g., `Select`, `Activate`, `ActiveCell`, `Windows`, etc.) but +1, it seems the error is happening from the VBA code. – David Zemens Jun 27 '19 at 15:11
  • Hi Vityata, thanks but that did not work. I am receiving the exact same error. – Gina Jun 27 '19 at 15:17
  • 2
    @Vityata, I don't see any value in writing an answer if you can't explain the error. A comment saying that you couldn't reproduce the error would have been enough. – Florent B. Jun 27 '19 at 15:18
  • @Gina - did you use exactly my code with the `MsgBox` Sub? – Vityata Jun 27 '19 at 15:20
  • @Gina - the error is because of the line `objExcel.DisplayAlerts = False`. If you remove it, it should work. – Vityata Jun 27 '19 at 15:23
  • @FlorentB. - actually it turned out that my code avoided the issue by missing one line. This is why a comment saying "Couldn't reproduce the error" would have made a harm to the question, but the steps I have done to "not reproduce the error" were a good way to go. – Vityata Jun 27 '19 at 15:25
  • @Vityata, much better, except for the fact that the error is raised by `objExcel.Application.Run` line 2 and not by `objExcel.DisplayAlerts` – Florent B. Jun 27 '19 at 15:33
  • @FlorentB. - we are slowly getting there. – Vityata Jun 27 '19 at 15:42
  • @Vityata still the same error, and yes I tried the MsgBox sub in another module (Module2) and tried a "test" script. It seems the error (as FlorentB mentioned) is in the "objExcel.Application.Run" line – Gina Jun 27 '19 at 15:47
  • @FlorentB. tried %WINDIR%\SysWOW64\wscript.exe "C:\Users\Owner\Desktop\VBA for macros - PVT.vbs" in cmd and still same error. – Gina Jun 27 '19 at 15:55
  • @Gina - can you restart your PC and try only the code I have written from 1 to 4? The restart is needed because there is probably a live Excel object somewhere, which is sabotaging the rest. – Vityata Jun 27 '19 at 15:57
  • @GSerg one vbs script intending to use on all 528 Excel files. Not the same error as the link you included - mine is "Unknown runtime error." And yes, I provided the exact code. – Gina Jun 27 '19 at 15:57
  • @Gina - pity. I am afk now. But can you try to make a "macro", that only stays in Excel, avoiding completely the VBScript? Check the 2 links I in my answer, these could give some directions. – Vityata Jun 27 '19 at 17:49
  • @Vityata you mean just running the macro outside of VBScript? It works fine, I am able to run on a single Excel file. – Gina Jun 27 '19 at 17:53
  • @Gina - yes, bit this is the first step. A "macro" in one excel file can be made to pricess multiple excel workbooks. – Vityata Jun 27 '19 at 17:58
  • @Vityata You mean using VBA in Excel? I don't know how to do that, and the links you provided don't seem to provide VBA code to run a macro? FYI, this is my first time coding through VBA/VBScript (in case that wasn't obvious already). – Gina Jun 27 '19 at 18:16