I have some automated processes that use an Excel auto_open sub to kickstart various things. Excel is called from a batch file triggered by a Task from the Task Scheduler.
Since auto_open can be a pain (I don’t want it to run every time the spreadsheet is opened, only when triggered by the batch file), I have it first check whether Excel is minimized, and if so, run whatever it’s supposed to do.
I do this with this command from a command window (or, technically, a batch file):
start /min excel <file name>
In Excel 2010, no problem, it opened minimized (Application.WindowState = xlMinimized). Over the weekend, we were upgraded to Office 2013 and this no longer works.
If I type:
start /min winword
Word opens minimized. Same with PowerPoint, etc. However, Excel doesn’t. Maybe it’s related to how Excel now opens each window in its own instance?
Anyway, any ideas as to how to solve this problem? Either a more reliable way to open Excel minimized or some other neat workaround using auto_open?
I don’t think that solution works. If I’m understanding correctly, RitterSport doesn’t want the document to open minimized all the time, but only when opened from the batch file.
You’re right that Excel 2013 and beyond don’t pay attention to the /min switch if you are opening a document (it does if you just open Excel without a document, but that doesn’t help you).
I think you might be able to make it work by passing a parameter in the command line. Then check for that parameter in your VBA instead of checking if it opened minimized.
Excel does obey the /min switch. I’ve just tried START /MIN EXCEL.EXE d20.xls and START /MIN EXCEL d20.xls and both started minimised for me with the spreadsheet loaded.
RitterSport, you can test this by trying START /MIN EXCEL.EXE /s to start Excel in Safe Mode. Try this with and without a test spreadsheet.
Anyway, I’ve just had another thought: is it possible that another instance of Excel is already running? If so, the /x switch should be applied as in START /MIN EXCEL.EXE /X XYZ.XLS.
Quartz, I don’t know what to tell you, it doesn’t work for me like that, even without a spreadsheet parameter.
TroutMan, as I mention, it starts non-minimized whether I include a spreadsheet to open or not. I just checked and there is no other Excel process open.
Anyway, I continued to investigate and I independently found the command line parameter version. First, I tried setting an environment variable, but Excel never seemed to see them (either when opening directly from a command window or typing “start…” from a command window. I mention that because it’s possible that “start” creates a different environment with different variables.
After multiple failed attempts, crashing Excel over and over again, I finally got it working. From the batch file, I include a parameter, Excel sees it, and runs if it’s the right one. If you’re interested, I can post the code or send it via PM.
There are so many configurations and versions between Excel and Windows, we could spend days or weeks trying to figure out the cause of the different behavior.
Glad you found a solution, RS. I crashed Excel multiple times trying to test out some sample code. I assumed it was a permissions issue with a kernel command (I’m not a local admin on my work machine), but maybe it’s just buggy.
It was crashing for me because it turns out you have to declare extra functions, not just GetEnvVar. These are the functions I had to declare to get it to stop crashing:
Private Declare Function GetEnvVar Lib “kernel32” Alias “GetEnvironmentVariableA” (ByVal lpName As String, ByVal lpBuffer As String, ByVal nSize As Long) As Long
Declare Function GetCommandLine Lib “kernel32” Alias “GetCommandLineW” () As Long
Declare Function lstrlenW Lib “kernel32” (ByVal lpString As Long) As Long
Declare Sub CopyMemory Lib “kernel32” Alias “RtlMoveMemory” (MyDest As Any, MySource As Any, ByVal MySize As Long)
Of course, I didn’t figure that out myself, it was buried multiple posts down in some thread on the subject. GetCommandLine returns a long which has to be converted to a string using CopyMemory – I think the long is a pointer and you’re dumping memory. This seems all seems so dangerous and hackable to me.
If I understand correctly, you have a spreadsheet with an auto-runs macro, that on startup checks if – some condition – is true, and if so runs – some code --.
The alternative approach is to move the relevant code from the auto-runs macro into a separate macro, and, when desired, run the separate macro with a separate command.
There are three well-known ways of getting Excel to run a separate command: SendKeys, DDE, and COM. Discounting SendKeys and DDE, script to run a specific macro would look something like this:
Dim xl as object
Set xl = CreateObject(“Excel.Application”)
xl.Workbooks.Open (“C:\fred.xlsm”)
xl.Visible = True
xl.Run “MyMacro”
if you don’t like Windows Scripts, (js or vbs) you can do the same thing use Powershell. If you /need/ to use a .bat or .cmd file, you can do something similar using DDE.
Thanks, Melbourne! As I mention above, I got it working using a command line argument and I’m going to leave well enough alone. Given how restrictive our environment is here, we probably have VBS turned off or something anyway.
If cscript is not installed, you can do the same thing using autoruns in another spreadsheet Your batch file loads the command spreadsheet, and the command spreadsheet loads and executes macros in the data spreadsheet.
I’m just putting it out there don’t break anything that is already working.
It’s funny because I already have some spreadsheets that run VBA code in a different spreadsheet, but that never occurred to me. That would definitely work – auto_run in one spreadsheet that opens the main spreadsheet and runs the DoItAll function there. However, since I already have it working, I’m leaving it. Plus, getting the first spreadsheet right would be annoying, because auto_run functions are a pain if they run without conditions – you always have to remember to open that spreadsheet with ctrl (or shift? whatever).
If cscript is not installed, you can do the same thing using autoruns in another spreadsheet Your batch file loads the command spreadsheet, and the command spreadsheet loads and executes macros in the data spreadsheet.