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/micro...4f416e2bef2ee8

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

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

(remove xxx from email address if mailing direct)

"yo" wrote in message
oups.com...
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/micro...4f416e2bef2ee8

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

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

"yo" a écrit dans le message de news:
...
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/micro...4f416e2bef2ee8

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

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

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.

--
HTH

Bob Phillips

(remove xxx from email address if mailing direct)

"yo" wrote in message
oups.com...

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

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?

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.

--
HTH

Bob Phillips

(remove xxx from email address if mailing direct)

"yo" wrote in message
ups.com...
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?

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

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


--
HTH

Bob Phillips

(remove xxx from email address if mailing direct)

"yo" wrote in message
oups.com...
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

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