CODEWITHSUNDEEP
excelvba
Leo
Leo

Reputation: 25

Get file path from excels cell

I need help how to get the file path from excel woorbook. Path is save it in Cell C3 in excel sheet1.

And I want it with my macro code to get that particular value from Cell C3 that I have in excel and set it to S1.

example: Now my code work like this: ' Set s1 = Workbooks.Open(Filename:="C:\Users\25012023\master_NEW.xlsx") But I don't wanna to fix that path name here in the module. I wanted to be more dynamically.

I save it that path in excel cell , but now I don't know how to get the value from there. I tried with this? Set s1 = ThisWorkbook.Worksheets("Sheet1").Range("C3").Value But without any success.

Suggestions?

Upvotes: 0

Views: 85

Answers (1)

Dolunaykiz
Dolunaykiz

Reputation: 333

Not sure if this is what you are looking for, but this formula will produce a dynamic file path/name in Excel. Simply enter this formula into cell A1 (substitute that cell address for another of your choosing:

=LEFT(CELL("filename",$A$1),FIND("[",CELL("filename",$A$1),1)-1)

I use this frequently when I need a dynamic way of getting the file name and path for Power Query for files that are on SharePoint.

Upvotes: 0

Related Questions