Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

VBA Shell can't Find Word document

Posted on 2016-11-15
11
Medium Priority
?
250 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 58

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
 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

 

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 52

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 58

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 58

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 2000 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 52

Expert Comment

by:Gustav Brock
ID: 41889866
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

In earlier versions of Windows (XP and before), you could drag a database to the taskbar, where it would appear as a taskbar icon to open that database.  This article shows how to recreate this functionality in Windows 7 through 10.
This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
Learn how to make your own table of contents in Microsoft Word using paragraph styles and the automatic table of contents tool. We'll be using the paragraph styles in Word’s Home toolbar to help you create a table of contents. Type out your initial …
Learn how to create and modify your own paragraph styles in Microsoft Word. This can be helpful when wanting to make consistently referenced styles throughout a document or template.
Suggested Courses

610 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