Is there a way to write a search, find and open in vba?

Question

I am writing a code but dont know what the syntax is. I just want my code to search and find a pdf

Sub open1()

Dim pdfname As String
Const sPath = "S:\PROFILE ORDERS\"
Dim path1

pdfname = Application.InputBox("Enter the pdf you are looking for")
pdfname = pdfname & ".pdf"

path1 = Dir(sPath & pdfname)

path1.Open

End Sub

Possible duplicate of [VBA macro that search for file in multiple subfolders](https://stackoverflow.com/questions/20687810/vba-macro-that-search-for-file-in-multiple-subfolders) — FAB, Jun 05 '19 at 12:56
There are a few things to consider here: Are there any subfolders, what to do if the file isn't found, the pdf you are looking for needs to be an EXACT match to the filename (otherwise it isn't found). — Luuklag, Jun 05 '19 at 13:00

score 1 · Answer 1 · answered Jun 06 '19 at 10:03

Sub OpenPdf()

    On Error GoTo OpenPdf_Error

    Dim pdfname As String
    Dim pdf
    Const sPath = "S:\RA QUOTES 2019"
    Dim FName As String
    Dim arNames() As String
    Dim myCount As Integer
    Dim i As Integer

    FName = Dir("S:\RA QUOTES 2019\*.pdf*")
    Do Until FName = ""
        myCount = myCount + 1
        ReDim Preserve arNames(1 To myCount)
        arNames(myCount) = FName
        FName = Dir
        Loop


    pdfname = Application.InputBox("Enter the pdf you are looking for")
    pdfname = "PLQ" & pdfname




For i = 1 To UBound(arNames)

If IsInArray(pdfname, arNames(i)) = True Then

    ThisWorkbook.FollowHyperlink sPath & arNames(i)

     End If

    Next i

    On Error GoTo 0
    Exit Sub

OpenPdf_Error:

    MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure OpenPdf"

End Sub

[Why are your arrays starting from 1?](https://www.google.com/search?q=array+starting+at+1&safe=strict&source=lnms&tbm=isch) — Vityata, Jun 06 '19 at 13:24

score 0 · Answer 2 · answered Jun 05 '19 at 13:02

0

As far as you give the directory in which to "search" it is not a real search. Pretty much, everything needed could be just in one line:

ThisWorkbook.FollowHyperlink S:\PROFILE ORDERS\somePdf.pdf

the rest depends on how do you want to aproach it. The code below would throw an error, if there is no such file in the specified directory.

Sub OpenPdf()

    On Error GoTo OpenPdf_Error

    Dim pdfname As String
    Const sPath = "C:\Users\gropc\Desktop\"

    pdfname = Application.InputBox("Enter the pdf you are looking for")
    pdfname = pdfname & ".pdf"

    ThisWorkbook.FollowHyperlink sPath & pdfname

    On Error GoTo 0
    Exit Sub

OpenPdf_Error:

    MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure OpenPdf"

End Sub

answered Jun 05 '19 at 13:02

Vityata

42,633
8
55
100

Thanks that helps a lot. Is there a way to splash page with this line ThisWorkbook.FollowHyperlink sPath & pdfname – Jun 05 '19 at 13:44
@TechTutors - you are welcome. What do you mean with "splash page"? – Vityata Jun 05 '19 at 14:16
Set the height and width of the dimensions of the pdf to half the screen? – Jun 06 '19 at 05:48
Please see my answer. I have one line barring me from completing the code – Jun 06 '19 at 10:03
@TechTutors - which line? – Vityata Jun 06 '19 at 13:21

Is there a way to write a search, find and open in vba?

2 Answers2