Reference the ParentFolder in a cell formula

[davedave24]

Hi guys. How would I reference the parent folder (of a workbook) in a cell reference, in code?

Currently I have this:

This goes to the All Deliveries 2011 (or whatever current year) workbook, goes to a specific row, then references the value in column G.

.Range("H13").Formula = "=SUM('[All Deliveries " & Year(Date) & ".xlsx]REVENUE'!$G$" & RowNumber & "/20)"

If however I move this workbook into a new folder, how would I reference the parent folder? i.e.

All Deliveries 2011.xlsx is in the parent folder
Workbook in question (where the cell reference is) is in a subfolder.

I'm looking for something like

.Range("H13").Formula = "=SUM('[[b]Thisworkbook.parentfolder\[/b]All Deliveries " & Year(Date) & ".xlsx]REVENUE'!$G$" & RowNumber & "/20)"

 

[xlbo]

Set a reference to Microsoft Scripting Runtime and then:

Dim fso As FileSystemObject

Dim ParentFolder As String

Set fso = New FileSystemObject

ParentFolder = fso.GetParentFolderName(ThisWorkbook.Path)

will give you what you need

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[Gavona]

if others need to use the workbook who might not have that reference set then you could construct a formula manipulating the results of Cell("filename") and if totally avoiding VBA using the Indirect function.

Regards,

Gavin

 

[N1GHTEYES]

If you want to put a reference into a cell such that it returns the workbook's path, you could use a user-defined function such as MyPath where:

Function MyPath() as string
MyPath = Application.Caller.Worksheet.Parent.Path
End function

Does that help?

Tony