VBA myshell.run error cannot open files

dellehurley

Board Regular
Joined
Sep 26, 2009
Messages
62
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.

VBA Code:
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
 

Some videos you may like

Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"

smozgur

BatCoder
Joined
Feb 28, 2002
Messages
1,270
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:
    VBA Code:
    Dim myShell As WshShell
  3. And change the initialization with the following:
    VBA Code:
    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

Board Regular
Joined
Sep 26, 2009
Messages
62
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:
    VBA Code:
    Dim myShell As WshShell
  3. And change the initialization with the following:
    VBA Code:
    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
Screenshot (21).png


My code now looks like
VBA Code:
[/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)]
 

Watch MrExcel Video

Forum statistics

Threads
1,122,448
Messages
5,596,210
Members
414,046
Latest member
mbeutler1203

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top