So I have been learning some VBA recently and noticed that I could save myself hours a day if I was able to figure out how to write this:
I have the test sheet below before I implement this at work and would like to use the "Full Folder Path" and paste that into a closed excel file. I know understand indirect can't be used for this since the work book is closed but my attempts at trying to write a macro for this doesn't seem to work (or a function for that matter). I'm using $A$1 as a reference but I will be changing that once I find what cell the data is in at work.
Would 100% appreciate any help here since I could be saving myself hours at my job pulling this data with this instead of manually opened 10-20 of these files a day to grab data.
I have the test sheet below before I implement this at work and would like to use the "Full Folder Path" and paste that into a closed excel file. I know understand indirect can't be used for this since the work book is closed but my attempts at trying to write a macro for this doesn't seem to work (or a function for that matter). I'm using $A$1 as a reference but I will be changing that once I find what cell the data is in at work.
Would 100% appreciate any help here since I could be saving myself hours at my job pulling this data with this instead of manually opened 10-20 of these files a day to grab data.
| Name | Folder Path | Cell Reference | Desired Info Inside The Cell? | Desired Output |
| Sales Documentation Sheet.xlsm | C:\Users\User\Desktop\VBA\ | $A$1 | C:\Users\User\Desktop\VBA\Sales Documentation Sheet.xlsm!$A$1 | Link to data in the cell based on other columns |
| Sales.xlsm | C:\Users\User\Desktop\VBA\ | $A$1 | C:\Users\User\Desktop\VBA\Sales.xlsm!$A$1 | Link to data in the cell based on other columns |
