Use MsgBox to name selected file.

[Steeviee]

Hello gang.

I'm trying to write a code to grab data from files that others can update. The format is the same but they will have different names. I need to be able to run the my master file, but grab the data from this update file.

Now, I can do this using

application.GetOpenFilename

as below

MsgBox ("Find latest revenue file in a drive")
thatInt = Application.GetOpenFilename
Workbooks.Open thatInt, Password:="password", WriteResPassword:="password", ReadOnly:=True
that = ActiveWorkbook.Name

but was wanting to just have another workbook open and select that, name it and off I go...

Is there a similar function to the above?

Thanks in advance.

 

[Misca]

You need to get data from all the files in the folder or just the latest?

All files can be looped using VBA or using Power Query (="Get & Transform" as it's called in Excel 2016). I'd use the Power Query for it's easier and you can do pretty much anything with it.

If it's only the latest (=the file that was modified / saved most recently) you want you can do it using the the code found from

https://answers.microsoft.com/en-us...n-folder/a23cfed4-944a-e011-8dfc-68b599b31bf5

In case the link does not work here's the actual code:

[COLOR=#00007F][FONT=&quot]Sub[/FONT][/COLOR][COLOR=#000000][FONT=&quot] OpenLatestFile()[/FONT][/COLOR]

[COLOR=#000000][FONT=&quot]    [/FONT][/COLOR][COLOR=#007F00][FONT=&quot]'Declare the variables[/FONT][/COLOR]
[COLOR=#000000][FONT=&quot]    [/FONT][/COLOR][COLOR=#00007F][FONT=&quot]Dim[/FONT][/COLOR][COLOR=#000000][FONT=&quot] MyPath [/FONT][/COLOR][COLOR=#00007F][FONT=&quot]As[/FONT][/COLOR][COLOR=#000000][FONT=&quot] [/FONT][/COLOR][COLOR=#00007F][FONT=&quot]String[/FONT][/COLOR]
[COLOR=#000000][FONT=&quot]    [/FONT][/COLOR][COLOR=#00007F][FONT=&quot]Dim[/FONT][/COLOR][COLOR=#000000][FONT=&quot] MyFile [/FONT][/COLOR][COLOR=#00007F][FONT=&quot]As[/FONT][/COLOR][COLOR=#000000][FONT=&quot] [/FONT][/COLOR][COLOR=#00007F][FONT=&quot]String[/FONT][/COLOR]
[COLOR=#000000][FONT=&quot]    [/FONT][/COLOR][COLOR=#00007F][FONT=&quot]Dim[/FONT][/COLOR][COLOR=#000000][FONT=&quot] LatestFile [/FONT][/COLOR][COLOR=#00007F][FONT=&quot]As[/FONT][/COLOR][COLOR=#000000][FONT=&quot] [/FONT][/COLOR][COLOR=#00007F][FONT=&quot]String[/FONT][/COLOR]
[COLOR=#000000][FONT=&quot]    [/FONT][/COLOR][COLOR=#00007F][FONT=&quot]Dim[/FONT][/COLOR][COLOR=#000000][FONT=&quot] LatestDate [/FONT][/COLOR][COLOR=#00007F][FONT=&quot]As[/FONT][/COLOR][COLOR=#000000][FONT=&quot] Date[/FONT][/COLOR]
[COLOR=#000000][FONT=&quot]    [/FONT][/COLOR][COLOR=#00007F][FONT=&quot]Dim[/FONT][/COLOR][COLOR=#000000][FONT=&quot] LMD [/FONT][/COLOR][COLOR=#00007F][FONT=&quot]As[/FONT][/COLOR][COLOR=#000000][FONT=&quot] Date[/FONT][/COLOR]
[COLOR=#000000][FONT=&quot]    [/FONT][/COLOR]
[COLOR=#000000][FONT=&quot]    [/FONT][/COLOR][COLOR=#007F00][FONT=&quot]'Specify the path to the folder[/FONT][/COLOR]
[COLOR=#000000][FONT=&quot]    MyPath = "C:\Users\Domenic\[/FONT][/COLOR][COLOR=#00007F][FONT=&quot]Do[/FONT][/COLOR][COLOR=#000000][FONT=&quot]cuments\"[/FONT][/COLOR]
[COLOR=#000000][FONT=&quot]    [/FONT][/COLOR]
[COLOR=#000000][FONT=&quot]    [/FONT][/COLOR][COLOR=#007F00][FONT=&quot]'Make sure that the path ends in a backslash[/FONT][/COLOR]
[COLOR=#000000][FONT=&quot]    [/FONT][/COLOR][COLOR=#00007F][FONT=&quot]If[/FONT][/COLOR][COLOR=#000000][FONT=&quot] Right(MyPath, 1) <> "\" [/FONT][/COLOR][COLOR=#00007F][FONT=&quot]Then[/FONT][/COLOR][COLOR=#000000][FONT=&quot] MyPath = MyPath & "\"[/FONT][/COLOR]
[COLOR=#000000][FONT=&quot]    [/FONT][/COLOR]
[COLOR=#000000][FONT=&quot]    [/FONT][/COLOR][COLOR=#007F00][FONT=&quot]'Get the first Excel file from the folder[/FONT][/COLOR]
[COLOR=#000000][FONT=&quot]    MyFile = Dir(MyPath & "*.xls", vbNormal)[/FONT][/COLOR]
[COLOR=#000000][FONT=&quot]    [/FONT][/COLOR]
[COLOR=#000000][FONT=&quot]    [/FONT][/COLOR][COLOR=#007F00][FONT=&quot]'If no files were found, exit the sub[/FONT][/COLOR]
[COLOR=#000000][FONT=&quot]    [/FONT][/COLOR][COLOR=#00007F][FONT=&quot]If[/FONT][/COLOR][COLOR=#000000][FONT=&quot] Len(MyFile) = 0 [/FONT][/COLOR][COLOR=#00007F][FONT=&quot]Then[/FONT][/COLOR]
[COLOR=#000000][FONT=&quot]        MsgBox "No files were found...", vbExclamation[/FONT][/COLOR]
[COLOR=#000000][FONT=&quot]        [/FONT][/COLOR][COLOR=#00007F][FONT=&quot]Exit[/FONT][/COLOR][COLOR=#000000][FONT=&quot] [/FONT][/COLOR][COLOR=#00007F][FONT=&quot]Sub[/FONT][/COLOR]
[COLOR=#000000][FONT=&quot]    [/FONT][/COLOR][COLOR=#00007F][FONT=&quot]End[/FONT][/COLOR][COLOR=#000000][FONT=&quot] [/FONT][/COLOR][COLOR=#00007F][FONT=&quot]If[/FONT][/COLOR]
[COLOR=#000000][FONT=&quot]    [/FONT][/COLOR]
[COLOR=#000000][FONT=&quot]    [/FONT][/COLOR][COLOR=#007F00][FONT=&quot]'Loop through each Excel file in the folder[/FONT][/COLOR]
[COLOR=#000000][FONT=&quot]    Do [/FONT][/COLOR][COLOR=#00007F][FONT=&quot]While[/FONT][/COLOR][COLOR=#000000][FONT=&quot] Len(MyFile) > 0[/FONT][/COLOR]
[COLOR=#000000][FONT=&quot]    [/FONT][/COLOR]
[COLOR=#000000][FONT=&quot]        [/FONT][/COLOR][COLOR=#007F00][FONT=&quot]'Assign the date/time of the current file to a variable[/FONT][/COLOR]
[COLOR=#000000][FONT=&quot]        LMD = FileDateTime(MyPath & MyFile)[/FONT][/COLOR]
[COLOR=#000000][FONT=&quot]        [/FONT][/COLOR]
[COLOR=#000000][FONT=&quot]        [/FONT][/COLOR][COLOR=#007F00][FONT=&quot]'If the date/time of the current file is greater than the latest[/FONT][/COLOR]
[COLOR=#000000][FONT=&quot]        [/FONT][/COLOR][COLOR=#007F00][FONT=&quot]'recorded date, assign its filename and date/time to variables[/FONT][/COLOR]
[COLOR=#000000][FONT=&quot]        [/FONT][/COLOR][COLOR=#00007F][FONT=&quot]If[/FONT][/COLOR][COLOR=#000000][FONT=&quot] LMD > LatestDate [/FONT][/COLOR][COLOR=#00007F][FONT=&quot]Then[/FONT][/COLOR]
[COLOR=#000000][FONT=&quot]            LatestFile = MyFile[/FONT][/COLOR]
[COLOR=#000000][FONT=&quot]            LatestDate = LMD[/FONT][/COLOR]
[COLOR=#000000][FONT=&quot]        [/FONT][/COLOR][COLOR=#00007F][FONT=&quot]End[/FONT][/COLOR][COLOR=#000000][FONT=&quot] [/FONT][/COLOR][COLOR=#00007F][FONT=&quot]If[/FONT][/COLOR]
[COLOR=#000000][FONT=&quot]        [/FONT][/COLOR]
[COLOR=#000000][FONT=&quot]        [/FONT][/COLOR][COLOR=#007F00][FONT=&quot]'Get the next Excel file from the folder[/FONT][/COLOR]
[COLOR=#000000][FONT=&quot]        MyFile = Dir[/FONT][/COLOR]
[COLOR=#000000][FONT=&quot]        [/FONT][/COLOR]
[COLOR=#000000][FONT=&quot]    [/FONT][/COLOR][COLOR=#00007F][FONT=&quot]Loop[/FONT][/COLOR]
[COLOR=#000000][FONT=&quot]    [/FONT][/COLOR]
[COLOR=#000000][FONT=&quot]    [/FONT][/COLOR][COLOR=#007F00][FONT=&quot]'Open the latest file[/FONT][/COLOR]
[COLOR=#000000][FONT=&quot]    Workbooks.Open MyPath & LatestFile[/FONT][/COLOR]
[COLOR=#000000][FONT=&quot]        [/FONT][/COLOR]
[COLOR=#00007F][FONT=&quot]End[/FONT][/COLOR][COLOR=#000000][FONT=&quot] [/FONT][/COLOR][COLOR=#00007F][FONT=&quot]Sub[/FONT][/COLOR]

 

[Steeviee]

Thank you Misca for your response.

I have not explained myself well enough...I'm not trying to open a file at all.

I want to have my master file open AND the update file open. From the code on the Master, I'd like to do something (MsgBox?) to allow me to select the update file (assign it a name) and carry on through the macro.

I can do this by opening a file, but I wondered if I could do it without opening it, just be activating it.

 

[Misca]

Sure you can make the Msgbox loop through the files and ask if this is the one you're looking for. But if the last modified -timestamp can be used as a guide you can tweak the code above to just find the correct file. If you can't use the name ("starts with", "contains" or whatever) or the modified date to help the macro spot out the correct file automatically, your current code might be about the best I can think of. The current code does open the file but comment the last line of the code and you're left with the file name. After that you can do pretty much whatever you want with it.

You can use the buttons in the msgbox to return boolean type answers to your questions but that's about as complex as it gets with that. If you want to be able to do more than that, you might want to come up with your own form. Or maybe you want to think about InputBox before that.

 

[Steeviee]

These are very good points - thank you.
I'll see what I can do with the suggestions.

Thank you.

 

[GTO]

...I'm not trying to open a file at all.

I want to have my master file open AND the update file open. From the code on the Master, I'd like to do something (MsgBox?) to allow me to select the update file (assign it a name) and carry on through the macro....

Presuming you only have one or two workbooks (in addition to the 'Master' workbook) open, a MsgBox would seem reasonable; maybe something like:

In a Standard Module:

Option Explicit
  
Sub example()
Dim WB As Workbook
Dim sWBName As String


  For Each WB In Application.Workbooks
    If Not WB.Name = ThisWorkbook.Name Then
      Select Case MsgBox("Run code against: '" & WB.Name & "'?" & vbLf & vbLf & _
                         "<Yes> to run code," & vbLf & _
                         "<No> to look at next open workbook," & vbLf & _
                         "<Cancel> to cancel", _
                           vbYesNoCancel Or vbQuestion, _
                           vbNullString)
                           
      Case vbYes:     sWBName = WB.Name
                      Stuff2do sWBName
                      Exit For 'or, exit sub?
                  
      Case vbNo:      'nothing needed
      
      Case vbCancel:  Exit For '...
      
      End Select
    End If
  Next
  
End Sub
  
Private Sub Stuff2do(WBName As String)
Dim WB As Workbook
  
  Set WB = Application.Workbooks(WBName)
  MsgBox "Do stuff with " & WB.Name
  Set WB = Nothing
  
End Sub

Hope that helps,

Mark