Solved

VBA Shell can't Find Word document

Posted on 2016-11-15
11
169 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 85

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 55

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
Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

 

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 55

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 55

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 85

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

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

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

This article describes two methods for creating a combo box that can be used to add new items to the row source -- one for simple lookup tables, and one for a more complex row source where the new item needs data for several fields.
The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
This Micro Tutorial well show you how to find and replace special characters in Microsoft Word. This is similar to carriage returns to convert columns of values from Microsoft Excel into comma separated lists.

687 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