-1

I have a working module that does some data transformation that I want to run on any excel sheet simply by running a vbscript.

It should work like:

1) Run vbscript, 2) choose excel file, 3) data is transformed into another sheet, 4) save and quit.

I have everything working besides the most important step, step 3. However the code is there, it's a working module but I have to import it and run it from the workbook for each workbook that comes in. I do not want to do this, it's too cumbersome for this project.

Is there a simpler way to do this than reprogram every module to work in VBScript? And if I have to rework the solution in VBScript, are there any good excel VBScript tutorials out there? I've done some research and can't find anything with the depth I need.

EDIT:

This Solution is not a solution to my problem. This assumes the module is imported into the excel sheet already. If I use this, I get the error:

Cannot run the macro 'filename.Module1.macro' The macro may not be available in this workbook or all macros may be disabled

This is because the macro is not added to the workbook, as I stated above I want to run the macro without having to add it to each workbook I run this on. Or if there's a way to script the adding of the macro and then running it, that will work too.

Ausche
  • 139
  • 2
  • 12
  • Why are you using VBScript? I would recommend VB.NET to automate Excel. Like this: https://msdn.microsoft.com/en-us/library/office/aa159913(v=office.11).aspx – HackSlash Jun 15 '18 at 15:41
  • 2
    Possible duplicate of [Run Excel Macro from Outside Excel Using VBScript From Command Line](https://stackoverflow.com/questions/10232150/run-excel-macro-from-outside-excel-using-vbscript-from-command-line) – HackSlash Jun 15 '18 at 15:43
  • Step 3 sounds more complicated than you're letting on. If the code doesn't exist in the workbooks, but some other workbook or as a .bas file, etc., then you need to import it, which requires some permissions settings that you *cannot* do programmatically. But, the other links above answer the direct question: is it possible. Yes. If the macro exists and is accessible, then you can run it as long as you have a handle on the Excel Application instance. – David Zemens Jun 15 '18 at 15:46
  • Yeah, if the macro isn't added to the workbook then where is it? – HackSlash Jun 15 '18 at 15:58
  • @DavidZemens This is what I was asking; if it's at all possible to run the macro without adding it into the workbook. It's not complicated, just a lot of work as these macro's I'm using are big. So it seems I will have to rework the solutions in VBScript then? Or try out VB.NET as recommended above? – Ausche Jun 15 '18 at 16:01
  • @HackSlash the macro itself is saved in my Documents folder as a .bas file. Can that be used to run step 3 without installing it manually? – Ausche Jun 15 '18 at 16:04
  • You can't run a .bas file. You need to import it to the workbook, or (possibly a better solution) import it in to a XLAM Add-In, and have that Add-in loaded with the Excel instance. You'll need to modify the .bas code (probably only slight changes) to do that. – David Zemens Jun 15 '18 at 16:07
  • @DavidZemens Okay great, that answers my question if you want to post it as an answer. Thank you guys. – Ausche Jun 15 '18 at 16:09
  • Most VBA macros are easily translatable to a VBScript program which uses `CreateObject()` to create an Excel Application object. Most of the needed transformations are mechanical -- dropping `i` from `Next i`, dropping `As Long` from `Dim i As Long`, making sure that all of your range references are properly qualified, etc. – John Coleman Jun 15 '18 at 16:11
  • @JohnColeman This is helpful! I've done this much, I'm looking into the range references now, that is the biggest issue so far. – Ausche Jun 15 '18 at 16:21

2 Answers2

3

You can't run a .bas file on it's own, it's not a macro or anything executable. It's just text.

You need to import it to the workbook, or (possibly a better solution) import it in to a XLAM Add-In, and have that Add-in loaded with the Excel instance. You'll need to modify the .bas code (probably only slight changes) to do that.

Or, use the VBIDE library and you can programmatically add the .bas to the workbook at runtime. I'd probably try to avoid that, though.

You don't need to refactor the macro in to VBS, nor use VB.Net. As long as you can get the .bas in any Excel file -- even if you put it in the Add-in manually, you should be able to run it.

Run Excel Macro from Outside Excel Using VBScript From Command Line

David Zemens
  • 53,033
  • 11
  • 81
  • 130
  • 1
    I put reusable code in my PERSONAL.XLS so that it is available at all times regardless of what workbook I am working on. https://www.rondebruin.nl/win/personal.htm – HackSlash Jun 15 '18 at 16:35
  • @HackSlash it's been so long since I did anything substantial in Excel that I forgot that's even an option hahah. – David Zemens Jun 15 '18 at 16:36
1

I have a text file on my desktop it is saved as .vbs.

When I double click it if runs a macro from a workbook. There are limitations to what you can do, but I thought it was kind of neat.

Set objExcel = CreateObject("Excel.Application")
objExcel.Application.Run "'C:\Users\dmorrison\Desktop\HelloMsg.xlsm'!Module1.Hello"
objExcel.DisplayAlerts = False
objExcel.Application.Quit
Set objExcel = Nothing
Davesexcel
  • 6,896
  • 2
  • 27
  • 42