Link to home
Start Free TrialLog in
Avatar of jasocke2
jasocke2Flag for United Kingdom of Great Britain and Northern Ireland

asked on

Got folder name from path, before filename (VBA)

Hi,

This should be quite a simple one.

If I have a variable (mypath) which is set to C:/Folder1/folder2/file.xls in Excel VBA, what I want to do is extract the folder name before the file.xls which would be 'folder2'.

What VBA function could I use to get this and assign it to the variable 'MyFolder'?

Thanks,
Avatar of Patrick Matthews
Patrick Matthews
Flag of United States of America image

Hello jasocke2,

Split the path into an array, and take the next to last member of the array:

MsgBox Split(mypath, "\")(UBound(Split(mypath, "\") - 1)

Regards,

Patrick
ASKER CERTIFIED SOLUTION
Avatar of jph572
jph572

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of jasocke2

ASKER

Spot on thanks :)