Page 1 of 2 12 LastLast
Results 1 to 15 of 19
  1. #1
    Lounger
    Join Date
    Apr 2002
    Location
    Los Angeles, California, USA
    Posts
    36
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Set Directory with msgBox (97sr2)

    In searching the archives, I can't seem to find any code on setting a target directory with a msgBox. <img src=/S/scratch.gif border=0 alt=scratch width=25 height=29>

    I've got this:
    Dim strPathAndFile As String
    strPathAndFile = Application.GetOpenFilename

    Which returns "C:windowsdesktopprojecttestdatafilename.xls

    But I can't find the code to chop off the "filename.xls" leaving the directory path.

    What I'm looking for is:

    strDirPath = {magic applied to (strPathAndFile)}

    Links or code anyone? <img src=/S/confused.gif border=0 alt=confused width=15 height=20>

    Thanks in advance.

  2. #2
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Kortrijk, Belgium
    Posts
    571
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Set Directory with msgBox (97sr2)

    <pre>Sub FileNameOnly()
    Dim CompleteName As String
    CompleteName = Application.GetOpenFilename
    MsgBox StripOffFileName(CompleteName)
    End Sub
    </pre>


    <pre>Private Function StripOffFileName(CName As String) As String
    Dim i As Integer
    Dim Length As Integer
    Dim dummy As String
    Length = Len(CName)
    dummy = ""
    For i = Length To 1 Step -1
    If Mid(CName, i, 1) = Application.PathSeparator Then
    StripOffFileName = dummy
    Exit Function
    End If
    dummy = Mid(CName, i, 1) & dummy
    Next i
    StripOffFileName = CName
    End Function
    </pre>


  3. #3
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Vienna, Wien, Austria
    Posts
    5,009
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Set Directory with msgBox (97sr2)

    <pre></pre>

    Try this:

    Dim strPath As String
    strPath = Left(ActiveWorkbook.FullName, Len(ActiveWorkbook.FullName) - Len(ActiveWorkbook.Name))
    MsgBox strPath

    HTH
    Gre

  4. #4
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Set Directory with msgBox (97sr2)

    Try this:

    <pre>Dim strPathAndFile As String, strPath As String
    strPathAndFile = Application.GetOpenFilename
    strPath = Left(strPathAndFile, InStrRev(strPathAndFile, ""))
    </pre>

    Legare Coleman

  5. #5
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Set Directory with msgBox (97sr2)

    That will work only on the active workbook name, not on a name that was gotten with GetOpenFilename, if the active workbook has not been saved with that name.
    Legare Coleman

  6. #6
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Vienna, Wien, Austria
    Posts
    5,009
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Set Directory with msgBox (97sr2)

    I thought of this also, but wasn't sure whether InStr works with 97. Does it?
    Gre

  7. #7
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Vienna, Wien, Austria
    Posts
    5,009
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Set Directory with msgBox (97sr2)

    I know this but, on the basis of the poster's project, I understood he was working with a series of already saved files. Also, the return from his posted code indicated an already saved file.
    Gre

  8. #8
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Set Directory with msgBox (97sr2)

    I just read his post again, and I don't see anything that indicated that the workbook has been saved with the name.
    Legare Coleman

  9. #9
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Set Directory with msgBox (97sr2)

    InstrRev is an Excel 2000 function and not 97
    Francois

  10. #10
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Vienna, Wien, Austria
    Posts
    5,009
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Set Directory with msgBox (97sr2)

    If the file had not already been saved, it would not have had the *.xls extension.
    Gre

  11. #11
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Set Directory with msgBox (97sr2)

    I don't know, and I don't have 97 to check. If it doesn't, then this will work:

    <pre>Dim strPathAndFile As String, strPath As String
    Dim I As Integer, iLast As Integer
    strPathAndFile = Application.GetOpenFilename
    For I = Len(strPathAndFile) To 0 Step -1
    If Mid(strPathAndFile, I, 1) = "" Then
    iLast = I
    Exit For
    End If
    Next I
    strPath = Left(strPathAndFile, iLast)
    </pre>

    Legare Coleman

  12. #12
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Set Directory with msgBox (97sr2)

    It would have if a .xls file was selected in the GetOpenFilename box or if the user typed .xls in.
    Legare Coleman

  13. #13
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Set Directory with msgBox (97sr2)

    Thanks, I have posted code that will work for 97 also then.
    Legare Coleman

  14. #14
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Vienna, Wien, Austria
    Posts
    5,009
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Set Directory with msgBox (97sr2)

    The code in question is:
    <hr> Dim strPathAndFile As String
    strPathAndFile = Application.GetOpenFilename

    Which returns "C:windowsdesktopprojecttestdatafilename.xls<h r>
    In the Debug window, on an unsaved file, I get the following:<pre>?activeworkbook.name
    Book2
    ?activeworkbook.fullname
    Book2
    ?application.GetOpenFilename
    False</pre>

    The *.xls extension only attaches once the file is saved. If you already have the save dialog box open, and an unsaved file, why do you need another path?
    Gre

  15. #15
    Lounger
    Join Date
    Apr 2002
    Location
    Los Angeles, California, USA
    Posts
    36
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Set Directory with msgBox (97sr2)

    In the project I am working on, the user has a series of files in a separate directory that will be processed for data extraction.

    Through the msgBox, the user points to the first file in the target directory (not the current directory) to start the data extraction.

    ActiveWorkbook.name, etc. does indeed Not get what I want because the msgBox return string is the FullName of a non-active workbook file. <img src=/S/frown.gif border=0 alt=frown width=15 height=15>

    This code is what I was looking for. <img src=/S/thumbup.gif border=0 alt=thumbup width=15 height=15>

    Thanks again to all.


    "Here, you are measured not by what you keep, but what you give"

Page 1 of 2 12 LastLast

Posting Permissions

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