none
File not found when using Shell in Access runtime RRS feed

  • Question

  • This is really a Shell question, and the answer could be more Windows-related than Access; however, I am also confident that it would  have been rejected as off-forum had I posted it on a Windows forum. So here goes.

    I have an Access app that opens Excel using Shell. All works fine when running the full version of Access 2013, but when using the runtime version, I get File Not Found error 53 on the last line below:

    Dim TemplateFile As String
    TemplateFile = "C:\MyFile.xls"
    Dim OpenExcel As Variant
    OpenExcel = Shell("Excel.exe " & """" & TemplateFile & """", 0)

    The problem is not with the path to TemplateFile, but with the path to Excel. Oddly, I can open the Run window and enter excel.exe and have Excel open. Or I can enter Excel.exe "C:\MyFile.xls" and have it open the target spreadsheet. But I cannot do either of these from the command prompt, which led me to the discovery below.

    Here is what I think is happening:

    With the full version of Access (even the 32-bit version), Excel.exe and MSAccess.exe reside together in the same folder downline from Program Files, where I would expect 64-bit apps to reside instead of Program Files (x86), where I would expect 32-bit apps to reside. But Access runtime is installed here by default (i.e. downline from (x86):

     C:\Program Files (x86)\Microsoft Office\Office15

    instead of here, where the full version would have been installed and where Excel.exe lives:

     C:\Program Files\Microsoft Office 15\root\office15

    I infer that the File Not Found error does not occur in the full version of Access 2013 because the Shell command begins by looking in the folder from where the current application (i.e. MSAccess.exe) was opened, which is downline from Program Files, and it finds Excel.exe there. But the runtime version encounters an error because Access is downline from Program Files (x86); when it looks for Excel, it is not there--Excel is in another folder, downline from Program Files.
     
    Yes, I already know I can add C:\Program Files\Microsoft Office 15\root\office15\ to my Path environment variable to solve the problem. However, that certainly adds complexity for me when attempting to deploy this app to client computers. I have to edit the Path environment variable to get it to work with runtime, but I do not know beforehand which stations have Access runtime and which have the full version. In fact, I am often not privy to setup at all; I can control only the content of my app, not the desktop environment. I can only require that Access 2010 or 2013, full or runtime, be installed.

    So I have three questions. For the first two, I know I am maybe expecting a bit of mind-reading of the MS developers, but I will ask anyway:

    1. Why does the 32-bit version of Office 2013 go downline from Program Files instead of Program Files(x86) like other 32-bit programs?
    2. Given that the 32-bit version of Office 2013 is downline from Program Files, why does MS then have the 32-bit runtime version of Access go downline from Program Files(x86), where there are bound to be inter-operability issues like this at some point?
    3. Any ideas on workarounds that would make deployment simpler? One thing I have not yet tried is to manually remove (x86) from the path when installing Access 2013 runtime. That will be my next test, but I half-expect Windows to tell me it cannot be done. Or perhaps there is a better way to get this done than using Shell? The ultimate goal is to import a CSV file that has requires some cleanup before TransferText to avoid erros, such as removing footer records). So TemplateFile above is an XLS file that contains an Auto_Open macro that then opens that CSV file, reformats the data, and saves it in a format that Access can easily import via TransferText in my code immediately following the code above.

    Bottom line: I am not stuck on using Shell to do this, but if I use it, there are issues finding Excel from runtime Access. Kind of dumb

    Thursday, August 13, 2015 3:52 PM

Answers

  • Hi Brian. I like Hans approach but you could also try using the ShellExecute API, which checks the system registry for the application that's registered to open the target file. Just a thought...
    • Marked as answer by Brian D. Hart Sunday, August 16, 2015 11:57 PM
    Friday, August 14, 2015 1:26 AM

All replies

  • I'd take a different approach:

        Dim TemplateFile As String
        Dim xlApp As Object
        Dim xlWbk As Object
        TemplateFile = "C:\MyFile.xls"
        Set xlApp = CreateObject(Class:="Excel.Application")
        Set xlWbk = xlApp.Workbooks.Open(FileName:=TemplateFile)

    This is independent of the version, bitness and location of Excel.


    Regards, Hans Vogelaar (http://www.eileenslounge.com)


    Thursday, August 13, 2015 7:20 PM
  • Hi Brian. I like Hans approach but you could also try using the ShellExecute API, which checks the system registry for the application that's registered to open the target file. Just a thought...
    • Marked as answer by Brian D. Hart Sunday, August 16, 2015 11:57 PM
    Friday, August 14, 2015 1:26 AM
  • A few things:

    First, you can use Application.FollowHyperlinnk “path to excel file name”

    Using the above would thus not require you to worry or care about where Excel.exe is. In fact you can use the above to launch + open any windows file (as if you clicked on it).

    Eg:

    Application.FollowHyperLink "c:\Mypdf\Invoice.pdf"

    The above would thus launch the default pdf viewer. So you can use the above for word, excel, pdf’s etc. and no need to include the path name to the application associated with the file extension.

    Also, the Shell() command may be failing in your case because the default folder that Shell() uses may well be different. In other words, if a VBA chdir occurs, or starting folder is changed (or specified on startup), then you need to supply the FULL path name to Excel.exe, and not “hope” that your current default path is the office15 folder (that is a bad choice here – you cannot be sure of this).

    Note the following shortcut to a access application:

    Note the start in folder – that’s not necessary going to be the office 15 folder.

    You could also pull the path name to msaccess.exe with this command:

    SysCmd(acSysCmdAccessDir)

    (try the above in the access debug window:

    Eg:

    Debug.print SysCmd(acSysCmdAccessDir)

    So the above would get you the current folder that msaccess.exe resides (and thus where excel.exe resides).

    A few more things:

    >>Run works, but cmd line does not.

    That is because run uses “start” “whatever you type in”

    So you can at the command line go:

    Start Excel.exe

    And it will launch excel. And just like the above VBA hyperlink command, you can also supply a file name.

    So you can go:

    Start “path name to some document”

    So you again don’t need to include the path name to Excel.exe, but JUST the document location.

    Some more things:

    >But Access runtime is installed here by default (i.e. downline from (x86):

     C:\Program Files (x86)\Microsoft Office\Office15


    The above is the case for the x32 bit version of Access (and runtime ALWAYS is IDENTICAL to full version of Access (read this sentence several times – always the same!!!).

    >the full version would have been installed and where Excel.exe lives:
     C:\Program Files\Microsoft Office 15\root\office15

    No the above is ONLY for the x64 bit version of office. (and root is useally NOT part of the path name) If your msaccess is in the above, then you running office 64.

    ALL of office ALWAYS installs to the SAME dir. The reason is that ribbon code, the VBA system, spell check and a HUGE list of other parts is SHARED code. This is also why you cannot install Access x32 and Access x64 on the same computer (no more so then you can install say the full edition of Excel two times on the same computer!).

    You cannot change the install location of the Access runtime (or full version) if ANY other parts of office have been installed. In fact you cannot install the full edition of access and the runtime edition on the same machine – only ONE copy of Access of a given version can reside on the computer. And that copy will reside in the shared code folder with the rest of office.

    In fact if you are the first one in, and install Access to different custom folder (runtime or full), then Excel, word outlook etc. will ALWAYS follow and install to that same folder!!! You cannot change the install location ONCE any part of office has been installed for a given version (due to shared code).

    >>instead of here, where the full version would have been installed and where Excel.exe lives:

    >> C:\Program Files\Microsoft Office 15\root\office15

    I was not aware that root was included in above, but regardless, the above is the x64 bit version and location of office. The above suggests you are running office x64 in place of x32.

    I would suggest you use Application.FollowHyperLink (path to the document you want to open).

    You can also get the current running location of msaccess.exe by using


    SysCmd(acSysCmdAccessDir)


    As noted, when you startup Access, the default folder could be anything.

    So in closing:

    So access x32 will ALWAYS install to:

    C:\Program Files (x86)\Microsoft Office\Office15\MSACCESS.EXE


    And access x64 will ALWAYS install to:


    "C:\Program Files\Microsoft Office\Office15\MSACCESS.EXE"

    In your case, the “root” part suggests that a custom install occurred, and also that you are running office x64.

    I would strongly suggest you develop with access x32 if you going to deploy to machines with Access x32. In fact you cannot compile down to an accDE and run with mixed versions of Access (an accDB will work for different bit sizes because the source code is available and will re-compile on the fly (startup)).

    So if you plan to distribute a accDE (and you should), then you have to create and compile the accDB with the correct version of Access into the accDE.  Access 32 cannot run a Access x64 accDE, and the reverse is also the case.

    Regards,
    Albert D. Kallal (Access MVP)
    Edmonton, Alberta Canada

    Friday, August 14, 2015 8:38 PM
  • Thank you, all. I am sure all your methods are better than mine, and I will test a couple to see which is simplest.

    Albert: comment on 32-bit vs 64-bit. What you say matches what I have always seen and believed to be true--that 64-bit always goes downline from Program Files, 32-bit always downline from Program Files (x86).

    But I have to defer to my eyes in this case. Office 2013 does reside downline from Program Files, but when I went to install Access runtime 64-bit on the theory that the 32-bit version was my problem, the setup routine told me I had to remove all 32-bit Office applications before I could install any 64-bit Office application. That is, it would let me install ONLY the 32-bit Access runtime.

    So, although I find you to be always right (really!), I cannot argue with what Windows told me when I attempted the 64-bit setup.

    And I always develop in 32-bit Access for the sake of consistency; this way, I can be sure I need only the 32-bit MySQL driver (and any other architecture-specific ODBC drivers), etc. And since I am not primarily a developer, I do not keep development stations populated with both 32-bit and 64-bit versions of Office, only 32-bit.

    Perhaps part of the issue is that this is not a system I fully control; my client's IT department did all the installations until I came along and found the Access problem. But I ended up back where they started: with Office downline from Program Files and Access Runtime downline from Program Files(x86). Other than the possibility that the IT department actually browsed there to force the 32-bit installation there, I have no idea how they accomplished this, and I suppose that part of my question will just have to remain a mystery.

    In the end, that was not the root of my problem anyway, so this all did one very good thing for me, though--forced me to look at a much better method for opening Excel from Access. I will probably test all three, since it is always best to know one's options. At the least, it is pretty clear that my use of Shell was a rather poor method.

    Thank you again!

    Saturday, August 15, 2015 12:48 PM
  • Hans,

     I tried this:

      Dim TemplateFile As String
      Dim xlApp As Object
      Dim xlWbk As Object
      TemplateFile = "C:\MyFile.xls"
      Set xlApp = CreateObject(Class:="Excel.Application")
      Set xlWbk = xlApp.Workbooks.Open(FileName:=TemplateFile)

     This opens Excel hidden and presumably opens the correct file, since Windows puts a lock on the file in its network location (C:\file.xls was just an example; it is actually on a network drive). But it does not run the Auto_Open macro in the Excel workbook. My Auto_Exec macro should (as it does when using Shell) open a CSV file, reformat it, save it, and then close Excel.

     But none of this happens. VBA progresses to the next line, but the Auto_Open macro in the Excel workbook is not run (i.e. the file it produces never appears in the file system), and it just leaves excel.exe process running hidden. I can force Excel to close by adding this:

      xlWbk.Close
      Set xlWbk = Nothing

    But still, the worksheet never gets around to running the Auto_Open macro as it does when I Shell. I am probably missing something I need that will allow Excel to run its Auto_Open macro when opened via the Application object.

    Sunday, August 16, 2015 11:53 PM
  • Albert,

     I tried this:

     TemplateFile = <\\NetworkPath\TemplateFileName.xls>
     Application.FollowHyperlink TemplateFile, , True, False

     And I get this error:

      ---------------------------
      Microsoft Office
      ---------------------------
      Opening \\NetworkPath\TemplateFileName.xls

      Some files can contain viruses or otherwise be harmful to your computer.
      It is important to be certain that this file is from a trustworthy source.

      Would you like to open this file?


      ---------------------------
      OK   Cancel  
      ---------------------------
      
     
     I cannot see any way to prevent this prompt and I see no further arguments that will prevent this. Admittedly, I may have shortchanged your ability to answer fully by using a local file example (for simplicity, not to fool you!) when the actual TemplateFile is on a network drive, where Trusted Sites may come into play when opening via FollowHyperlink. Still, there there is no sense in adding yet another level of complexity by requiring Trusted Sites configuration for this one thing.

    Sunday, August 16, 2015 11:53 PM
  • DB guy,

    I guess you take the edge in this round of me vs. the computer, despite two answers by other experts (at least until one or both of them ferrets out the shortcoming in my use of their suggested code, as I just posted). I suspect that my failure to get either of those to work satisfactorily is likely a shortcoming in my attempts to implement Hans & Albert's suggested code, and not in their code bits; but a bit of research online gave me sufficient info to at least write the functions necessary to use ShellExecute.

    So I tested it on a dev machine having the full version of Access; now I just need to test it on the affected computer that has Office 32-bit erroneously downline from Program Files and Access Runtime downline from Program Files(x86). At this point, I kind of assume that part will not be a failing point. And to be fair to Hans & Albert, I would not be at all surprised if they have additional info that may make their methods work also.

    Sunday, August 16, 2015 11:56 PM
  • Hi Brian. Thanks, happy to assist. Checking the registry should be a safe bet, but there's really several ways to accomplish the same thing. Please let us know if you're also able to use the other methods in the future. Good luck with your project.
    Monday, August 17, 2015 2:35 AM