Input MessageBox / Msgbox to choose a location of a file

[Cosmos75]

I have a macro that I use that copies part of the active worksheet, opens an existing workbook, pastes the stuff copied. The newly opened workbook then does some calculations based upon what is pasted. Then it copies the results back into the original workbook.

Problem is the workbook that I open from the macro sometimes changes names or location. How do I create an input messagebox that promts the user to choose the location of the file?

Thanks!

[MrExcel]

Something like this:

On Error Goto NotFound
NewFN = Worksheets("Sheet1").Range("Z1").value
Workbooks.Open Filename:=NewFN
On Error GoTo 0
' The rest of your code
....
Exit Sub

NotFound:
    Msg = "OtherFile.xls not found in expected directory. Click OK to browse for this file, or click Cancel"
    ans = MsgBox(prompt:=Msg, Buttons:=vbOKCancel + vbDefaultButton1 + vbInformation, Title:="File not found")
    If ans = vbOK Then
        NewFN = Application.GetOpenFilename(fileFilter:="Excel Files (*.xls), *.xls", Title:="Browse for OtherFile.xls")
        If NewFN = False Then
            ' They clicked Cancel
            Exit Sub
        End If
        Workbooks.Open Filename:=NewFN
        Worksheets("Sheet1").Range("Z1").value = NewFn
        Resume Next
    End If
    Exit Sub

If you want to be nice to your users, you could then save that NewFN variable in a cell on the worksheet and have the Open use that next time. I just added in the logic to get the file name from Sheet1!Z1 to make that happen.

Bill

[Cosmos75]

WOW! A reply from MrExcel!!! Am honored that my question was worthy enough (or stupid enough).

Thanks!!

[ This Message was edited by: Cosmos75 on 2002-04-10 12:05 ]