Results 1 to 8 of 8
  1. #1
    New Lounger
    Join Date
    Sep 2001
    Location
    UK
    Posts
    17
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Running Macros without opening Excel (Excel2000)

    We use Excel to produce configuration files for a c++ system. When building the system I'd like to run the macros that write the configuration files automatically from a batch program without needing user intervention. That way I can be sure that the configuration files are always the latest versions.

    For example - the Excel work book 'MainCFGTables.xls' contains a macro e.g. WriteCharacterCfgFile() and I want to be able to have a batch command that does something like:
    ExcelRunMacro MainCFGTables.xls WriteCharacterCfgFile

    This will run the macro on the workbook and exit (it would be nice if the macro could take a parameter but I can live without that).

    Is this possible?

  2. #2
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,054
    Thanks
    2
    Thanked 417 Times in 346 Posts

    Re: Running Macros without opening Excel (Excel2000)

    Hi Wippy,

    Since you can run a windows application from a batch file, it shouldn't be too difficult to set things up so that your file runs. Check out the Start command (Win 9x and later) for more details. For example:
    START /MAX /WAIT "C:WINDOWSNOTEPAD.EXE"
    would start Notepad maximised and would (I think) cause the batch file to pause until notepad is closed. For files with associations, you can use the filename instead, so something like:
    START /MAX /WAIT "C:WINDOWSMainCFGTables.xls"
    should allow the macro to run and, provided the macro closes the workbook at the end, to continue processing when it's finished.

    Cheers
    Cheers,

    Paul Edstein
    [MS MVP - Word]

  3. #3
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Running Macros without opening Excel (Excel2000)

    You would need to put a call to the macro in the Thisworkbook module, using the Workbook_Open event.
    Also you would have to either digitally sign the project or set macro security to low (Don't!!) to prevent the macro warning dialog from appearing.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  4. #4
    2 Star Lounger
    Join Date
    Nov 2003
    Location
    Oroville, California, Uruguay
    Posts
    105
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Running Macros without opening Excel (Excel2000)

    I have been building an intricate spreadsheet to calculate railroad time (please refer to post(s) by "Nugget", and am very interested in being able to run a macro on a one time basis either from a batch file or when the book is opened for the first time, then have the batch file or macro disappear so when the book is opened, it will not execute again. My intent here is to be able to emaiil my spreadsheet to fellow workers and friends, but the spreadsheet is too large when filled in with all of the formula's and format's. I want to be able to take a row (cells a3:dd:3) for example, copy it, and post special with all of the formula's and formatting to cells a4:dd400 (as an example) so a person can have a whole years worth of use from the spreadsheet. I have found not one single fellow employee who understands any part of Excel, so I am trying to malke it as simple as possible for them to compute their time.

    I am a novice at Excel, and would appreciate any help that can be rendered.

    Thanx

    Larry (Nugget)

  5. #5
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Running Macros without opening Excel (Excel2000)

    You could have a workbook open macro that checked to see if cell a400 (for example) was empty. If it is then copy the rows. If it is not empty it would do nothing.

    Steve
    <pre>Private Sub Workbook_Open()
    If IsEmpty(Range("a400")) Then _
    Range("A4D4").Copy Range("A4:A400")
    End Sub</pre>


  6. #6
    2 Star Lounger
    Join Date
    Nov 2003
    Location
    Oroville, California, Uruguay
    Posts
    105
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Running Macros without opening Excel (Excel2000)

    Thanks Steve

    Larry

  7. #7
    2 Star Lounger
    Join Date
    Nov 2003
    Location
    Oroville, California, Uruguay
    Posts
    105
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Running Macros without opening Excel (Excel2000)

    Steve:

    Tried this, but do not understand how to put it into the spreadsheet as a MACRO. Went to the help file, but did not understand. Can you please advise.

    Private Sub Workbook_Open(C: Railroad[Timebook (Source).xls)
    If IsEmpty(Range("a400")) Then_Range("A5:CK5").Copy Range("A6:A400")
    End Sub

    Thanx

    Larry

  8. #8
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Running Macros without opening Excel (Excel2000)

    Open the file that you want the macro in [I assume it is named Timebook (Source).xls and it is stored in the path: C: Railroad]

    Go to VB(alt-f11)
    Open proj explorer (ctrl-R)
    [Usually on the left pane you will see an "explorer" pane with "folders"]
    Goto the one named "VBAProject(Timebook (Source).xls)" (or whatever the file is named that you want the macro in)
    If the folder is NOT opened (left has a "+") open it by pressing the plus
    "open" the "microsoft excel objects" by pressing the "+" (if not opened)
    Dbl-click the "thisWorkbook" object.
    You should see a "macro pane on the right side
    Copy this code and paste it into the pane:

    Private Sub Workbook_Open()
    If IsEmpty(Range("a400")) Then _
    Range("A5:CK5").Copy Range("A6:A400")
    End Sub

    Save the file and whenever it is opened, the macro will check to see if A400 is empty. If it is empty it will copy A5:Ck5 down the cols from A6 to A400 (all columns copied). If A400 is not empty the macro will do nothing.

    Steve

Posting Permissions

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