2

In my Access database, I have a button on a form to open an external file. Here is the code that I am using for that.

Private Sub btn_OpenFile_Click()
     Dim a As New Shell32.Shell
     Dim strPath As String
     strPath = Me.Attachment
     strPath = Chr(34) & strPath & Chr(34)
     Call a.ShellExecute(Me.Attachment)
     'Call CreateObject("Shell.Application").ShellExecute(strPath)

'MsgBox strPath
End Sub

The problem that I have is if I actually put in the value of the variable (Me.Attachment) it works fine and opens the program and the file.

For Example, If I put in Call a.ShellExecute("C:\Docs\Some File.pdf") it will open. But if I use the variable in it's place it won't open and tells me it cannot find the file. I have verified with the msgbox that it is receiving the correct information. I have tried to wrap it in quotes and have used the Chr(34) as shown above but nothing works.

How can I get that variable to work in the ShellExcute command?

I have looked through all the forums and it seems like everyone is using a string but not a variable. I don't want to use just the shell command as I don't want to track down all of the different apps people use to open different types of files. There will be different file types that will need to be opened and I thought this would be easier than it actually is.

Thank you for the help.

jsvanc
  • 23
  • 3
  • Is `Attachment` an attachment type field or a text field? You verified strPath receives an actual full path string? Using variable works for me, without the Chr(34) line. – June7 Jan 22 '22 at 10:05
  • Attachment is a text field. I have tried it with and without the Chr(34) line. I have tried with and without a file path that has spaces also. Don't know if it matters, but I am using access 365 64-bit. – jsvanc Jan 22 '22 at 16:10
  • I use Access 2010 32-bit. – June7 Jan 22 '22 at 19:02

2 Answers2

0

I'm pretty sure that ShellExecute expects a Variant parameter, not a String.

So try this:

Call a.ShellExecute(CVar(strPath))

or use a Variant variable from the start.

I had the same problem here.

Andre
  • 26,751
  • 7
  • 36
  • 80
  • I think I looked at that post and tried it already with the .Open Cvar but I will try it with this line of code and let you know. Thanks. – jsvanc Jan 22 '22 at 16:13
  • I tried it with that line of code and I have the same problem. It acts like when using the variable in the ShellExecute statement that it strips the quotes from that statement. When I use the msgbox to check the value of strPath with the chr(34) it shows double quotes, but when the error comes up during the call statement and shows the path\filename it is not showing double quotes around it. – jsvanc Jan 22 '22 at 18:26
  • As stated in my comment above, tested with string variable and works for me. – June7 Jan 22 '22 at 19:01
  • Since OP says it works with literal string, it should work with string variable. – June7 Jan 22 '22 at 19:11
  • Shell.Application.Open works with a literal string, but doesn't with a string variable. But this case may be different. @June7 – Andre Jan 22 '22 at 22:59
  • @June7 Ok, so I uninstalled 64-bit office and installed 32-bit office with no change in behavior. so in vba, i set strPath="C:\SomeFileName.pdf" and then ran the command a.ShellExecute(strPath) and that works fine. So it is somewhere in the conversion of Me.Attachment where it is running into the problem. Just not sure why it is happening. – jsvanc Jan 23 '22 at 18:56
  • Might post to a forum that allows attaching files and provide db for analysis. – June7 Jan 23 '22 at 20:27
  • So I found something using debug.print. If I use the string variable, it is putting in a return at the end of the line before the last set of quotes so the last quote is on the next line. Any idea how to fix this? Is it something in the way it is getting stored in the field or in the database? Not sure what do to about that. Thanks again! – jsvanc Jan 26 '22 at 02:11
0

Both of the following work for me:

    Dim a As New Shell32.Shell
    Dim strPath As String
    strPath = Me.Attachment
    Call a.ShellExecute(strPath)

    Dim a As Shell
    Dim strPath As String
    strPath = Me.Attachment
    Set a = CreateObject("Shell.Application")
    a.ShellExecute strPath

Even referencing Attachment directly.
a.ShellExecute(Me.Attachment)

June7
  • 19,874
  • 8
  • 24
  • 34