Macro to open a folder to which a user can choose a .txt file

[ClaraHam]

I have a macro:

Sub test()
Dim myFile As String
Dim YourFolderPath As Variant

YourFolderPath = "\\ESCPTOVSPIFS003\Shares5\ESC\SSB2\Revenue Management\40 Elm Documentation\Bank Reconciliations"
myFile = Application.GetOpenFilename("Text Files (*.txt),*.txt")
Workbooks.Open Filename:=myFile
End Sub

But it doesn't do what I want it to. It is actually the I want the macro to open a folder and allow the user to pick the appropriate text file. So far when I run it, it brings up a folder, but not to the path specified.

Please help! I have been looking at this forever it seems.

 

[igold]

Hi,

Welcome to the MrExcel Forum.

Does this get you any closer. The user will have to navigate to the correct folder. However on subsequent runs it should default to that folder.

Sub test()
    
    Dim FSO As Object
    Dim fileName As String
    
    
    Application.ScreenUpdating = False
    Set FSO = CreateObject("Scripting.FileSystemObject")
    With Application.FileDialog(msoFileDialogFilePicker)
        .Title = "Select A File!"
        .ButtonName = "Confirm"
            If .Show = -1 Then
                fileName = .SelectedItems(1)
            Else
                MsgBox "You didn't select a folder.", vbExclamation, "Folder Not Selected!"
                Exit Sub
            End If
    End With
    Workbooks.Open fileName
    
End Sub

I hope this helps

 

[ClaraHam]

Thanks igold,

This didn't work - a folder open, but not the specific folder location that I want it to. This procedure should do the following when the button is clicked:

Ask for a text file to be picked - I want a specific path to be chosen/opened when users are prompted to pick a folder. Currently it just opens to a default myDocuments.
Import the text file into excel - code is complete
format the data that was imported - code is complete

ClaraHam

 

[Fluff]

Not sure if this works with UNC paths but try this mod to igold' code

Sub test()
    
    Dim FSO As Object
    Dim fileName As String
    
    
    Application.ScreenUpdating = False
    Set FSO = CreateObject("Scripting.FileSystemObject")
    With Application.FileDialog(msoFileDialogFilePicker)
       [COLOR=#0000ff] .InitialFileName = "\\ESCPTOVSPIFS003\Shares5\ESC\SSB2\Revenue Management\40 Elm Documentation\Bank Reconciliations\"[/COLOR]
        .Title = "Select A File!"
        .ButtonName = "Confirm"
            If .Show = -1 Then
                fileName = .SelectedItems(1)
            Else
                MsgBox "You didn't select a folder.", vbExclamation, "Folder Not Selected!"
                Exit Sub
            End If
    End With
    Workbooks.Open fileName
    
End Sub

 

[igold]

Thanks igold,

This didn't work - a folder open, but not the specific folder location that I want it to. This procedure should do the following when the button is clicked:

Ask for a text file to be picked - I want a specific path to be chosen/opened when users are prompted to pick a folder. Currently it just opens to a default myDocuments.
Import the text file into excel - code is complete
format the data that was imported - code is complete

ClaraHam

Hi ClaraHam,

Thanks for the feedback. I hope that the code that Fluff provided for you better suits your needs. As I mentioned in my post the user would have to navigate to the correct path on their own.

igold

 

[daverunt]

This may be an option also.
You ChDir for your preferred location prior to using GetOpenFilename but you'll need a drive letter.

https://social.msdn.microsoft.com/F...the-directory-at-getopenfilename?forum=isvvba