Excel VB Shell Command Help!!

[gman1979]

Howdy,

I'm in the process of building a user form that required the user to brows for a file, the path of the file that they select is then displayed in the textbox next to the "browse" button in my form. I also have another button next to this which when selected will open the file that is displayed in the textbox previously mentioned.

I’ve been trying the shell command but with no success, I can get the user form to open adobe reader but whenever I try to include that file path for which to open the file I keep getting runtime errors, can anyone help.

Below are a couple of examples that I’ve tried (you'll prob be able to guess from the code that I’m very new to VB in excel).

For additional info the value in the textbox would be something like, "H:\My Documents\PDF Files\myfile.pdf"

Dim GetFile As String
Shell "C:\Program Files\Adobe\Reader 8.0\Reader\AcroRd32.exe", vbNormalFocus
'this opens adobe reader but I'm trying to open a specific file

Shell "C:\Program Files\Adobe\Reader 8.0\Reader\AcroRd32.exe" & textbox9.value, vbNormalFocus
This produces the runtime error again

Dim GetFile As String
GetFile = TextBox9.Value
Shell "C:\Program Files\Adobe\Reader 8.0\Reader\AcroRd32.exe" & GetFile, vbNormalFocus
'this produced a runtime error - to be honest this was an attempt born out of frustration.

Any help would be greatly appreciated.

Thanks in advance

G

 

[Richard Schollar]

hi

try this:

Shell "C:\Program Files\Adobe\Reader 8.0\Reader\AcroRd32.exe " & GetFile, vbNormalFocus

There is a space inserted after your application location in the string passed to Shell.

 

[gman1979]

Cheers, I feel like a total idiot for not noticing that!!

Adobe reader is now opening and it is trying to open the file, however it's displaying a message that the file cannot be found, will this be down to format of the path being returned by the textbox9 as the file is there and opens no bother separately.

Thanks again

G

 

[Richard Schollar]

What's the value being returned by TextBox9?

 

[gman1979]

It’s the full file path to the PDF file including the file itself. I’ve worked out why it won’t open though. All the folders and sub folders, including some of the file name all have spaces in them; once I removed the spaces it opened no bother.

Thing is.... (You knew it was coming!)

There a huge volume of files that I want this thing to reference to, is there anyway of getting it to work with the spaced in the path??

otherwise the whole thing is scuppered (nnnnNNNNNNOOOOOOooooo)

Cheers Again

G

 

[Richard Schollar]

Pass it like this:

GetFile = """" & TextBox9.Value & """"
Shell "C:\Program Files\Adobe\Reader 8.0\Reader\AcroRd32.exe " & GetFile, vbNormalFocus

 

[Kenneth Hobson]

If Adobe is associated, then you can use CMD /C. The Quote function would add quotes if needed.

Sub Test()
  Dim myFile As String
  myFile = Quote("w:\kenpdf.pdf")
  Shell "CMD /c " & myFile
End Sub

Function Quote(str As String) As String
  If Left(str, 1) = """" Then
    Quote = str
    Else: Quote = """" & str & """"
  End If
End Function

 

[gman1979]

Magic!!

Thanks for that, would I also be able to apply the above to open windows explorer in the folder that the file was in (as this would be stored in textbox10 as below.

Private Sub OpenFolder_Click()
Dim GetFolder As String
GetFoder = """" & TextBox10.Value & """"
Shell "C:\WINDOWS\explorer " & GetFolder, vbNormalFocus
End Sub

Sorry for the sneaky 2nd question but I’ve been wracking my brain all day with this and you've solved it in a matter of minutes!!!

Thanks again!

G

 

[gman1979]

Oops!!!!

spelling mistake, it works perfectly


Thanks again guy's

G

 

[Richard Schollar]

Yep:

Shell "Explorer.exe " & """" & GetFolder & """", vbNormalFocus