1

I'd like to open a PDF file using an Excel VBA macro.

I have a list of names in the excel file. As soon as the commandbutton "Open PDF" is pressed i would like the macro to open a pdf file from a certain location.

filename corrosponds with activecell.value

thanks in advance

Sub Knop1_Klikken()

Dim a As String
Dim myShell As Object

a = ActiveCell.Value

Set myShell = CreateObject("WScript.Shell")
myShell.Run "Z:\simbeton - Solidworks\bp - betonplaten\bp07 - simvlak ZH Sport\PDF\" & "a" & ".pdf"

End Sub

The error: (my MS is in dutch):

Fout -2147024894 (80070002) tijden uitvoering: Methode Run van object IWshSHell3 is mislukt

Translated: Error -2147024894 (80070002) during execution: Methode Run of object IWshSHell3 has failed.

Cornelis
  • 445
  • 3
  • 11
  • 27
  • Possible duplicate of [How can Excel VBA open file using default application](http://stackoverflow.com/questions/18921168/how-can-excel-vba-open-file-using-default-application) – CommonSense May 11 '17 at 08:49

2 Answers2

1

You can simply use WScript.Shell like this:

a = ActiveCell.Value
Dim myShell As Object
Set myShell =  CreateObject("WScript.Shell")
myShell.Run "C:\" & a & ".pdf"
Koby Douek
  • 16,156
  • 19
  • 74
  • 103
  • thanks so far... but i'm not familiar with the myShell function. Am i applying it the right way? Command button 1() Dim a As String a = ActiveCell.Value Dim myShell As Object Set myShell = CreateObject("WScript.Shell") myShell.Run "Z:\simbeton - Solidworks\bp - betonplaten\bp07 - simvlak ZH Sport\PDF\" & "a" & ".pdf" End Sub – Cornelis May 11 '17 at 08:47
  • Unfortunately it doesn't. I pasted the code as it is right now in my question. I get an error on the last line. Did i assigned "a" correctly? Or... does this not work for files on a server. (external HDD connected over the internet)? Thanks in advance – Cornelis May 11 '17 at 09:07
  • use `& a &`, dont use `""` around it. It's a variable. – Koby Douek May 11 '17 at 09:12
  • ah thanks...that was a stupid mistake... unfortunately i'm still getting the same error – Cornelis May 11 '17 at 09:18
  • What happens when you run `Z:\simbeton - Solidworks\bp - betonplaten\bp07 - simvlak ZH Sport\PDF\file.pdf` in the windows run window ? – Koby Douek May 11 '17 at 09:20
  • (Replace `file` with the file name exactly from the cell) – Koby Douek May 11 '17 at 09:21
  • So the problem is not in the script, the file path is incorrect. – Koby Douek May 11 '17 at 09:25
  • juist tried another file path on my desktop... same error. strange – Cornelis May 11 '17 at 09:28
  • simplify it by copying the pdf to `c:\` – Koby Douek May 11 '17 at 09:30
  • Desktop is a complex path. Listen to my advice and try from `C:\` – Koby Douek May 11 '17 at 10:08
1

Have you find out how it's done yet? If not, here 's the solution:

myShell.Run chr(34) & "C:\" & a & ".pdf" & chr(34)

chr(34) is a "

The difference is : your command sends C:\JouBetonInfo.pdf as an argument whereas my command sends "C:\JouBetonInfo.pdf" as an argument. Note the quotes that I send along. It works for me (Excel 2007).

msanford
  • 11,803
  • 11
  • 66
  • 93
Dave
  • 11
  • 4
  • I didn't found a sollution yet. I'll give this a try soon. Could you please explain why (and when) one should use chr(34) instead of a ". Is it because two "" after oneanother can't be red by vba? Why do you require double "" at the start and end any way? Thanks in advance! – Cornelis Oct 25 '17 at 10:20