quick help: get folder name

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

yo - 20 May 2006 11:24 GMT

hi, i'm a n00b on vba, can somebody tell me how to get (extract) the
folder name from ActiveWorkbook.Path? i read about InStr() &
InStrRev(), but i don't know how to use the result from InStr (as i
find below), and InStrRev i can't make sense of (returns 10 from
comparing "D:\BACKUP\myjobprices\tmp" with "\", and 0 if starts at 1)

so i got a macro from this group, but instead of extract the folder
name, it returns the root & parent folder name/path. so instead of
"tmp", i got "D:\BACKUP\myjobprices"

here's where i got the function:
http://groups.google.com/group/microsoft.public.excel.programming/browse_frm/thr
ead/af44fad6da8d57d9/ff4f416e2bef2ee8?rnum=53#ff4f416e2bef2ee8

any help will be great. i may try another route, since there's a date
entry inside the file, which will be combined with another data
(job/project name) for duplicating the file inside a new folder name.
but i want to exhaust this option first. thanks

Reply to this Message

Bob Phillips - 20 May 2006 12:22 GMT

With ActiveWorkbook
iPos = InStrRev(.Path, "\")
If iPos > 1 Then
sFolder = Right(.Path, Len(.Path) - iPos)
Else
iPos = InStrRev(.Path, ":")
End If
If iPos > 1 Then sFolder = Right(.Path, Len(.Path) - iPos)
End With

MsgBox sFolder

Signature

HTH

Bob Phillips

(remove xxx from email address if mailing direct)

> hi, i'm a n00b on vba, can somebody tell me how to get (extract) the
> folder name from ActiveWorkbook.Path? i read about InStr() &
[quoted text clipped - 7 lines]
>
> here's where i got the function:

http://groups.google.com/group/microsoft.public.excel.programming/browse_frm/thr
ead/af44fad6da8d57d9/ff4f416e2bef2ee8?rnum=53#ff4f416e2bef2ee8

> any help will be great. i may try another route, since there's a date
> entry inside the file, which will be combined with another data
> (job/project name) for duplicating the file inside a new folder name.
> but i want to exhaust this option first. thanks

Reply to this Message

papou - 20 May 2006 16:22 GMT

Hello
You may also use the FileSystemObject GetBaseName method:
Add a reference (Tools Reference in VB Editor) to Microsoft Scripting
Runtime in your project

Dim fso As FileSystemObject
Set fso = New FileSystemObject
MsgBox fso.GetBaseName(ActiveWorkbook.Path)

HTH
Cordially
Pascal

> hi, i'm a n00b on vba, can somebody tell me how to get (extract) the
> folder name from ActiveWorkbook.Path? i read about InStr() &
[quoted text clipped - 13 lines]
> (job/project name) for duplicating the file inside a new folder name.
> but i want to exhaust this option first. thanks

Reply to this Message

yo - 22 May 2006 05:43 GMT

With ActiveWorkbook
iPos = InStrRev(.Path, "\")
--> If iPos > 1 Then
sFolder = Right(.Path, Len(.Path) - iPos)
Else
iPos = InStrRev(.Path, ":")
--> End If
If iPos > 1 Then sFolder = Right(.Path, Len(.Path) - iPos)
End With

MsgBox sFolder

--
HTH

Bob Phillips

Hi Bob, it's working great, but i don't understand, why 2 instances of
sFolder = Right(.Path, Len(.Path) - iPos)? i took out the 1st
if...then, and it still works. can you elaborate further on this?

Papou: it's working too, and it's simple! but if someone else use this
macro on other computers, will it still works? will they have to enable
this ms scripting runtime option?

Thanks guys

Reply to this Message

Bob Phillips - 22 May 2006 08:55 GMT

The reason there are two is in case you get a path like C:\Program
Files\Office\Excel.exe, or another like C:\Jim.xls. Papou's solution uses
FileSystemObject, and whilst it should work in most instances, I do know
that some organisations don't allow scripting, so it is possible that it
wouldn't work.

Signature

HTH

Bob Phillips

(remove xxx from email address if mailing direct)

> With ActiveWorkbook
> iPos = InStrRev(.Path, "\")
[quoted text clipped - 22 lines]
>
> Thanks guys

Reply to this Message

yo - 22 May 2006 12:26 GMT

I see, that's quick and awesome :D. thanks Bob, God knows how many more
hours i have to spend on browsing for this if i didn't post this
thread. But of course if some corp won't allow MS Scripting Runtime,
they won't allow VB too, will they?

Reply to this Message

Bob Phillips - 22 May 2006 12:30 GMT

Not necessarily, scripting is viewed as a bigger security threat than VB/VBA
by some organisations. Of course some may hold that view, but it is a
straight equivalent.

Signature

HTH

Bob Phillips

(remove xxx from email address if mailing direct)

> I see, that's quick and awesome :D. thanks Bob, God knows how many more
> hours i have to spend on browsing for this if i didn't post this
> thread. But of course if some corp won't allow MS Scripting Runtime,
> they won't allow VB too, will they?

Reply to this Message

yo - 22 May 2006 13:27 GMT

sorry to bother you again, can you help me with this?
i run into error (Object required) while trying to do it like below:

Function GetFolderName(bookPath As String) As String
'
' GetFolderName(bookPath) Function Macro
' find workbook folder name - 22/05/2006 18:46
'
Dim i As Integer

Set i = InStrRev(bookPath, "\")
If i > 1 Then
' if the path incl. file name(??)
folderName = Right(bookPath, Len(bookPath) - i)
Else
' in case it's in the root folder
i = InStrRev(bookPath, ":")
End If
If iPos > 1 Then
' get the folder name
folderName = Right(bookPath, Len(bookPath) - i)
End If

End Function

Sub exportSheetNewBook()
'
' exportSheetNewWorkbook Subroutine Macro
' export each sheet to new workbook - 22/05/2006 18:54
'
' Keyboard Shortcut: Ctrl+Shift+e
'
Dim srcBook As Workbook
Dim newBook As Workbook
Dim fdPath As String
Dim fdName As String
Dim sh As Worksheet
Dim shName As String

Set srcBook = ThisWorkbook
Set sh = srcBook.ActiveSheet
Set fdPath = srcBook.Path ' << this is where it threw the error
Set fdName = GetFolderName(fdPath)

For Each sh In srcBook.Worksheets
sh.Copy

Set newBook = ActiveWorkbook
newBook.SaveAs (fdName & "\" & newBook.ActiveSheet.Name & "_" &
fdName & ".xls")

newBook.Close
Next sh

End Sub

Reply to this Message

Bob Phillips - 22 May 2006 17:21 GMT

You don't use Set for non-object variables.

Function GetFolderName(bookPath As String) As String
'
' GetFolderName(bookPath) Function Macro
' find workbook folder name - 22/05/2006 18:46
'
Dim i As Integer

i = InStrRev(bookPath, "\")
If i > 1 Then
' if the path incl. file name(??)
folderName = Right(bookPath, Len(bookPath) - i)
Else
' in case it's in the root folder
i = InStrRev(bookPath, ":")
End If
If iPos > 1 Then
' get the folder name
folderName = Right(bookPath, Len(bookPath) - i)
End If

End Function

Sub exportSheetNewBook()
'
' exportSheetNewWorkbook Subroutine Macro
' export each sheet to new workbook - 22/05/2006 18:54
'
' Keyboard Shortcut: Ctrl+Shift+e
'
Dim srcBook As Workbook
Dim newBook As Workbook
Dim fdPath As String
Dim fdName As String
Dim sh As Worksheet
Dim shName As String

Set srcBook = ThisWorkbook
Set sh = srcBook.ActiveSheet
fdPath = srcBook.Path
fdName = GetFolderName(fdPath)

For Each sh In srcBook.Worksheets
sh.Copy

Set newBook = ActiveWorkbook
newBook.SaveAs (fdName & "\" & newBook.ActiveSheet.Name & "_" &
fdName & ".xls")

newBook.Close
Next sh

End Sub

Signature

HTH

Bob Phillips

(remove xxx from email address if mailing direct)

> sorry to bother you again, can you help me with this?
> i run into error (Object required) while trying to do it like below:
[quoted text clipped - 51 lines]
>
> End Sub

Reply to this Message

yo - 23 May 2006 07:34 GMT

oh, so that's why those errors keep coming up...

thanks for everything, Bob ... i wanted to ask you about that var:
folderName, but i found out already, change to match Function name &
voila, it returns the value. i just don't understand why, lol. it's
different from javascript & actionscript, that's for sure

Reply to this Message