VBA GetOpenFileName

[shamas21]

Hi All

The following gets the file path of the file but Im trying to get the folder path only.

Dim sFile1 As Variant

sFile1 = Application.GetOpenFilename

How can I change this?

Thanks

 

[Mike Blackman]

Hi,

You can use .Path for this. Hope this helps;

ActiveWorkbook.Path

 

[shamas21]

Hi,

You can use .Path for this. Hope this helps;

ActiveWorkbook.Path

Hi

I dont want the activeworkbooks file path. Instead i want a dialog box for the user to select a folder and i want the file path for that folder.

Any suggestions?

 

[Mike Blackman]

Hi,

Do me a favour, run your code with this line added at the end;

Dim sFile1 As Variant

sFile1 = Application.GetOpenFilename

Msgbox sFile1

It does provide the full path....

 

[shamas21]

Hi,

Do me a favour, run your code with this line added at the end;

Dim sFile1 As Variant

sFile1 = Application.GetOpenFilename

Msgbox sFile1

It does provide the full path....

Yes it provides the full path...but im looking for the folder path.

i.e. it returns: C:\Documents and Settings\Desktop\TCF\Forecast.xls
I want it to be: C:\Documents and Settings\Desktop\TCF

Does that make sence?

Thanks

 

[Kenneth Hobson]

It is unclear just what you want. Did you want to browse for a folder and return the foldername or browse for a file and return the folder part? If the latter, this will do it.

Function FolderPart(sPath As String) As String
  FolderPart = Left(sPath, InStrRev(sPath, "\"))
End Function

If the former:

Sub Test_GetFolder()
  MsgBox GetFolder("Get My Folder", ThisWorkbook.path)
End Sub


Function GetFolder(Optional sTitle As String = "Select Folder", _
  Optional sInitialFilename As String)
  Dim myFolder As String
  With Application.FileDialog(msoFileDialogFolderPicker)
    If sInitialFilename = "" Then sInitialFilename = ThisWorkbook.path

    .initialFilename = sInitialFilename
    .Title = "Greetings"
    If .Show = -1 Then
      GetFolder = .SelectedItems(1)
      If Right(GetFolder, 1) <> "\" Then
            GetFolder = GetFolder & "\"
      End If
      Else: GetFolder = ""
    End If
  End With
End Function

 

[thedrinkingbeer]

i was looking for this kenneth and the first half of your reply was exactly what I needed...InStrRev! simple really...

 

[edevelasco]

the easy way to do it is:

fpath = dir(sFile1)