VBA myshell.run error cannot open files

[dellehurley]

Hello
I am trying to use the macros below to open files (pdf and jpg's) that are listed in a database. The file should open when double clicked on the filename which is in a listbox in userform.
It is finding the filename and folder name correctly. If the file is missing the macros correctly advises that but I cannot get it to open another document.
I think the issue is with these lines, when I watch this is what comes up.
Set myShell = CreateObject("WScript.Shell")
Watch : : WScript.Shell : <Expression not defined in context> : Empty : FrmForm.lstDatabase_DblClick
myShell.Run FolderName & FileName
Watch : : myshell.Run : <Wrong number of arguments or invalid property assignment> : Variant/Integer : FrmForm.lstDatabase_DblClick

Any help appreciated.

Private Sub lstDatabase_DblClick(ByVal Cancel As MSForms.ReturnBoolean)

    Dim FolderName As String, FileName As String
    Dim myShell As Object
    
'change folder path to database if required
    FolderName = ThisWorkbook.Path & "\"
    FileName = Me.lstDatabase.Value

    On Error GoTo myerror
    
    If Not Dir(FolderName & FileName, vbDirectory) = vbNullString Then
        Set myShell = CreateObject("WScript.Shell")
        myShell.Run FolderName & FileName
    Else
        Err.Raise 53
    End If

'report errors
myerror:
    If Err <> 0 Then MsgBox (Error(Err)), 48, "Error"
    Set myShell = Nothing
End Sub

 

[smozgur]

Your code works perfectly fine in my computer.
However, I don't know how, but it sounds like it doesn't create the shell object in your computer. Which one is the actual error line when you click Debug (You need to remove On Error Goto myerror line to see this)?

May be you can try the following with early binding:

1. Go to VBE->Tools->References, and select Windows Script Host Object Model library.
2. Change the declaration with the following:
   

   Dim myShell As WshShell

3. And change the initialization with the following:
   

   Set myShell = New WshShell

4. And try again.

Note: Although it doesn't matter here, I would use vbNormal instead of vbDirectory, because the code is certainly looking for a file.

 

[dellehurley]

Your code works perfectly fine in my computer.
However, I don't know how, but it sounds like it doesn't create the shell object in your computer. Which one is the actual error line when you click Debug (You need to remove On Error Goto myerror line to see this)?

May be you can try the following with early binding:

1. Go to VBE->Tools->References, and select Windows Script Host Object Model library.
2. Change the declaration with the following:
   

   Dim myShell As WshShell

3. And change the initialization with the following:
   

   Set myShell = New WshShell

4. And try again.

Note: Although it doesn't matter here, I would use vbNormal instead of vbDirectory, because the code is certainly looking for a file.

Hi Suat,
Thanks for the reply. I turned on the Script host as requested. This reminds me, while I was searching the web for a fix (before I posted this) I found someone telling me to turn on the Microsoft Shell Controls on. I have done so now but that couldn't be the issue or something involving that since it was not on when I wrote the macros?
I have now changed all the things you requested above.
On this line of code myShell.Run FolderName & FileName I get this error

My code now looks like

[/COLOR]
Private Sub lstDatabase_DblClick(ByVal Cancel As MSForms.ReturnBoolean)

    Dim FolderName As String, FileName As String
    Dim myShell As WshShell
    
'change folder path to database if required
    FolderName = ThisWorkbook.Path & "\"
    FileName = Me.lstDatabase.Value
    
    If Not Dir(FolderName & FileName, vbNormal) = vbNullString Then
        Set myShell = New WshShell
        myShell.Run FolderName & FileName
    Else
        Err.Raise 53
    End If

'report errors
myerror:
    If Err <> 0 Then MsgBox (Error(Err)), 48, "Error"
    Set myShell = Nothing
End Sub
[COLOR=rgb(0, 0, 0)]