- Scores: 0
- Joined: 3/26/2017
Run VBA macro code with VBS script
Sunday, March 26, 2017 7:36 AM
I am trying to run TextToColumns on an excel file, and would like to remove unnecessary steps if possible.
Long story short, this is part of an R script that outputs data into an Excel file and I need to run TextToColumns by calling a VBS script. I currently have a 2-step solution, where the R output is placed into an .xlsm file containing the VBS code and I run that macro from VBS using something like this:
Dim xlApp, xlBookSet
xlApp = CreateObject("Excel.Application")
xlApp.DisplayAlerts = False
Set xlBook = xlApp.Workbooks.Open("C:\PreOutput.xlsm")
xlApp.ActiveWorkbook.SaveAs "C:\Test.xlsx", 51
set xlBook = NothingxlApp.Quit
Set xlApp = NothingWScript.Quit
My question is... with something as simple as Macro1 shown below, isn't there a way to just include this in the VBS script? When I try, VBS does not like ":=" or the use of Range as a part of the destination.
Public Sub Macro1()
Thank you for any ideas.