Excel VBA get file path.

Question

Hi Excel Gurus

The code below is from a macro that opens a workbook (path in cell Y2) moves worksheet to that worksheet. 

That works very well if you always run it from one computer.  I would like to change the macro so that the first part of the path with a path (up to the folded) picked with the file picker and add the file name in cell A2 of the sheet.

 Set wbkSrc = ActiveWorkbook

    For i = wbkSrc.Worksheets.Count To 11 Step -1

        Set wsh = wbkSrc.Worksheets(i)

        strFile = wsh.Range("y2").Value

        Set wbkTrg = Workbooks.Open(strFile)

Thanks

Es

[ Moved from: Office / Excel / Windows other / Office 365 Home ]

Answer 1

This page has what you need:

https://wellsr.com/vba/2016/excel/vba-select-folder-with-msoFileDialogFolderPicker/

Answer 2

Thank you that was very helpful.