+ Reply to Thread
Results 1 to 13 of 13

Run a macro only when file is opened via a batch file?

  1. #1
    Registered User
    Join Date
    06-26-2006
    Location
    New York
    MS-Off Ver
    Office Pro 2003 primarily, some 2007
    Posts
    85

    Run a macro only when file is opened via a batch file?

    I have a workbook that I want to save a copy of every month. I had planned on using scheduled tasks and a batch file to open the file, but then I realized I didn't have a way to call the macro. In the past when I've run into this I've used a workbook open event that only triggers if the workbook is open at a certain time. I'd prefer not to do that in this case, as it is likely that the time and date that the file opens will need to change occasionally. I'd prefer to only have to update the scheduled task when that happens.

    I've done some searching and the suggestion seems to be to use VBS to take care of the whole thing? Unfortunately, I can't search the forum for just VBS, and my Google searches seem to come up with lots of fragments that get me part way, but I'm having trouble putting the whole thing together. So hoping I can ask a couple of questions here.

    1) Given my needs is VBS likely my best bet, or is there a simpler way to do this?
    2) Assuming VBS is the way to do this, does the VBS file replace both the batch file and the macro? I.E. I use scheduled tasks to call the VBS file and that file will contain both code to open the workbook + my macro code and I would not have my macro stored within the workbook itself?

  2. #2
    Forum Expert royUK's Avatar
    Join Date
    11-18-2003
    Location
    Derbyshire,UK
    MS-Off Ver
    Xp; 2007; 2010
    Posts
    26,202

    Re: Run a macro only when file is opened via a batch file?

    Why not post what you have so far?
    Hope that helps.

    RoyUK
    --------
    For Excel Tips & Solutions, free examples and tutorials why not check out my web site

    Free DataBaseForm example

  3. #3
    Forum Guru shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: Run a macro only when file is opened via a batch file?

    Without giving it a lot of thought, you could put the macro in another workbook, and schedule that. It then opens the other workbook and runs the macro on it.
    Entia non sunt multiplicanda sine necessitate

  4. #4
    Registered User
    Join Date
    12-14-2007
    Location
    Ontario
    Posts
    19

    Re: Run a macro only when file is opened via a batch file?

    Check out this article, I think it should point you where you want to go.
    http://www.dailydoseofexcel.com/arch...uments-in-vba/

  5. #5
    Registered User
    Join Date
    06-26-2006
    Location
    New York
    MS-Off Ver
    Office Pro 2003 primarily, some 2007
    Posts
    85

    Re: Run a macro only when file is opened via a batch file?

    Quote Originally Posted by royUK View Post
    Why not post what you have so far?
    This is the macro I want to run once a month:

    Please Login or Register  to view this content.
    I haven't written the batch file yet, but all it would do is open the Excel file.

  6. #6
    Registered User
    Join Date
    06-26-2006
    Location
    New York
    MS-Off Ver
    Office Pro 2003 primarily, some 2007
    Posts
    85

    Re: Run a macro only when file is opened via a batch file?

    Wow. You all are really quick with the responses.

    Quote Originally Posted by shg View Post
    Without giving it a lot of thought, you could put the macro in another workbook, and schedule that. It then opens the other workbook and runs the macro on it.
    Nice thought. This would work and I like that I think I already know enough to do it, but I dislike having to keep track of another Excel file. A little worried that someone else would see an Excel file that seems empty and delete it to try and "tidy up" the file server.

    Check out this article, I think it should point you where you want to go.
    http://www.dailydoseofexcel.com/arch...uments-in-vba/
    Just read through this and it is a little over my head, but it seems like I should be able to figure it out if I go slowly.

    Does this seem like a better option than VBS?

  7. #7
    Forum Guru shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: Run a macro only when file is opened via a batch file?

    I avoid VBS, only because I don't have a debug environment.

  8. #8
    Registered User
    Join Date
    06-26-2006
    Location
    New York
    MS-Off Ver
    Office Pro 2003 primarily, some 2007
    Posts
    85

    Re: Run a macro only when file is opened via a batch file?

    So I'm probably getting what I deserve here, but I'm trying VBS even though I too do not have a debug environment. Here is my code VBS code

    Please Login or Register  to view this content.
    That calls the macro in the worksheet which I list below.

    Please Login or Register  to view this content.
    It all seems to work perfectly. I fire off the VBS file, and after 30 seconds (based on my application.wait) the file is saved in the correct path and everything seems to have executed correctly in my macro...but then right after that I get an unknown runtime error. Line: 5 and Char: 5. Since it seems to be working perfectly I'm tempted to ignore it, but I'd obviously prefer to know what is causing it.
    Last edited by dylanemcgregor; 05-27-2010 at 04:29 PM.

  9. #9
    Valued Forum Contributor squiggler47's Avatar
    Join Date
    02-17-2009
    Location
    Littleborough, UK
    MS-Off Ver
    Excel 3.0 to 2007+2010! (couldnt get 2.1 working)
    Posts
    1,013

    Re: Run a macro only when file is opened via a batch file?

    You could do this :-
    Please Login or Register  to view this content.
    put the code in a new module

    then run it with a batch file :-
    Please Login or Register  to view this content.
    note you will habe to change the location of EXCEL.EXE and possibly provide a full path for runme.xlsm!

    the function picks up on the " /e/BATCH"

    then you can use it in your workbook open.
    Please Login or Register  to view this content.


    I modified code from :http://www.vbforums.com/showthread.php?t=366559
    Last edited by squiggler47; 05-28-2010 at 06:42 AM. Reason: Change to suit AP after re-reading

  10. #10
    Valued Forum Contributor squiggler47's Avatar
    Join Date
    02-17-2009
    Location
    Littleborough, UK
    MS-Off Ver
    Excel 3.0 to 2007+2010! (couldnt get 2.1 working)
    Posts
    1,013

    Re: Run a macro only when file is opened via a batch file?

    I tried getcommandlineA but it refused to run on my system!

    This works in Windows7/Excel 2010 and XP/2003

  11. #11
    Forum Expert snb's Avatar
    Join Date
    05-09-2010
    Location
    VBA
    MS-Off Ver
    Redhat
    Posts
    5,647

    Re: Run a macro only when file is opened via a batch file?

    I think your solution is a too complicated way to do this:

    Please Login or Register  to view this content.
    You don't have to open the file to make a copy of it.
    If you want to test the existence of the copy
    Please Login or Register  to view this content.
    You can put this code in the workbook_open event of your persnlk.xls.
    Please Login or Register  to view this content.
    Last edited by snb; 05-28-2010 at 09:28 AM.

  12. #12
    Valued Forum Contributor
    Join Date
    05-21-2009
    Location
    Great Britain
    MS-Off Ver
    Excel 2003
    Posts
    550

    Re: Run a macro only when file is opened via a batch file?

    Quote Originally Posted by dylanemcgregor View Post
    It all seems to work perfectly. I fire off the VBS file, and after 30 seconds (based on my application.wait) the file is saved in the correct path and everything seems to have executed correctly in my macro...but then right after that I get an unknown runtime error. Line: 5 and Char: 5. Since it seems to be working perfectly I'm tempted to ignore it, but I'd obviously prefer to know what is causing it.
    ActiveWorkbook.Close is closing the workbook before the macro has finished, so get rid of this line to fix the error. For completeness, close the workbook instead in the .vbs file with .ActiveWorkbook.Close(False), although this isn't strictly necessary as the Quit will close it.

  13. #13
    Valued Forum Contributor squiggler47's Avatar
    Join Date
    02-17-2009
    Location
    Littleborough, UK
    MS-Off Ver
    Excel 3.0 to 2007+2010! (couldnt get 2.1 working)
    Posts
    1,013

    Re: Run a macro only when file is opened via a batch file?

    You could just use the command line in the batch file direct in task scheduler, eliminating the need for a batch file completely!

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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