VBA; relative path; using OPEN <filename> for Input As #1

I want to open the TNSNAMES.ORA file for input and write its contents to a table.  I can do this if I supply full path (e.g. C:\Oracle92\network\ADMIN\tnsnames.ora) as "filename".  Since I don't always know the exact path, how would I construct the open statement (or the variable "filename") so that it would open the file wherever it is on the PC?
Who is Participating?
GrahamSkanConnect With a Mentor RetiredCommented:
You can use the built-in FileSearch Object:

Sub FindFile()
    Dim i As Integer
    With Application.FileSearch
        .LookIn = "C:\"
        .SearchSubFolders = True
        .Filename = "TNSNAMES.ORA"
        .MatchTextExactly = True
            If .Execute() > 0 Then
            MsgBox "There were " & .FoundFiles.Count & _
                " file(s) found."
            For i = 1 To .FoundFiles.Count
                MsgBox .FoundFiles(i)
            Next i
            MsgBox "There were no files found."
        End If
    End With
End Sub
you could have a file selector box (Common Dialog) and have the user manually select the file.  Then use that filename from the file selector as your TNSNAMES.ORA file.

Or are you looking for the program to search the harddrive and look for the file?
ShelnuttAuthor Commented:

Thanks for the quick response.  I am looking for the program to search the harddrive and look for the file.  Users would be totally lost as to where to even begin looking.
Cloud Class® Course: Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

What application is the VBA in?
you can do a recursive file search by extension.  The code is provided at the link.  You can alter the code to search for the entire filename.  

How are you going to handle multiple filenames on a single disk drive?
here is some code for recursive file searching in VB 6:

ShelnuttAuthor Commented:
>What application is the VBA in?
Thanks, I am using MSAccess.
Code at the link looks like more than I need but maybe not!  
>How are you going to handle multiple filenames on a single disk drive?
Plan is to err out if more than one found.
the second link is probably the best link for you.  VB 6 is basically the same as Access VBA.
ShelnuttAuthor Commented:
Thanks to everyone who responded.  The last answer from "GrahamSkan" is the one I needed.  I believe I will be able to complete what I started with that.

Please award points to Graham.
Thanks. Good luck with the rest of your project.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.