3

I'm writing a batch script that, among other things, opens a macro enabled excel file (.xlsm):

2>nul (
  >>%CSF% echo off
)  && (start "cmdTitle" /B excel %CSF% /e /automation) || (echo could not open)

%CSF% is the variable referring to a full path to the excel file i.e. "C:\test\testfile.xlsm" (including the quotes for cases where there may be spaces in the directory / filename)

This code checks if the file is locked for editing, then if not it opens the file (start command) otherwise echos could not open.

If I open the file from windows explorer or run just the following in a batch script the file opens fine.

set CSF="C:\Test Folder\Test.xlsm"
start "cmdTitle" /B excel %CSF% /e /automation

However, running the batch file when checking for a locked file always causes excel to say, "Excel found unreadable content in 'filename.xlsm' Do you want to recover the contents of this workbook?" which removes the macros from the file.

EDIT: In addition to the file not opening correctly, any excel add-ins that do something upon excel opening, for example, removing and recreating a command bar also fail to load: "Addin.xla cannot be accessed"

What is causing the difference in behavior between running the command alone and in redirection? TIA

Kumar V
  • 8,810
  • 9
  • 39
  • 58
theoldlr
  • 31
  • 3
  • 1
    Is it `2>nul` that causes the error? Then you could set a marker instead of direct start excel – jeb Jul 25 '13 at 12:33
  • possible duplicate of [Excel .xlsm file unreadable content but only when opened from batch file](http://stackoverflow.com/questions/17617652/excel-xlsm-file-unreadable-content-but-only-when-opened-from-batch-file) – Endoro Jul 25 '13 at 13:31
  • @jeb The 2>nul redirects the error(if any) from appending 'echo off' to the the file %CSF%. This is how it is determined if the file is locked--it will attempt to append to %CSF% which it cannot do if it is locked. More detailed explanation http://stackoverflow.com/questions/10518151/how-to-check-in-command-line-if-given-file-or-directory-is-locked-that-it-is-us – theoldlr Jul 25 '13 at 13:53
  • The question was if `2>nul` causes the problem, not what you expect that it should do. Btw. It doesn't work the way you described. `2>NUL` only redirects the error outputs to nul, so they wont be displayed, but they can't be appended to the file with or without `2>nul` – jeb Jul 25 '13 at 14:06
  • @jeb I'm not sure if the 2>nul is causing the problem. I understand that 2>nul is just redirecting the error, it is the >>%CSF% that attempts to append the file--in this case appending 'echo off', effectively nothing which is the goal as I just want it to open the file which it can only do if it is not locked. ANDing the 2>nul as shown is how it determines if the file was able to be opened for append or not. If it is the 2>nul causing the problem, I cannot see how and that is the information I am hoping to obtain here. – theoldlr Jul 25 '13 at 14:34

1 Answers1

1

Examine the end of the file in a hex viewer and see what, if anything, is appended to the end.

Your code as posted works fine (echoing the excel command too). Some other code is likely to be the problem.

foxidrive
  • 40,353
  • 10
  • 53
  • 68
  • Appending 'echo off' should append nothing--merely open the file which is how it is determined if the file is locked. I confirmed nothing is appended to the file with a hex editor. Also, after running the batch the file still opens without error from the gui. – theoldlr Jul 25 '13 at 14:35
  • Ok. I followed that up because of your comment that Excel asks if you want to recover the file. – foxidrive Jul 25 '13 at 23:55