VBA to prompt user to select two xlsm files

[MikeL]

Hello,
I would like to prompt the user to first select one .XLSM file. I have the code to then filter and paste to a Consolidation.XLSM

Then, I would like the user to be prompted to select a second .XLSM file.

Key VBA Needs:
User prompt to Select file (all files are in A:/Shared but might be in different subfolders)
Open Consolidation.XLSM After WB1 is open
Close WB1 and prompt user to open WB2.

Thanks,
Mike

 

[laughingdevil]

Somthing I've got in my current project, though its a bit lenghty

Set fDialog = Application.FileDialog(msoFileDialogFilePicker)
With fDialog
'Don't allow user to make multiple selections in dialog box
.AllowMultiSelect = True

.InitialFileName = strInitPath

'Set the title of the dialog box.
.Title = "Please select xml file"

'Clear out the current filters, and add our own.
.Filters.Clear
.Filters.Add "XLS or XLSX Files", "*.xls, *.xlsx"

'Show the dialog box. If the .Show method returns True, the
'user picked at least one file. If the .Show method returns
'False, the user clicked Cancel.
If .Show = True Then
For Each varFile In .SelectedItems
DoCmd.Hourglass True
LoadXLSFile varFile
SaveXLSFile varFile
strFiles = strFiles & varFile & vbCrLf
DoCmd.Hourglass False
Next
Else
MsgBox "You clicked Cancel in the file dialog box."
Exit Sub
End If
End With

I'm pretty sure with some tweaking that could fill your needs, unless someone has somthing simpler

 

[MikeL]

thanks for the response LD. I will work with that code.

 

[laughingdevil]

thanks for the response LD. I will work with that code.

NP, hopefully it'll work, you should be able to get rid of some stuff if you need, the msg for example gets annoying (and wasn't coded by me), the hourglass stuff is only if you want it (it can take a while to load the file!)

I should mention the below

LoadXLSFile varFile
SaveXLSFile varFile

refer to my own functions that "do" what I want to do with the file (in this case stick the data into a database

and

strFiles = strFiles & varFile & vbCrLf

is not needed for you at all (I had some more lines that use this var to report back whats been done.