+ Reply to Thread
Results 1 to 8 of 8

Open a workbook - run a macro - from .bat or .vbs file

  1. #1
    Registered User
    Join Date
    06-16-2015
    Location
    montreal
    MS-Off Ver
    2007
    Posts
    3

    Open a workbook - run a macro - from .bat or .vbs file

    I simply want to

    1) open a workbook

    2) run/call a macro

    --

    I am able to open the workbook from a .bat file using simply:

    "C:\myfilepath.csv"

    But I do not know what other code to enter to make my macro file run within this opened workbook.

    I know this is possible using a .vbs file, to then call from the .bat file, but I am not familiar with .vbs coding.

    If possible to run the macro directly from the .bat file I would prefer that.

  2. #2
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Open a workbook - run a macro - from .bat or .vbs file

    Excel uses VBA. There's no need to reference a Windows .bat file, VBA can open files all on its own.

    MyFile.CSV is not a workbook, it is a text file. Excel can open this text file and display the contents as if it were a sheet several different ways.

    When VBA opens a CSV file by placing the data into a spreadsheet, a SHEET variable can be set at the same time that file is opened making it very easy for VBA to keep track of that sheet as an object and do pretty much anything you want to that sheet.

    Once you are done massaging that data, it can be saved back to the text CSV file again. Or any other file format you need.
    _________________
    Microsoft MVP 2010 - Excel
    Visit: Jerry Beaucaire's Excel Files & Macros

    If you've been given good help, use the icon below to give reputation feedback, it is appreciated.
    Always put your code between code tags. [CODE] your code here [/CODE]

    ?None of us is as good as all of us? - Ray Kroc
    ?Actually, I *am* a rocket scientist.? - JB (little ones count!)

  3. #3
    Registered User
    Join Date
    06-16-2015
    Location
    montreal
    MS-Off Ver
    2007
    Posts
    3

    Re: Open a workbook - run a macro - from .bat or .vbs file

    Thanks,

    I have created a macro file that does all the work (opens the .csv file i need to manipulate and saves it as a new .csv when it's done) from a new blank workbook.

    Now I just need to launch that macro file from a .vbs or .bat file (the goal here is to launch this automatically with windows task scheduler at specific times)

  4. #4
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Open a workbook - run a macro - from .bat or .vbs file

    I see. You wouldn't be launching an Excel macro from a .bat file. Perhaps you can set the scheduler to open your Excel workbook that has this macro installed at a specific time, and have that workbook set to run the macro automatically when it opens? Just a thought.

  5. #5
    Registered User
    Join Date
    06-16-2015
    Location
    montreal
    MS-Off Ver
    2007
    Posts
    3

    Re: Open a workbook - run a macro - from .bat or .vbs file

    Interesting.

    How do you set a workbook to automatically run a given macro?

  6. #6
    Forum Contributor
    Join Date
    10-13-2012
    Location
    Southern California
    MS-Off Ver
    Excel 2007
    Posts
    401

    Re: Open a workbook - run a macro - from .bat or .vbs file

    Quote Originally Posted by sergiogent View Post
    Interesting.

    How do you set a workbook to automatically run a given macro?
    It sounds like you want to use Workbook_Open Event:


    Here's one of many pages that show an example of how it's done.
    http://www.ozgrid.com/VBA/auto-run-macros.htm

  7. #7
    Registered User
    Join Date
    06-12-2015
    Location
    Maryland, USA
    MS-Off Ver
    2010
    Posts
    83

    Re: Open a workbook - run a macro - from .bat or .vbs file

    did you try using auto_open?

    If you code a sub called auto_open in the Workbook it will execute whenever the Excel file is opened.

    Please Login or Register  to view this content.
    Got help? Pls give rep.
    If you do R&D learn VBA

  8. #8
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Open a workbook - run a macro - from .bat or .vbs file

    Both workbook_open and auto_open events can be used to call anotherwise standalone macro:

    Please Login or Register  to view this content.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 0
    Last Post: 06-12-2013, 09:05 AM
  2. Macro to open a workbook from an unknown file path
    By paul_sykes00 in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 12-03-2012, 02:42 AM
  3. Macro to open txt file into a new workbook and save it
    By GHIREM in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 07-09-2012, 04:18 PM
  4. Perform macro "on open" specific file- store macro in Personal Macro Workbook?
    By thompssc in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 04-17-2012, 12:38 PM
  5. Run excel macro from batch file. Workbook Open
    By Jonsby in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 02-26-2009, 08:14 AM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1