none
Fun and Extremely Useful Project for a Script Lover RRS feed

  • General discussion

  • I am thinking about dabbling with PowerShell scripting because I and other business users could really use a script that intercepts commands to open .xlsx files and does the following:

    1) Checks to see if an excel file of the same name is already open in an instance of Excel.  If not, then the file is passed on to Excel/a running instance of Excel to open as normal.

    2) If so, a dialog will say "A file of this name is already open in Excel.  Would you like to create a temporary copy of this file with a slightly different name and open it?"

    3) Choosing Yes to the above will create a copy of the file with a different name in the user's temporary directory and then open it in the already active Excel instance, thus somewhat defeating the dreaded "Sorry, Excel can't open two workbooks with the same name at the same time."

    Most of the time, people trying to open two instances of a workbook of the same name are doing so to compare them, so making one be a temporary copy is not harmful but rather extremely helpful.  I would guess that most of the time, users receiving this stop message are fully aware of what they're trying to do and end up immediately making a copy of one of the two files in question with an altered name so that they can go about their business of trying to compare the contents in Excel :).

    Monday, March 16, 2015 4:06 PM

All replies

  • What's your question?

    -- Bill Stewart [Bill_Stewart]

    Monday, March 16, 2015 4:24 PM
    Moderator
  • That is not how Excel works. We can easily open two copies but why compare two identical thinsg? You whole premise is somewhat faulty so I have to assume you are not explaining it correctly.


    ¯\_(ツ)_/¯

    Monday, March 16, 2015 8:16 PM
  • Sounds like fun. Write it up and post it here for others to use and learn from. Playing around with Powershell is a blast and you can do all kinds of things with it. Even if you don't get whatever it is you are trying to do to work exactly how you want it, you will learn a lot in just trying new things. If you have questions once you start coding your tools just post to this forum, there are many very knowledgeable persons on here who enjoy helping others out!
    Monday, March 16, 2015 8:28 PM
  • I'm not a scripter today, but currently code in R, so this doesn't feel out of range for me to try.  My fundamental question at this stage is:  As I've described the workflow above, is PowerShell (or PowerShell + some registry key editing) the best choice/capable of the interactions I've described?  These are essentially:

    1) Intercept any user actions looking to open an Excel file (from windows explorer, from the command line, from the run box, etc.)

    2) Examine the call to open the file.

    3) Find and query running instances of Excel in order to compare the filenames of what is already opened in Excel to the information from (2).

    4) Display a GUI interaction choice to the user to decide how to proceed.

    Posts like these two made me think this was a useful forum to post the idea and garner some feedback (The site won't let me hyperlink these titles, but search for them on the Scripting Guy's Blog):

    Can I open a file dialog box with Windows PowerShell?

    PowerShell and Excel:  Fast, Safe, and Reliable

    The examples above also lend me to believe that if it turns out this is extremely simple with just about 10 lines, someone might just provide code which does it.

    As an aside, it's not the hardest problem to solve now:  before opening the 2nd file that you already know is a name collision, I quickly hit Ctrl+C, Ctrl+V to make a copy right there in the same directory, and open it instead.  However, this also requires you to remember you've done this and go back to delete that file before moving on.  In my work, it may be hours before I'm done with the comparison work and it is hard to recall that you have some file cleanup to do afterwards, thus the idea of a script that would already put the copy in a temp directory for you and also not require you beforehand to remember whether the filename of what you're about to open is identical to one already opened (an enhancement might be to also check whether the path is the same, i.e., you've made a mistake and are opening a "truly already open" file.)

    Thanks in advance if you or any readers have some ideas!


    • Edited by mdsosa Monday, March 16, 2015 8:42 PM
    Monday, March 16, 2015 8:34 PM
  • No-- Excel 2013 and all predecessors have the default behavior that within the same Excel process, they won't open two files whose names are the same but reside on different paths.

    In my case, I'm producing tons of statistical data in R, and the algorithm I choose places the outputs in separate directories.  Some of the filenames created are exactly the same, so to compare algorithms with a quick look at the .csv files can require a renaming workaround because of this error.  The text I quoted in the OP ,"Sorry, Excel can't open two workbooks with the same name at the same time.", is precisely the dialog box presented with just "OK" as the only option.


    • Edited by mdsosa Monday, March 16, 2015 8:44 PM
    Monday, March 16, 2015 8:38 PM
  • No-- Excel 2013 and all predecessors have the default behavior that within the same Excel process, they won't open two files whose names are the same but reside on different paths.

    In my case, I'm producing tons of statistical data in R, and the algorithm I choose places the outputs in separate directories.  Some of the filenames created are exactly the same, so to compare algorithms with a quick look at the .csv files can require a renaming workaround because of this error.  The text I quoted in the OP ,"Sorry, Excel can't open two workbooks with the same name at the same time.", is precisely the dialog box presented with just "OK" as the only option.


    You said same file not same name differ3ent file.

    You cannot intercept a file open via script.  You can via a debug or API hook but that is disallowed in scripts for security purposes.

    You can populate your Excel with a macro or add-in that will give it the behavior you desire. Post in Excel developer forum for assistance.


    ¯\_(ツ)_/¯

    Monday, March 16, 2015 8:59 PM
  • I like the idea of an Excel add-in since the problem is inherently Excel's.  However, I was thinking you could do it with a script and a change in Windows Explorer's assigned handler for .xlsx, xls, etc.-- sending those files to the script first or something.

    Thanks for the information.  I do dispute your reading of my original post and claiming that I said "same file" in (1) and (2) describing the workflow, I did not say "same file" at all-- (1) clearly says that the script "Checks to see if an excel file of the same name is already open".  Reading the whole post, it's clear that the problem I'm interested in solving is opening two files of the same name to inspect (visually at least) what differences are contained inside.


    • Edited by mdsosa Monday, March 16, 2015 10:37 PM
    Monday, March 16, 2015 10:37 PM
  • I say no way.  Go ahead - do the research.  I do not see how to associate an EXE to a non-executable. 

    That's your clue. 


    ¯\_(ツ)_/¯

    Monday, March 16, 2015 11:18 PM
  • You could use windiff or something similar to compare the files if you need something quick and easy. You could likely use powershell to manipulate a utility like that as well.

    You would have to write and compile a utility that you could use for the Excel file-open handler as you mention, and have that compiled exe perform the actions you are looking for. Writing something to intercept the file open event would be quite involved. You would learn a lot by attempting it though. You can do pretty much anything you want on/with a computer or OS once you learn how but as JRV eluded to; to get exactly what you are asking for in this case would be more involved than a powershell script. Unfortunately it doesn't look as easy as just changing the extension association in the HKEY_CLASSES_ROOT registry hive to run "powershell.exe -command <scriptnamehere>".

    There are many other options though. You could write a powershell script that you run that pops up a GUI, allowing you to select two excel files to compare, then the script does what you want it to do to compare them. You could use Powershell and WMI events to watch for excel to open a file and then take scripted actions based on that event. Likely, excel would open the file but at that point you could grab a handle on that process, get the info you need and use powershell to re-launch the excel process/file in any way you need, and the script would/could close that file using the handle, and you would have just the excel file you powershell script spawned based on that initial file open. It wouldn't be as clean as you were hoping but there is always a way to do what you are looking for - it just may not be the way you initially envisioned it.



    • Edited by mdkelley Tuesday, March 17, 2015 12:31 PM
    Tuesday, March 17, 2015 12:24 PM