• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 289
  • Last Modified:

VBA Shell can't Find Word document

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
MDSS
Asked:
MDSS
  • 3
  • 2
  • 2
  • +2
1 Solution
 
oBdACommented:
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
 
Bill PrewCommented:
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
 
MacroShadowCommented:
Is the file stored on the remote server?
0
Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

 
MDSSAuthor Commented:
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
 
Gustav BrockCIOCommented:
Will

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

return the filename?

/gustav
0
 
MacroShadowCommented:
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
 
Bill PrewCommented:
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
 
Bill PrewCommented:
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
 
oBdACommented:
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
 
MDSSAuthor Commented:
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
 
Gustav BrockCIOCommented:
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 3
  • 2
  • 2
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now