Button to Prompt for Filename to be Used

[Excel_Fan]

So I wrote all my macro and it works perfectly but I have a little quirk I regarding Filenames. The marco reference a file that follows this naming scheme "fname-09-08-15". So it's first name-year-month-day. This file is received every mid and end cycle.

I would like to be able to pass that file name as an parameter to the macro so the i dont have to go in and do a manual change everytime. I have a button on my worksheet that runs the macro.

My question is: is there a way so that when i click the button, the user is prompted to input the filename or perhaps browse for it so it can be used? That way i can use the input of the button perhaps as input to the marco...?!?! (my macro only works when both files are open at the same time under one instance of excel - not sure why but didnt bother).

If there is a better way, please feel free to suggest. This is my 3rd day coding in VB and I've done a lot so far. Thanks!

[JBeaucaire]

A little sub to demonstrate how you can browse to select a file and find out the path AND the file name chosen in on go:

Please Login or Register  to view this content.

[Excel_Fan]

Thanks for that. i tried playing around with it for a while but didn't get anywhere. I think I lack some basics in VB?

I'll try to give some more info and some Pseudocode. Below is how my Sub starts. The Workbook containing the data I need to pull will be called WB-09-08-15.xls in what follows.

Please Login or Register  to view this content.

Since the date changes every 15 days (the next being 09-08-30), I would like to be able to perhaps pass the filename as a parameter to the main sub.
So i would have

Please Login or Register  to view this content.

The tricky part is the sub runs by clicking a button that is in my worksheet.
So ideally I would like to be able to
1- Click the button.
2- Be prompted to enter the FileName or browse for it.
3- Have that Filename passed as a parameter.

[Excel_Fan]

bump in case anyone can follow up.


Thanks!