How to alter the code to find the file on users machine

[mayhem11]

I have an export code on my report that allows the end-user to select Excel or PDF. The Excel code never finds the file because users put the file anywhere they want in their machine and I want some code that will find that file on their machine. If the template does not actually exist, then they have to create it. I remember it was something like Environ & ....forgot...But here is what I have so far:

Dim mydlg As Office.FileDialog
Set mydlg = Application.FileDialog(msoFileDialogSaveAs)
Dim myfile As String
With mydlg
    .AllowMultiSelect = False
    '.Filters.Clear
    .InitialFileName = CurrentProject.path
If msgbox("Do you want to export to Excel?", _
vbQuestion + vbYesNo) = vbYes Then
    '.Filters.Add "Excel File", "*.xlsx;*.xls"
    If Dir(CurrentProject.path & "\frmQualityGraph.xlsx") = "" Then
        msgbox "Template excel file is not present in the current directory. So Export is not possible", vbOKOnly + vbInformation, "Excel"
        Set mydlg = Nothing
        Exit Sub
    End If
    
    If .Show = -1 Then
        myfile = .SelectedItems(1)
    Else
        msgbox "You must select a file to export", vbOKOnly + vbInformation, "Excel"
        Set mydlg = Nothing
        Exit Sub
    End If
    Dim myxls As New Excel.Application
    Set myxls = New Excel.Application
    Dim mybook As Excel.Workbook
    Set mybook = myxls.Workbooks.Open(CurrentProject.path & "\frmQualityGraph.xlsx")
    Dim mysheet As Excel.Worksheet
    Set mysheet = mybook.Worksheets(1)
    mysheet.Activate
    mysheet.Range("A2:T65000").Select
    myxls.Selection.ClearContents
    Dim k As Long, m As Long
    k = 2
    Dim myset As DAO.Recordset
    Set myset = CurrentDb.OpenRecordset("qryResult", dbOpenSnapshot)
    Dim myfld As DAO.Field
    With mysheet
    Do Until myset.EOF
       For m = 0 To myset.Fields.Count - 1
            .Cells(k, m + 1) = myset.Fields(m)
        Next
        .Rows(k).RowHeight = 18.75
        k = k + 1
        myset.MoveNext
    Loop
    End With
    If Dir(myfile) <> "" Then Kill myfile
    mybook.Sheets("PivotChart").Select
    mybook.ActiveSheet.ChartObjects("Chart 1").Activate
    mybook.ActiveSheet.PivotTables("PivotTable1").PivotCache.Refresh
    mybook.SaveAs myfile
    Set mysheet = Nothing
    myset.close
    Set myset = Nothing
    
    Set myfld = Nothing
    myxls.Visible = True
    Set myxls = Nothing
    
    
Else
    '.Filters.Add "PDF File", "*PDF"
    If .Show = -1 Then
        myfile = .SelectedItems(1)
    Else
        msgbox "You must select a file to export", vbOKOnly + vbInformation, "PDF"
        Set mydlg = Nothing
        Exit Sub
    End If
    
    DoCmd.OutputTo acOutputReport, "rptSearchQuality", acFormatPDF, myfile, True

End If
Set mydlg = Nothing
End With
[\code]

 

[ajs1952]

Sub FindMaster()
Dim wbResults As Workbook

With Application.FileSearch
.NewSearch
.LookIn = "C:\"
.FileType = msoFileTypeExcelWorkbooks
.Filename = "Master.xls"
If .Execute > 0 Then 'Workbooks in folder
Set wbResults = Workbooks.Open(Filename:=.FoundFiles(lCount), UpdateLinks:=0)
Else
MsgBox "Not found"
End If
End With
End Sub

Is this any use?