How to open a .PDF file with wild card option via excel macro

Question

Since I am very new to the excel macro I am trying to develop a code which is able to open the PDF file.But There are some PDF files in my system which are generated by another system therefore those files names change day by day and some figures are included too.

As an example,"Process Report 151120183569844" like this.These figures change everyday.I tried it with adding WILDCARD option but it doesn't work.How do I open this PDF with only a part of the file name?

     Sub Open_PDF()
    Dim pdfPath As String
    pdfPath ="D:\Reports\Process Report*" & ".pdf" 'I used wildcard instead "Process Report 151120183569844"'
Call OpenAnyFile(pdfPath)
End Sub

Function openAnyFile(strPath As String)
Set objShell = CreateObject("Shell.Application")
objShell.Open(strPath)
End Function

https://stackoverflow.com/q/10380312/2727437 – Marcucciboy2 Nov 15 '18 at 05:18 — Marcucciboy2, Nov 15 '18 at 05:18

score 3 · Accepted Answer · answered Nov 15 '18 at 08:25

As pointed out in another answer, the Dir function with a wildcard should do the trick.

Here's an example using the original openAnyFile function.

Sub Open_PDF()
    Dim filePath As String, fileName As String

    filePath = "D:\Reports\"
    fileName = Dir(filePath & "Process Report*.pdf")

    If fileName <> "" Then
        openAnyFile filePath & fileName
    End If
End Sub

Function openAnyFile(strPath As String)
    Dim objShell As Object
    Set objShell = CreateObject("Shell.Application")
    objShell.Open (strPath)
End Function

score 2 · Answer 2 · edited Nov 15 '18 at 05:19

2

You cannot open a file using a wildcard - it's just common sense, what if more than one file was matching your criteria - which one would you want to program to open? You have to specify the exact file name to open it.

if there is just one file in the target directory, you can use something like the following code to open it, regardless of its name:

sFound = Dir(ActiveWorkbook.Path & "\Process Report*.xlsm") 

If sFound <> "" Then
    Workbooks.Open filename:= ActiveWorkbook.Path & "\" & sFound
End If

edited Nov 15 '18 at 05:19

Marcucciboy2

3,156
3
20
38

answered Nov 15 '18 at 04:32

Michal Rosa

2,459
2
15
28

I really appreciate your answer but my problem is that it is not an excel file,it is a PDF file.If it is an excel file I know how to open through wildcard but I do not know how it associates with PDF files. – Nilusha M. Nov 15 '18 at 04:59

How to open a .PDF file with wild card option via excel macro

2 Answers2