Apply working Shell code in a different database

Question

I have used the "Shell" function, in other Access databases, to open folders.
With the same code structure I get the

5 error code of "Invalid procedure call or argument"

Using shell function as follows:

    Dim FreightFile_Path As String
    FreightFile_Path = "S:\Supply Chain\Freight"
    Shell "explorer.exe" & " " & FreightFile_Path, vbNormalFocus

I tried the double quotes and Chr(34)'s around them.
I copied the code from one database (that it worked in) to another and it error-ed.

Am I missing something I need to activate in MS Access? I checked the references in VBA and made sure they match.

Things I tried:

Call Shell("explorer.exe" & " " & Chr(34) & "S:\Shared" & Chr(34), 
vbNormalFocus)

Shell "explorer.exe " & Chr(34) & FreightFile_Path & Chr(34), vbNormalFocus

Shell "explorer.exe" & " " & FreightFile_Path, vbNormalFocus

Dim retVal
retVal = Shell("explorer.exe" & " " & FreightFile_Path, vbNormalNoFocus)

Dim i As String
i = "explorer.exe" & " " & FreightFile_Path
Shell i, vbNormalFocus

FreightFile_Path = "S:\Supply Chain\Freight"
Shell "explorer.exe " & FreightFile_Path, vbNormalFocus

Restarted the application, restarted the computer.

It is just a string to a folder. I have literally set it to just "S:\Supply Chain\Freight" for testing purposes. I dont really want to open a specific file like a pdf or an excel document because there will be multiple in this folder. I have also tried using the full server path and that didnt work either — goldgerm, Jun 19 '19 at 20:38
The code in the top snippet works perfectly fine here, ...given a valid path, with or without spaces in it. Is the `S` drive a mapped network drive? (should work fine too) - I can't repro the error 5 you're getting, even with an invalid path (it opens "my documents") — Mathieu Guindon, Jun 19 '19 at 20:39
FWIW the `Shell` function is defined in the `VBA.Interaction` module; it's in the VBA standard library, just like `MsgBox`. No "special references" are needed. — Mathieu Guindon, Jun 19 '19 at 20:44
I realize it is very strange. I have another database on the same computer that the same exact code works perfectly fine. I am just wondering if I dont have a setting activated or something that is preventing it from working — goldgerm, Jun 19 '19 at 20:45
Stupid question: does the project compile? Debug ~> Compile VBAProject — Mathieu Guindon, Jun 19 '19 at 20:52
Yep, everything is compiled. Here is a photo of the code side by side. If you just look at the highlighted areas that is what is running (Rest of stuff would work if I wasnt testing). But it works on one and not the other https://imgur.com/YUpVQAW Saw i had the call shell not commented out, but still - commented out that line and same result — goldgerm, Jun 19 '19 at 20:53
Is it possible that the symbol `Shell` has been redefined elsewhere in the database? — Lee Mac, Jun 19 '19 at 22:24
For what it's worth, I'm having the exact same problem: `Shell` works in one database but not another. My test is as simple as it gets: `Shell "notepad.exe"`. The question is here: https://stackoverflow.com/questions/56676948/access-vba-shell-command-broken-in-one-particular-database — MarredCheese, Jun 20 '19 at 01:07

score 1 · Accepted Answer · answered Jun 20 '19 at 18:05

1

I just had the same problem. In my case, it turned out to be anti-virus that was blocking Shell. It just so happened that IT had put exceptions in place for my computer for one database but not the other. See my question and answer for more detail.

answered Jun 20 '19 at 18:05

MarredCheese

17,541
8
92
91

This is the answer. Thank you – goldgerm Jun 20 '19 at 20:15
@goldgerm Sweet! It's so weird how we both had the same strange problem on the same day. Glad it worked out :) – MarredCheese Jun 20 '19 at 20:22
Very much so. It was driving me nuts! Between the 2 databases it was literally THE. SAME. CODE. and still not working – goldgerm Jun 20 '19 at 20:38

score 0 · Answer 2 · answered Jun 19 '19 at 20:55

0

Try this:

FreightFile_Path = "S:\Supply Chain\Freight"
Shell "cmd /c start explorer.exe """ & FreightFile_Path & """"

It is a bit of a workaround, but it works...

answered Jun 19 '19 at 20:55

Sam

5,424
1
18
33

Thanks for the answer, unfortunately no dice. Still got the error 5 What's strange is I paste that into a different database and it works just fine, I wonder if i just have a corrupt access file... – goldgerm Jun 19 '19 at 20:59

score 0 · Answer 3 · answered Jun 19 '19 at 21:07

New try. Use a WinAPI call

Declare Function ShellExecute Lib "shell32.dll" Alias "ShellExecuteA" (ByVal hwnd As Long, _
    ByVal lpOperation As String, ByVal lpFile As String, ByVal lpParameters As String, _
    ByVal lpDirectory As String, ByVal lpnShowCmd As Long) As Long

Public Sub ShellEx(ByVal Path As String, Optional ByVal Parameters As String, Optional ByVal HideWindow As Boolean)
    If Dir(Path) > "" Then
        ShellExecute 0, "open", Path, Parameters, "", IIf(HideWindow, 0, 1)
    End If
End Sub

Sub Test()
    FreightFile_Path = "S:\Supply Chain\Freight"
    ShellEx "c:\windows\explorer.exe", """" & FreightFile_Path & """"
End Sub

score 0 · Answer 4 · edited Jun 20 '19 at 16:03

0

Thank you everybody for the help. This might not really be an answer to the Shell problem, but it will work for opening a file path.

Dim FreightFilePath As String
FreightFilePath = "S:\Supply Chain\Freight"
Application.FollowHyperLink FreightFilePath

edited Jun 20 '19 at 16:03

MarredCheese

17,541
8
92
91

answered Jun 20 '19 at 13:33

goldgerm

23
2

Apply working Shell code in a different database

4 Answers4