+ Reply to Thread
Results 1 to 10 of 10

quick help: get folder name

  1. #1
    yo
    Guest

    quick help: get folder name

    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


  2. #2
    Bob Phillips
    Guest

    Re: quick help: get folder name

    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" <[email protected]> wrote in message
    news:[email protected]...
    > 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
    >




  3. #3
    papou
    Guest

    Re: quick help: get folder name

    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" <[email protected]> a écrit dans le message de news:
    [email protected]...
    > 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
    >




  4. #4
    yo
    Guest

    Re: quick help: get folder name

    >>
    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


  5. #5
    Bob Phillips
    Guest

    Re: quick help: get folder name

    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" <[email protected]> wrote in message
    news:[email protected]...
    > >>

    > 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
    >




  6. #6
    yo
    Guest

    Re: quick help: get folder name

    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?


  7. #7
    Bob Phillips
    Guest

    Re: quick help: get folder name

    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" <[email protected]> wrote in message
    news:[email protected]...
    > 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?
    >




  8. #8
    yo
    Guest

    Re: quick help: get folder name

    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


  9. #9
    Bob Phillips
    Guest

    Re: quick help: get folder name

    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" <[email protected]> wrote in message
    news:[email protected]...
    > 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
    >




  10. #10
    yo
    Guest

    Re: quick help: get folder name

    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 Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1