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

Topic

New Reply

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

     [open(`% 1`)][print()][close()]

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

Reply | Quote

Replies

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

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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
– 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

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

Ah, I see… it’s shame that you’re forced to do it this way… .

Is something like Windows Scripting Host not even an option?

Reply | Quote

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

Reply | Quote

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:

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

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.

Reply | Quote

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

Reply | Quote

The macros security warning dialog can be omitted by signing the workbook with a certificate created using selfcert.exe and then telling Excel to trust that certificate. That way you can still keep security set at medium or high.

Good to see that you’re looking at Windows Scripting Host though, it would be a far neater solution than scheduling workbooks.

Reply | Quote

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.

Reply | Quote

Jan,

That is wonderful. Thank you so much for this example routine. Absolutely fabulous! Exactly what I am looking for!

..dane

Reply | Quote

That’s the API routine that I have not had time to find!

Reply | Quote