Page 1 of 2 12 LastLast
Results 1 to 15 of 18
  1. #1
    2 Star Lounger
    Join Date
    Oct 2002
    Posts
    114
    Thanks
    0
    Thanked 0 Times in 0 Posts

    'running' an excel file from command prompt? (2000/sp3)

    I was thinking this morning... you can right-click an excel file and it gives the option to open, print, etc. Looking at the configuration, Windows uses DDE to pass

    <pre> [open(`% 1`)][print()][close()]
    </pre>


    to Excel to open the file, print it, and then close it.

    Can something similar be used to execute a macro? Open a file, run a macro (which processes some information and generates some output files), and then close it?

    Boy, that would be sweet... Ideas?

    thanks!
    ..dane

  2. #2
    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' an excel file from command prompt? (2000/sp3)

    You can create an xls file and the code in the macro. The file could have the code in the workbook open event to do what you want and the line before the endsub would be the workbook close.

    So if you open the file, it would run the code, do whatever you want, then close.

    Steve

  3. #3
    2 Star Lounger
    Join Date
    Oct 2002
    Posts
    114
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: 'running' an excel file from command prompt? (2000/sp3)

    Steve -- good suggestion, however this is a file that I do use to add and update information to, so I cannot have the "open" event run anything that then closes the book.

    I did think about creating another Excel file with the appropriate code in IT to open the "data" Excel file, process it, and then close it. But that just seems somewhat messy, if I could do it in the original data file with a "command-line parser" of sorts... cleaner and self-contained (won't be affected if I move the file or change the name, for instance)...

    ..dane

  4. #4
    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' an excel file from command prompt? (2000/sp3)

    I guess I am confused. You said that you wanted to:
    "Open a file, run a macro (which processes some information and generates some output files), and then close it"

    But if the requirement also "cannot have the "open" event run anything that then closes the book" then aren't you asking to:
    "Open a file, run a macro (which processes some information and generates some output files)", but does NOT close it?"

    You can do it with the same suggestion, just don't close the workbook at the end.

    Steve

  5. #5
    2 Star Lounger
    Join Date
    Oct 2002
    Posts
    114
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: 'running' an excel file from command prompt? (2000/sp3)

    Sorry, maybe I was not clear...

    When you right-click an Excel file, you can open it, or you can print it (which sends a DDE command to Excel to open it, print it, then close it). Neither of those options use Excel macro code, it is Windows telling Excel what to do once the file is opened... (either just open it, or open-print-close it).

    I was wondering if this "technique" or something similar could be utilized in a script file (run daily by the Windows Scheduler) to use the DDE command to do something else, such as open-runmacro-close instead of printing..

    Does that make sense? I'm wanting to harness the obviously existing capabilities of windows for other, customized purposes...

    ..dane

  6. #6
    Silver Lounger
    Join Date
    Jan 2001
    Location
    West Long Branch, New Jersey, USA
    Posts
    1,921
    Thanks
    6
    Thanked 9 Times in 7 Posts

    Re: 'running' an excel file from command prompt? (2000/sp3)

    Dane,

    Like Steve, I'm confused as to what you want to do (and I confuse easier than Steve).

    From what I know, I think you can use the Windows scheduler to open a workbook according to some schedule. I don't know how to do that but I'm assuming it can be done.

    It seems like you want the Windows scheduler to tell Excel to run some macro. It's not clear to me why you need the Windows scheduler to tell Excel this. If the Windows scheduler opens the workbook and the workbook has a Workbook Open macro that does some stuff, isn't this equivalent to having the Windows scheduler tell the workbook to do whatever is in the Workbook open macro? If it's a question of the WIndows scheduler telling the workbook to do different things, that can be built into the Workbook open as a dialog. If it's a question of the Windows scheduler passing some parameter value to the Workbook open, I don't know how to do that but I don't know if you were asking for that.

    I've attached a small workbook that has a Workbook Open macro. This macro prints out a message, adds 1 to cell A1 and closes. So if this workbook were scheduled to run by the Windows scheduler, it would open, add 1 to cell A1 every time and close. A few caveats on this sample:
    - you probably don't want the messages in your workbook
    - you probably want to do things other than add 1 to A1 <img src=/S/grin.gif border=0 alt=grin width=15 height=15>
    - you can automate the dialog about you've made changes to the workbook in the macro (when the Workbook Open macro executes the application.thisworkbook.close statement) and do you want to save it. This way, the question doesn't get ask and the workbook is saved automatically. I didn't include that but it could be done.
    - the only thing you can't avoid is the initial open telling you the workbook has macros and do you want to enable or disable them.
    - the statement application.thisworkbook.close closes the workbook but not the application. I didn't see where one can close the Excel from within an Excel macro; to some degree, it would make sense that you can't do it. I'm not sure if you call Excel from the DDE whether you could close it but it seems like you can, so this would suffice.

    Hope this helps.

    Fred

  7. #7
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: 'running' an excel file from command prompt? (2000/sp3)

    I think that what Dane wants is a command like parameter to Excel that tells it to run a macro when the file is opened with that parameter on the command line. Then, if you start Excel from a shortcut that had a command line that included the file and the parameter, the file would open and the macro would run, but if you just double clicked on the file in Explorer the file would open but the macro would not run. Excel does not have such a parameter.

    If that is what Dane wants, I see a couple of possibilities.

    1- Create another Excel workbook with code in the Open event routine. That code would then open the workbook in question and run the macro.

    2- Put code in the Open event routine in the workbook in question. That code would use Windows API routines to get the command line and look for a parameter. If that parameter is present, run the rest of the code and if it is not there exit from the open event routine.
    Legare Coleman

  8. #8
    2 Star Lounger
    Join Date
    Oct 2002
    Posts
    114
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: 'running' an excel file from command prompt? (2000/sp3)

    Legare -- I was getting a little discouraged in my abilities to vocalize what I am wanting until I read your post. You are exactly correct; I want the same Excel file to either open normally (no automatic macros) if double-clicked from Explorer, but if run with from a shortcut with a parameter, process that parameter...

    I have already thought about your first suggestion (use another workbook to perform the tasks on the "data" workbook), but that just seems a bit messy. I may use that technique, but would like something more elegant if possible.

    Regarding your second suggestion -- this sounds like it would work perfectly; unobtrusive yet effective (elegant, to me). I'll have to look into Windows API routines to decode the commandline.... I'm from the old DOS days of programming, and am very new to utilizing Windows API stuff. If you happen to have any good references for that, please let me know.

    Thanks for the ideas everyone..
    ..dane

  9. #9
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Flims, Switzerland
    Posts
    271
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: 'running' an excel file from command prompt? (2000/sp3)

    I doubt you will be able to read the command line passed to excel.exe from the code within your excel workbook. Any command line switches (listed here) are for the consumption of excel.exe and I'd be suprised if Excel publicised them after the event.

    If the above is tue, I would advocate using the first method, i.e. a second workbook which calls the workbook which does your work. You say this is messy, but the action of scheduling the opening of a Workbook to run code is, in itself, rather messy. I would always avoid this if it could be replaced by, say, an executable that could do the same work. Office apps are designed for user interation rather than performing services.

  10. #10
    2 Star Lounger
    Join Date
    Oct 2002
    Posts
    114
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: 'running' an excel file from command prompt? (2000/sp3)

    Adam,

    Thank you for your response. I agree that scheduling a "workbook" is messy. However, I have no compilers available to me (in any language). So I must work with what I have available to help me be as productive as requried in my job..

    thanks,
    ..dane

  11. #11
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Flims, Switzerland
    Posts
    271
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: 'running' an excel file from command prompt? (2000/sp3)

    Ah, I see... it's shame that you're forced to do it this way... <img src=/S/boxedin.gif border=0 alt=boxedin width=25 height=20>.

    Is something like Windows Scripting Host not even an option?

  12. #12
    2 Star Lounger
    Join Date
    Oct 2002
    Posts
    114
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: 'running' an excel file from command prompt? (2000/sp3)

    I moved from software into electrical engineering before Windows really became much of a common reality, so I'm not very familiar with Windows Scripting Host. But I use Office apps so frequently that VBA really comes in handy to take the grunt work out of the tasks I have to perform repetitively (report writing, circuit calculations, file generation/formatting, etc).

    I will certainly look into Windows Scripting Host though -- thanks for the suggestion!

    ..dane

  13. #13
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: 'running' an excel file from command prompt? (

    Hi Dane,
    What I think you could do is use the /embedded switch to run Excel when you want the workbook to run its routine. You can then use code in the workbook open event something like:
    <pre>Private Sub Workbook_Open()
    If (Application.Visible) Then Exit Sub
    ' Do whatever you need here
    With ThisWorkbook
    .Save
    .Close
    End With
    Application.Quit
    End Sub
    </pre>

    which shouldn't run if you open Excel normally because the application is visible. Please note, I have only done limited testing on this but it does appear to work OK unless Excel is already running, in which case it can be a little flaky. The main caveat is that you would need to have macro security set to low (otherwise you get the Enable Macros prompt with no-one there to say yes).
    Hope that helps.
    Regards,
    Rory

    Microsoft MVP - Excel

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

    Re: 'running' an excel file from command prompt? (2000/sp3)

    Here is a small workbook that shows a messagebox with the commandline used to launch Excel.
    If I open this workbook using this commandline (start, run):

    excel.exe "c:dataxl utilsGet Commandline.xls"

    on my system it shows a messagebox like this:

    "C:PROGRA~1MI01DA~1Office10excel.exe" "c:dataxl utilsGet Commandline.xls"

    When I double click from explorer, I get:

    "C:Program FilesMicrosoft Office XPOffice10excel.exe" /e

    So you just need to start this file in different ways to determine what the commandline has to be for what situation.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  15. #15
    2 Star Lounger
    Join Date
    Oct 2002
    Posts
    114
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: 'running' an excel file from command prompt? (

    Great idea Rory! I'll consider this approach. I'm currently also investigating WSH which may be another good alternative for some of these tasks that I need performed regularly..

    thanks again,
    ..dane

Page 1 of 2 12 LastLast

Posting Permissions

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