Thanks Thanks:  0
Likes Likes:  0
Results 1 to 3 of 3

Thread: Input MessageBox / Msgbox to choose a location of a file

  1. #1
    Board Regular
    Join Date
    Feb 2002
    Location
    Houston, TX
    Posts
    359
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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!

  2. #2
    . MrExcel's Avatar
    Join Date
    Feb 2002
    Location
    Merritt Island Florida
    Posts
    864
    Post Thanks / Like
    Mentioned
    5 Post(s)
    Tagged
    0 Thread(s)

    Default

    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
    View a collection of recent Excel articles in the Excel Daily News

  3. #3
    Board Regular
    Join Date
    Feb 2002
    Location
    Houston, TX
    Posts
    359
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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 ]

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •