Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

open file based on cell

0

hi

I  have  this  code  to  open  file  based  on  cell G2 .    it  should  open  PDF extension

Private Sub Worksheet_Change(ByVal Target As Range)
 Dim searchfile As String, fname As String
 Dim oShell As Object
 If Target.Address = "$G$2" Then
 searchfile = "C:\Users\alhjh\Desktop\files\"
 fname = Dir(searchfile & Target.Value & ".pdf")
 If fname <> vbNullString Then
 If MsgBox(fname & " available. Do you want to open this file?", vbYesNo + vbInformation, "Open pdf file?") = vbYes Then
 Set oShell = CreateObject("WScript.Shell")
 oShell.Run searchfile & fname
 End If
 End If
 End If

End Sub

but  doesn't  work  and    no  error  

any  suggestion to  fix  it  please 

excel
MS Office Version: 2016
posted by: Kalil (rep: 36) on Thu Jul 1, 2021 at 6:31 am
Answer
Discuss

Discussion

The Shell object represents a DOS-prompt. Therefore oShell.Run searchfile & fname runs "searchfile & fname" from the prompt. The question you should answer is whether that is the most efficient way to open a PDF file using Excel VBA. Had you not already selected an answer I would have made a different suggestion. Had you asked such a question John would probably made a different suggestion, too.
As things stand I share your astonishment that removing the extension from the file name actually works. The only explanation I can offer is that your default PDF app somehow supplies this information. So, you need to consider DOS and Acrobat and Excel. Any change in the software of any of them may cause your code to fail again in the future.
Variatus (rep: 4889) Jul 1, '21 at 11:10 pm
Add to Discussion

Answers

0
Selected Answer

Kalil

Try adding inverted commas - via Chr(34)- to this line as follows, changes in bold:

oShell.Run Chr(34) & searchfile & fname & Chr(34)
Not sure why you're using the Worksheet_Change event for this
posted by: John_Ru (rep: 6152) on Thu Jul 1, 2021 at 7:35 am
Discuss

Discussion

thanks. unfortunately still doesn't work and there is no error.
Kalil (rep: 36) Jul 1, '21 at 7:55 am
So you're entering a valid file name (less ".pdf") into G2 and it doesn't work? When I press Enter in G2, I get your requestor stating "<<file>>.pdf is available. Do you want to open it?" and, on pressing Okay, the pdf opens.

Have you tried copying/ converting the event macro into a normal module (and defining fname with Range("G2").Value" rather then target (and the first If?
John_Ru (rep: 6152) Jul 1, '21 at 8:01 am
I  did   by  normal  module  and  doesn't  works 
see  my  code  . is  it  right? 
Sub nn()
Dim searchfile As String, fname As String
 Dim oShell As Object
 Dim rng As Range
Set rng = Sheets("sheet1").Range("g2")
 searchfile = "C:\Users\PC WORLAD\Desktop\files\"
 fname = Dir(searchfile & rng.Value & ".pdf")
 If fname <> vbNullString Then
 If MsgBox(fname & " available. Do you want to open this file?", vbYesNo + vbInformation, "Open pdf file?") = vbYes Then
 Set oShell = CreateObject("WScript.Shell")
 oShell.Run Chr(34) & searchfile & fname & Chr(34)
 
 End If
 End If
 
End Sub
Kalil (rep: 36) Jul 1, '21 at 8:28 am
Looks okay but is "PC Worlad" correct in the path? (Rather than PC World) You need a correct path and a correct file name of course
John_Ru (rep: 6152) Jul 1, '21 at 8:33 am
I assume that you've already checked that your pdf reader (e.g. Adobe Acrobat Reader DC) is working and is set as the default app to open .pdf files, right?
John_Ru (rep: 6152) Jul 1, '21 at 8:48 am
Adobe Acrobat Reader DC) is working and is set as the default app to open .pdf files, right?
yes
Kalil (rep: 36) Jul 1, '21 at 10:38 am
Kalil, I can't really help; your code works for me (with my correct paths and file names and in both forms of macro). Also this isn't a discussion Forum, sorry.
John_Ru (rep: 6152) Jul 1, '21 at 11:40 am
John
I 've  found   my  mistake     I  should  write  the  file  without  extension .  now   it  works.   by   the  way   my  orginal   code  works   on  version 2010 office  without  problem   but  in  office2019  it  gives  error .  your  answer  about   update  code  line   it  works   so    I  consider  your   contribution  is  a valuble . thanks  buddy
Kalil (rep: 36) Jul 1, '21 at 4:37 pm
Kalil. Glad you fixed it, well done.

Thought it might be that (so I said early in our conversation "So you're entering a valid file name (less ".pdf") into G2...." but perhaps you missed it).

Not sure why Offcie 2019 doesn't work (I no longer thave a copy of that, sorry).
John_Ru (rep: 6152) Jul 1, '21 at 5:52 pm
Add to Discussion


Answer the Question

You must create an account to use the forum. Create an Account or Login