Bat file to open excel with parameters - spaces

Question

I would like to run Excel, open a specific file in it and pass arguments to this file. My call is

"C:\Program Files (x86)\Microsoft Office\Office14\EXCEL.EXE" 
"D:\Desktop\libs\xlam\+apps\+diagramViewer\diagramViewer.xlsm"
/e"D:\Desktop\MyFile.dia"

and this runs the way I want.

However, when the Excel file parameter (MyFile.dia) contains a space, everything is ruined, regardless of the quotes. The error message is that "File.dia" cannot be found.

How can I solve this? The MS help does not help.

EDIT:

Reacting to Dave's comments, I tried:

"C:\Program Files (x86)\Microsoft Office\Office14\EXCEL.EXE"
"C:\Program Files (x86)\Microsoft Office\Office14\EXCEL.EXE" "D:\Desktop\libs\xlam\+apps\+diagramViewer\diagramViewer.xlsm"
"C:\Program Files (x86)\Microsoft Office\Office14\EXCEL.EXE" "D:\Desktop\libs\xlam\+apps\+diagramViewer\diagramViewer.xlsm" "D:\Desktop\MyFile.dia"
"C:\Program Files (x86)\Microsoft Office\Office14\EXCEL.EXE" "D:\Desktop\libs\xlam\+apps\+diagramViewer\diagramViewer.xlsm" "D:\Desktop\MyFile .dia"

and all work OK. The problem starts when I try to include the /e switch so as to pass arguments to Excel. Namely ...

 "C:\Program Files (x86)\Microsoft Office\Office14\EXCEL.EXE" "D:\Desktop\libs\xlam\+apps\+diagramViewer\diagramViewer.xlsm" /e "D:\Desktop\MyFile.dia"
 "C:\Program Files (x86)\Microsoft Office\Office14\EXCEL.EXE" "D:\Desktop\libs\xlam\+apps\+diagramViewer\diagramViewer.xlsm" /e "D:\Desktop\MyFile. dia"

These do not produce any error, the problem is that Excel does not understand that the last file is something I would like to PASS to the xlsm file and not another file to be opened in Excel. To tell Excel that .dia should be a parameter processed by .xlsm, the only thing I found to work was deleting the spaces around "/e". Hence

 "C:\Program Files (x86)\Microsoft Office\Office14\EXCEL.EXE" "D:\Desktop\libs\xlam\+apps\+diagramViewer\diagramViewer.xlsm"/e"D:\Desktop\MyFile.dia"

works as intended, however

 "C:\Program Files (x86)\Microsoft Office\Office14\EXCEL.EXE" "D:\Desktop\libs\xlam\+apps\+diagramViewer\diagramViewer.xlsm"/e"D:\Desktop\MyFile .dia"

results into Excel trying to open the ".dia" file resulting in an error message "The .dia file does not exist...".

Solution 1

The fault must be your path isn't actually referencing what you think it is or, an issue with the locale may be, or how it's recognizing characters...

I just created the following

"C:\Program Files (x86)\Microsoft Office\Office14\EXCEL.EXE" "C:\Users\DRook\Desktop\a d c.xlsx"/e "C:\Users\DRook\Desktop\My File . dia"

It works as expected (meaning, no error message) from command prompt and a .bat file.

I suspect that the file isn't referenced correctly some how or that your may have some locale setting which is confusing it!

To be certain (just to rule it out), find the MyFile.dia and rename it My File . dia in Explorer. Then, with the shift button held, right click your file (file.dia) and select the option Copy as path

Then paste this as the final parameter in your command.

Remember, if you didn't create this string yourself and copied and pasted it from another source, the " marks may not do what you think they should. I suggest you re-write the entire string and then test it. Or copy my string above and test that, just rename the paths.

If D: is not a local drive, then I suggest testing with everything on your desktop (or at least, a local location where you won't have any issues with networking or permissions).

I have even tried it with similar directory structure and it works fine.

Edit

I would also attempt each parameter one at a time to ensure the path is correct. So, within the command prompt type

"C:\Program Files (x86)\Microsoft Office\Office14\EXCEL.EXE" and press enter

Then

"D:\Desktop\libs\xlam+apps+diagramViewer\diagramViewer.xlsm" and press enter

Then

"D:\Desktop\MyFile.dia" and press enter

Then

"D:\Desktop\My File . dia" and press enter

Edit 2

Right, I can now see / understand the fault better but offer only 1 solution which is a solution you don't want - don't allow white space.

What is happening when I send white space in the parameter is I get the following message from Excel (note, my parameter is e/"C:\Users\DRook\Desktop\My File . dia")

Note, then problem is Excel appears to be swapping white space with the extension (so therefore see's the white space, assumes it's the end of a file name and creates the extension).

Solution 2

I have been looking at something similar but wanted to use a macro in the opened XLSM file to autorun IF passed a parameter.

There is a long discussion at http://www.vbforums.com/showthread.php?366559-Excel-How-to-Pass-Command-Line-Parameter-DKenny-is-KING

about this subject.

What I did is use the suggestion in post #19 (interesting that the VB(6) "command" does not function in Office applications, which I have verified). What I do is use a batch file to launch Excel:-

Set ExcelArgs=AutorunMacros
"C:\Program Files (x86)\Microsoft Office\Office14\excel.exe" /e "C:\Path to\Excel File\Analyser Program.xlsm" 

Then within the Macro workbook I have the following (click on the "Workbook" tab in the Project

Private Sub Workbook_Open()
Dim ExcelArgs As String
ExcelArgs = Environ("ExcelArgs")
If Lcase (ExcelArgs)= "autorunmacros" Then
    Call StartMyMacro(Auto:=True)
End If
End Sub

The StartMyMacro is what you want to achieve Automatically.

In addition My StartMyMacro is defined as follows

Public Sub StartMyMacro(Optional Auto As Boolean = False)

which if called manually has Auto Set to False, and calling it from the Automatic Open sets the variable to True. At the end, with Auto=True, I save the workbook (actually a new one created during the Macro) and then Quit Excel. This causes the CMD window to close and the environment variable to be cleared.

Comments

[Dave]

I'm not trying to be difficult, but is renaming the file so it doesn't have any white space not an option?

[Daniel Bencik]

Dave, not really. This will be called a lot of times on files created by the other employees of the company where I work and I can expect them to be nothing but lazy...

[Dave]

I can run your code without an error.. .even if I change the from "MyFile.dia" to "My File here.dia" it still without the error message you mention...

[Daniel Bencik]

From the cmd line? Can you please post the exact command that you use?

[Dave]

I ran it from a .bat file, as per your subject? Your title says "Bat file to open excel"... well, as a .bat file, it works as is

[Ash King]

Hmm just wondering, isn't there supposed to be a space between "/e" & "D:\Desk..." ? Cause I can run that command with a space but I do get the same error without the space between the switch and command

[Daniel Bencik]

When I run it from a bat file, I still get my original error.. Have you added some special characters to the command?

[Dave]

You could try double escaoing, try using "" and "" to surround the final parameter (or even """ and """)? Although I'm not hopeful

[Dave]

Can you try "C:\Program Files (x86)\Microsoft Office\Office14\EXCEL.EXE" /e"D:\Desktop\libs\xlam\+apps\+diagramViewer\diagramViewer.x‌​lsm" "D:\Desktop\MyFile .dia"

[Daniel Bencik]

OK, stopped working...

[Dave]

What do you mean, stopped working?

[Daniel Bencik]

Gives the same error as ever... I can not figure out what our machines differ in. I dont believe different OS could affect this...

[Dave]

I made an update. I am on W7