Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!

Categories

Welcome to the new platform of Programmer's Heaven! We apologize for the inconvenience caused, if you visited us from a broken link of the previous version. The main reason to move to a new platform is to provide more effective and collaborative experience to you all. Please feel free to experience the new platform and use its exciting features. Contact us for any issue that you need to get clarified. We are more than happy to help you.

Help with VB for excel

jdelkjdelk Posts: 14Member
I am trying to return the file path of a specified file without the file name attached to the end of the path.
I am really struggling with interpreting the useless help documentation in VB.
I want the macro to prompt the user for a directory using this code..

pname = Application.GetOpenFilename(, , "Please Select A File In The Prefered Working Directory", , False)

This allows the user to browse the directories for the files they want but I really only want the directory, not the file itself. Unfortunately, you must select a file that gets attached to the variable, pname in this case.

This is one of the examples the help documentation shows for using the path property, one of the things that I tried...

Sub ShowFileAccessInfo(filespec)
Dim fs, d, f, s
Set fs = CreateObject("Scripting.FileSystemObject")
Set f = fs.GetFile(filespec)
s = UCase(f.Path) & vbCrLf
s = s & "Created: " & f.DateCreated & vbCrLf
s = s & "Last Accessed: " & f.DateLastAccessed & vbCrLf
s = s & "Last Modified: " & f.DateLastModified
MsgBox s, 0, "File Access Info"
End Sub

Can someone tell me what filespec stands for?
I don't care about the extra info, like date modified and such.
I am only concerned with getting the path to the directory that the user has chosen, without the filename.


I have tied countless different methods to extract the directory only without the file because my code goes on to use the
.LookIn command to search the designated directory for as many files of a certain type there are, then it opens each file one by one and does stuff to them.
I just don't want to have to hard code the path to the directory in the macro script. I want the user to be able to browse for it when they run the script.

Is there an easy way to do this that I am completely overlooking??

Thanks,

Comments

  • PavlinIIPavlinII Posts: 404Member
    : I am trying to return the file path of a specified file without the file name attached to the end of the path.
    : I am really struggling with interpreting the useless help documentation in VB.
    : I want the macro to prompt the user for a directory using this code..
    :
    : pname = Application.GetOpenFilename(, , "Please Select A File In The Prefered Working Directory", , False)
    :
    : This allows the user to browse the directories for the files they want but I really only want the directory, not the file itself. Unfortunately, you must select a file that gets attached to the variable, pname in this case.
    :
    : This is one of the examples the help documentation shows for using the path property, one of the things that I tried...
    :
    : Sub ShowFileAccessInfo(filespec)
    : Dim fs, d, f, s
    : Set fs = CreateObject("Scripting.FileSystemObject")
    : Set f = fs.GetFile(filespec)
    : s = UCase(f.Path) & vbCrLf
    : s = s & "Created: " & f.DateCreated & vbCrLf
    : s = s & "Last Accessed: " & f.DateLastAccessed & vbCrLf
    : s = s & "Last Modified: " & f.DateLastModified
    : MsgBox s, 0, "File Access Info"
    : End Sub
    :
    : Can someone tell me what filespec stands for?
    : I don't care about the extra info, like date modified and such.
    : I am only concerned with getting the path to the directory that the user has chosen, without the filename.
    :
    :
    : I have tied countless different methods to extract the directory only without the file because my code goes on to use the
    : .LookIn command to search the designated directory for as many files of a certain type there are, then it opens each file one by one and does stuff to them.
    : I just don't want to have to hard code the path to the directory in the macro script. I want the user to be able to browse for it when they run the script.
    :
    : Is there an easy way to do this that I am completely overlooking??
    :
    : Thanks,
    :
    :
    Hi,
    try this
    [code]
    Dim fn As String
    fn = "C:SomePathSomeSubdirFileName.ext"
    fn = Mid(fn, 1, InStrRev(fn, ""))
    MsgBox fn [green]'Just show the result[/green]
    [/code]But if fn contains no backslash, fn will be vbNullString at the end..
    The filespec stands for filename and the FileSystemObject is usefull when you need additional info about some file/dir..

    Hope this helps

    [blue][b][italic][size=4]P[/size]avlin [size=4]II[/italic][/size][/b][/blue]

    [purple]Don't take life too seriously anyway you won't escape alive from it![/purple]


  • jdelkjdelk Posts: 14Member
    : : I am trying to return the file path of a specified file without the file name attached to the end of the path.
    : : I am really struggling with interpreting the useless help documentation in VB.
    : : I want the macro to prompt the user for a directory using this code..
    : :
    : : pname = Application.GetOpenFilename(, , "Please Select A File In The Prefered Working Directory", , False)
    : :
    : : This allows the user to browse the directories for the files they want but I really only want the directory, not the file itself. Unfortunately, you must select a file that gets attached to the variable, pname in this case.
    : :
    : : This is one of the examples the help documentation shows for using the path property, one of the things that I tried...
    : :
    : : Sub ShowFileAccessInfo(filespec)
    : : Dim fs, d, f, s
    : : Set fs = CreateObject("Scripting.FileSystemObject")
    : : Set f = fs.GetFile(filespec)
    : : s = UCase(f.Path) & vbCrLf
    : : s = s & "Created: " & f.DateCreated & vbCrLf
    : : s = s & "Last Accessed: " & f.DateLastAccessed & vbCrLf
    : : s = s & "Last Modified: " & f.DateLastModified
    : : MsgBox s, 0, "File Access Info"
    : : End Sub
    : :
    : : Can someone tell me what filespec stands for?
    : : I don't care about the extra info, like date modified and such.
    : : I am only concerned with getting the path to the directory that the user has chosen, without the filename.
    : :
    : :
    : : I have tied countless different methods to extract the directory only without the file because my code goes on to use the
    : : .LookIn command to search the designated directory for as many files of a certain type there are, then it opens each file one by one and does stuff to them.
    : : I just don't want to have to hard code the path to the directory in the macro script. I want the user to be able to browse for it when they run the script.
    : :
    : : Is there an easy way to do this that I am completely overlooking??
    : :
    : : Thanks,
    : :
    : :
    : Hi,
    : try this
    : [code]
    : Dim fn As String
    : fn = "C:SomePathSomeSubdirFileName.ext"
    : fn = Mid(fn, 1, InStrRev(fn, ""))
    : MsgBox fn [green]'Just show the result[/green]
    : [/code]But if fn contains no backslash, fn will be vbNullString at the end..
    : The filespec stands for filename and the FileSystemObject is usefull when you need additional info about some file/dir..
    :
    : Hope this helps
    :
    : [blue][b][italic][size=4]P[/size]avlin [size=4]II[/italic][/size][/b][/blue]
    :
    : [purple]Don't take life too seriously anyway you won't escape alive from it![/purple]
    :
    :
    :

    I think I get what you are trying to do but the way I am getting the path doesn't like to be considered a string.
    I am using
    pname = Application.GetOpenFilename(, , "Please Select A File In The Prefered Working Directory", )
    I this returns an object instead of a string.
    So when I declare pname to be a string it pukes...
    How can in convert the pname variable to a string?


  • PavlinIIPavlinII Posts: 404Member
    : : : I am trying to return the file path of a specified file without the file name attached to the end of the path.
    : : : I am really struggling with interpreting the useless help documentation in VB.
    : : : I want the macro to prompt the user for a directory using this code..
    : : :
    : : : pname = Application.GetOpenFilename(, , "Please Select A File In The Prefered Working Directory", , False)
    : : :
    : : : This allows the user to browse the directories for the files they want but I really only want the directory, not the file itself. Unfortunately, you must select a file that gets attached to the variable, pname in this case.
    : : :
    : : : This is one of the examples the help documentation shows for using the path property, one of the things that I tried...
    : : :
    : : : Sub ShowFileAccessInfo(filespec)
    : : : Dim fs, d, f, s
    : : : Set fs = CreateObject("Scripting.FileSystemObject")
    : : : Set f = fs.GetFile(filespec)
    : : : s = UCase(f.Path) & vbCrLf
    : : : s = s & "Created: " & f.DateCreated & vbCrLf
    : : : s = s & "Last Accessed: " & f.DateLastAccessed & vbCrLf
    : : : s = s & "Last Modified: " & f.DateLastModified
    : : : MsgBox s, 0, "File Access Info"
    : : : End Sub
    : : :
    : : : Can someone tell me what filespec stands for?
    : : : I don't care about the extra info, like date modified and such.
    : : : I am only concerned with getting the path to the directory that the user has chosen, without the filename.
    : : :
    : : :
    : : : I have tied countless different methods to extract the directory only without the file because my code goes on to use the
    : : : .LookIn command to search the designated directory for as many files of a certain type there are, then it opens each file one by one and does stuff to them.
    : : : I just don't want to have to hard code the path to the directory in the macro script. I want the user to be able to browse for it when they run the script.
    : : :
    : : : Is there an easy way to do this that I am completely overlooking??
    : : :
    : : : Thanks,
    : : :
    : : :
    : : Hi,
    : : try this
    : : [code]
    : : Dim fn As String
    : : fn = "C:SomePathSomeSubdirFileName.ext"
    : : fn = Mid(fn, 1, InStrRev(fn, ""))
    : : MsgBox fn [green]'Just show the result[/green]
    : : [/code]But if fn contains no backslash, fn will be vbNullString at the end..
    : : The filespec stands for filename and the FileSystemObject is usefull when you need additional info about some file/dir..
    : :
    : : Hope this helps
    : :
    : : [blue][b][italic][size=4]P[/size]avlin [size=4]II[/italic][/size][/b][/blue]
    : :
    : : [purple]Don't take life too seriously anyway you won't escape alive from it![/purple]
    : :
    : :
    : :
    :
    : I think I get what you are trying to do but the way I am getting the path doesn't like to be considered a string.
    : I am using
    : pname = Application.GetOpenFilename(, , "Please Select A File In The Prefered Working Directory", )
    : I this returns an object instead of a string.
    : So when I declare pname to be a string it pukes...
    : How can in convert the pname variable to a string?
    :
    :
    :
    In this case..
    [code]fn = Application.GetOpenFilename(Title:="Please Select A File In The Prefered Working Directory")[/code] returns selected file name (as string) or False as boolean, if Cancel clicked, to me..

    [code]Dim fn As String, fo As Variant
    fo = Application.GetOpenFilename(Title:="Please Select A File In The Prefered Working Directory")
    If fo = False Then
    MsgBox "Cancel selected"
    Else
    fn = CStr(fo)
    fn = Mid(fn, 1, InStrRev(fn, ""))
    MsgBox fn
    End If
    [/code]I cry when I see theese implicit type conversions of old VB.. ;)

    Hope this helps

    [blue][b][italic][size=4]P[/size]avlin [size=4]II[/italic][/size][/b][/blue]

    [purple]Don't take life too seriously anyway you won't escape alive from it![/purple]


Sign In or Register to comment.