Input MessageBox / Msgbox to choose a location of a file

Cosmos75

Active Member
Joined
Feb 28, 2002
Messages
359
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!
 

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.
Something like this:

Code:
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
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,532
Messages
6,114,176
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top