Welcome !

 Run VBA macro code with VBS script

Author Message

  • Total Posts : 2
  • Scores: 0
  • Reward points : 0
  • Joined: 3/26/2017
  • Status: offline
Run VBA macro code with VBS script Sunday, March 26, 2017 7:36 AM (permalink)
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.Run "'PreOutput.xlsm'!Module1.Macro1"
 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()   
    Selection.TextToColumns Destination:=Range("B1")
 End Sub

Thank you for any ideas.
    Online Bookmarks Sharing: Share/Bookmark

    Jump to:

    Current active users

    There are 0 members and 2 guests.

    Icon Legend and Permission

    • New Messages
    • No New Messages
    • Hot Topic w/ New Messages
    • Hot Topic w/o New Messages
    • Locked w/ New Messages
    • Locked w/o New Messages
    • Read Message
    • Post New Thread
    • Reply to message
    • Post New Poll
    • Submit Vote
    • Post reward post
    • Delete my own posts
    • Delete my own threads
    • Rate post

    2000-2017 ASPPlayground.NET Forum Version 3.9