Solved

VBA Shell can't Find Word document

Posted on 2016-11-15
11
131 Views
Last Modified: 2016-11-16
I have an Access application that opens a Word document using this code:

x=Shell("C:\Program Files (x86)\Microsoft Office\root\Office16\WINWORD.EXE C:\Test\Test.doc",1)

This works fine for all machines except 4 that are on a separate network ... It says it can't find the document ... but the document is present and opens manually.

What's going on?
0
Comment
Question by:MDSS
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
  • 2
  • +2
11 Comments
 
LVL 84

Expert Comment

by:oBdA
ID: 41888109
VBA seems to do some pretty complex parsing trying to identify the program and arguments, even if it is basically specified incorrectly like here, but it can't do miracles.
When using paths with spaces, you should add quote characters around the respective strings, that is, the program path and the document path. You can create a single character quote inside a string by using two consecutive double quotes.
So try
x = Shell("""C:\Program Files (x86)\Microsoft Office\root\Office16\WINWORD.EXE"" ""C:\Test\Test.doc""")

Open in new window

That aside, to exclude an error that might be way too obvious: you made sure that the machines in question have the correct Office version (as specified in the program path) installed?
0
 
LVL 54

Expert Comment

by:Bill Prew
ID: 41888275
Just a small thought, I often find it easier to add the needed double quotes with a small helper function rather than using embedded pairs of double quotes.  I find it a little cleaner and typically avoid at least one or two mistakes in building the quoted substrings in a larger string field.  But naturally personal taste comes into play here.  I also like to build the full command line in a variable before passing it to the Shell command, makes it a little easier to read and debug.  Again just thoughts to consider.

Command = Quote("C:\Program Files (x86)\Microsoft Office\root\Office16\WINWORD.EXE") & " " & Quote("C:\Test\Test.doc")
x = Shell(Command, 1)

Function Quote(s As String) As String
   Quote = Chr(34) & s & Chr(34)
End Function

Open in new window

~bp
0
 
LVL 27

Expert Comment

by:MacroShadow
ID: 41888320
Is the file stored on the remote server?
0
Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

 

Author Comment

by:MDSS
ID: 41888524
Neither of these suggestions made a difference .... it still opens Word and says it can't find the file C:\Test\Test.doc.  ... (the file is located on the local C: drive by the way)

What's puzzling is that this syntax works on hundreds of other computers, just not these 4 ... I thought perhaps Office needed to be repaired, but it would be strange if all 4 had that problem.  It's like something in their environment or a setting is keeping the file from being seen.
0
 
LVL 50

Expert Comment

by:Gustav Brock
ID: 41889306
Will

? Dir("C:\Test\Test.doc", vbNormal)

return the filename?

/gustav
0
 
LVL 27

Expert Comment

by:MacroShadow
ID: 41889313
Is the path to winword.exe the same on those four machines as it is on the other machines?
The default installation path is:
C:\Program Files (x86)\Microsoft Office\Office16\WINWORD.EXE
not
C:\Program Files (x86)\Microsoft Office\root\Office16\WINWORD.EXE
Perhaps on them the default settings were used.
0
 
LVL 54

Expert Comment

by:Bill Prew
ID: 41889576
It might be safer to determine the path to the Word EXE dynamically at runtime rather than hard coding, using something like this.  Naturally you would want some error checking added to make sure Word was actually installed and you got a path to the EXE.

    Dim objShell As Object
    Dim strWordExe As String
    
    Set objShell = CreateObject("WScript.Shell")
    strWordExe = objShell.RegRead("HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows\CurrentVersion\App Paths\Winword.exe\")
    
    Shell (strWordExe & " " & """C:\Test\Test.doc""")

Open in new window

~bp
0
 
LVL 54

Expert Comment

by:Bill Prew
ID: 41889583
Are you getting one of these error messages when you run your Access code, or is it something different.  What does it say exactly?

~bp

sshot-49.png

sshot-50.png
0
 
LVL 84

Accepted Solution

by:
oBdA earned 500 total points
ID: 41889603
The path to the exe is not the issue, see MDSS's comment at https:#a41888524: it still opens Word and says it can't find the file C:\Test\Test.doc.

What is the OS on the machines where it works, and the machines where it doesn't work?
Is it the same behavior for all users on a machine where it doesn't work?

Please copy the path to the document directly from the Access macro into the clipboard, without surrounding quotes.
Then open a command prompt (cmd.exe), enter
dir ""
move the cursor one step back, so that it is between the two double quotes, right-click the command prompt's title bar, and choose Edit > Paste.
The line should now look like
dir "Path"
Finally hit <Return>.
Does it list the file in question?
If not, hit <Arrow up> to recall the "dir" command, and add "/a" after "dir", so that the command looks like
dir /a "Path"
Does it list the file in question now?
If yes, open the file's properties and remove the Hidden and/or System attributes.
If it's still not there, the path in the Macro is indeed not the actual path to the document, there's a typo somewhere.
0
 

Author Comment

by:MDSS
ID: 41889832
Problem solved ... based on the suggestion to go to DOS prompt and doing the directory listing I discovered the file name had been changed to C:\Test\test.doc.docx    .... renaming the file solved the issue.

Thanks for the tip
0
 
LVL 50

Expert Comment

by:Gustav Brock
ID: 41889866
0

Featured Post

Secure Your Active Directory - April 20, 2017

Active Directory plays a critical role in your company’s IT infrastructure and keeping it secure in today’s hacker-infested world is a must.
Microsoft published 300+ pages of guidance, but who has the time, money, and resources to implement? Register now to find an easier way.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Using Word 2013, I was experiencing some incredible lag when typing.  Here's what worked for me....
As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
Office 365 is currently available in five editions. Three of them are for business use: Office 365 Business Essentials, Office 365 Business, and Office 365 Business Premium. Two of them are for home/personal use: Office 365 Home and Office 365 Perso…

696 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question