Create a link to another workbook via form control

[CaptainGravyBum]

Hello,
I have a user who has created a workbook for each week number of the year and would like portions of the data in that workbook to be populated from another workbook that has already been created. Both workbooks will change name each week, which makes it hard to link them, so I have played with the idea of using the data in a cell to specify which workbook to open before copying the data across, but it could be hard work because the source folder will change as well.
I'm now thinking that it may be easier to have a 'browse' button at the top of the destination workbook so the user can simply browse to the required file and create a workbook link.

Sorry, I'm struggling to find a logical way to explain what I need.

Can anyone decipher my needs and suggest a solution? Or a completely different way to accomplish this that I haven't thought of.

Thanks,

 

[Micron]

I suggest that your button should run code that opens the msoFileDialogFilePicker. I think Excel has another method named GetOpenFileName but I'm not too familiar with it. AFAIK, either one should return the complete path. The Excel version might have file name as a property but I can't recall. The picker doesn't so you get the filename by extracting what comes after the last "\". I would have to look at old code in both apps to verify, but you could Google both methods and see what you think.

With the picker, you can start with any valid folder name (the property is called InitialFileName) which might come in handy if folders are being added or moved around slightly. You might even put the last starting point in a helper cell if it turns out you'll always want to start from there. Sorry I can't be more explicit at the moment.