CODEWITHSUNDEEP
excelvba
b_dawg
b_dawg

Reputation: 23

Paste the name of the folder a sheet is contained in

I have a sheet which is the active sheet, and I would like to paste the name of the folder the excel sheet is contained in into cells in Excel. Is there a way to do this and if so how?

ie. how can I get the folder that the activeSheet is in? Is there a function called active folder or something?

Upvotes: 0

Views: 42

Answers (1)

Robert Mearns
Robert Mearns

Reputation: 11986

From code

Use ActiveWorkbook.Path. If the active workbook has not been saved, an empty string will be returned.

Sub FindFolder()

Dim strFolder As String

    strFolder = ActiveWorkbook.Path

    ActiveWorkbook.ActiveSheet.Range("A1").Value = strFolder

End Sub

From Formula

Use the CELL function to get the full path, file name and sheet name. Then extract the folder using LEFT and FIND functions. If the active workbook has not been saved, a #VALUE! will be returned.

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

Upvotes: 1

Related Questions