File Search and Select capability in Excel2000

[m_lny]

I would like to provide a button on my spreadsheet to allow the user to search down thru folders on the c drive to select a file somewhere on the computer. I'm not sure how to reference it, but it should work like "windows explorer" where you can click on a folder to open - then maybe a sub folder until you find the file you would like to select.
The purpose is to import a text file into excel without using a macro, and import text files with different names ( one at a time ).
I think I can mke it work after selecting the fle, but need help in making that selection possible. I have seen references to comdlg32 and msofilepicker but can't do anything with these.

any help will be appreciated

Thank you

 

[jindon]

try

Sub test()
Dim myDir As String, myFileName As String, x As String
myDir = "c:\temp"
myFileName = "myText.txt"
If Dir(myDir, vbDirectory) = "" Then
     MsgBox "Directory is not exist"
     Exit Sub
End If
x = SearchFile(myDir,myFileName)
If x <> "" Then
     ChDir x
     Application.Dialogs(1).Show
End If
End Sub

Function SearchFile(myDir As String, myFileName As String) As String
Dim fso As Object, myFile As Object, myFolder As Object
Set fso = CreateObject("Scripting.FileSystemObject")
For Each myFile In fso.GetFoler(myDir).Files
     If myFile.Name = myFileName Then
          SearchFile = myDir
          Exit Function
     End If
Next
For Each myFolder In fso.GetFolder(myDir).Subfolders
     SearchFile = SearchFile(myDir & "\" & myFolder.Name)
Next
End Function

 

[Norie]

Why not use GetOpenFileName?

 

[m_lny]

Thank you for replying !

I have tried:
fname = Application.GetOpenFilename
Workbooks.Open fname

This opens the selected file in a new workbook. I have an existing workbook and sheet that I would like to add the contents of the file to. I would like to insert the first data into "A3" and then on down thru the A column. The GetOpenFile inserts the data starting at cell A1.

I have searched the Excel help files and can't find anything on GetOpenFile. It does not show up in the index of the help files. I have tried different ways of using workbooks.open, activesheet.open, application.whatever.open, and can't find what to use to insert the data into the active worksheet starting at cell A3.

What I am trying to do is to use something like GetFileOpen to select a text file somewhere on my computer, then inserting the comma delimited data into column A starting at cell A3.

Again, any help would be greatly appreciated.
Thanks