Excel programming ?

[buddyel]

I would like to create a button on an excel spreadsheet that when clicked, opens a form that prompts the user for a filename and searches a specified folder for that filename and opens the file. I was wondering if anyone could tell me I could go about creating this.

 

[segmentationfault]

I don't mean to be flippant, but isn't this what the open file button on the general toolbar does? Did you have something else in mind? Do you want to customize the behavior of the open file button?

 

[buddyel]

We have an excel spreadsheet that we use as a template and the the spreadsheet is then saved under a correspoding part number on a network folder. The path is kind of long and hard to remember for people who seldom use the template, so i just want to make it easier to open an existing file by creating an small app to search for the file and open it for them.

 

[SkipVought]

Hi,

I think that what you want is the GetSaveAsFileName Method. It opens a window that the user can navigate the directory tree to zero in on the correct file. The resulting name can be used to identify a file to open. The path can be specified in your code...

    fileSaveName = Application.GetSaveAsFilename( _
        InitialFileName:=MyPathAndFile, _
        fileFilter:="Excel Files (*.xls), *.xls")
    If fileSaveName <> False Then
        MsgBox &quot;Open &quot; & fileSaveName
    End If

Hope this helps Skip,
SkipAndMary1017@mindspring.com

 

[buddyel]

I figured out what i needed:

Private Sub CommandButton1_Click()
With Application.FileSearch
.LookIn = "\\server\path\folder
.SearchSubFolders = True
.Filename = TextBox1.Value
.FileType = msoFileTypeAllFiles
If .Execute > 0 Then
For i = 1 To .FoundFiles.Count
MsgBox .FoundFiles(i), vbOKCancel
Workbooks.Open .FoundFiles(i)
Next i
Else
MsgBox "File Not Found"
End If
End With
End Sub