Dynamic Folder directory

[jabawalkie5000]

Hi All

I am trying to include coding in a macro that enables the user to enter the path in the control tab of my report.

The macro can then read that string and go to the desired folder to open the selected files

Can anyone help?

Many Thanks

 

[VoG]

Try like this

Sub GetFileName()
Dim fName As Variant
fName = Application.GetOpenFilename(FileFilter:="Excel Files (*.XLS), *.XLS", Title:="Select File To Be Opened")
If fName = False Then Exit Sub
Workbooks.Open Filename:=fName
End Sub

 

[Richard Schollar]

If you just want to select the folder rather than a file:

Dim strFolder As String

With Application.FileDialog(msoFileDialogFolderPicker)
  .AllowMultiSelect = False
  .InitialFileName = "C:\SomeFolder\"  'default start location
  .Title = "Please select a folder"
  If .Show = -1 Then strFolder = .SelectedItems(1) Else Exit Sub
End With

MsgBox "Path of folder is " & strFolder

 

[jabawalkie5000]

Many Thanks.

Does the tab name " control" not need to be included on here?

 

[VoG]

Isn't it easier for them to use a dialog to select a file or folder?

 

[jabawalkie5000]

Thanks again

Here is where I stand, I have a macro that has a static folder directory within, it goes to that folder, imports all the file names including extensions to a tab called "sheet1".

But I only want it to pick up .doc, .xls & .ppt files


Can you please tweak your macro to make the code dynamic and only import the 3 file types to my tab called sheet1?

Thanks

 

[jabawalkie5000]

Yes it is, I will incorporate that into the macro, so hopefully the user doesnt have to go cutting and pasting folder directory strings

Thanks, please see my new query above

Isn't it easier for them to use a dialog to select a file or folder?

 

[VoG]

Perhaps you mean something like

Sub ListFiles()
Dim MyFolder As String
Dim MyFile As String
Dim j As Integer
MyFolder = Sheets("control").Range("A1").Value
MyFile = Dir(MyFolder & "\*.*")
Do While MyFile <> ""
    If MyFile Like "*.doc" Or MyFile Like "*.xls" Or MyFile Like "*.ppt" Then
        j = j + 1
        Sheets("Sheet1").Cells(j, 1).Value = MyFile
    End If
    MyFile = Dir
Loop
End Sub

 

[jabawalkie5000]

Sorry to mess you around, could you now incorporate a dialogue box so that the user can just browse easily for the path and the rest of your macro works as per below?

Many Many Thanks

Perhaps you mean something like

Sub ListFiles()
Dim MyFolder As String
Dim MyFile As String
Dim j As Integer
MyFolder = Sheets("control").Range("A1").Value
MyFile = Dir(MyFolder & "\*.*")
Do While MyFile <> ""
    If MyFile Like "*.doc" Or MyFile Like "*.xls" Or MyFile Like "*.ppt" Then
        j = j + 1
        Sheets("Sheet1").Cells(j, 1).Value = MyFile
    End If
    MyFile = Dir
Loop
End Sub

 

[VoG]

Combining mine and Richard's

Sub ListFiles()
Dim MyFolder As String
Dim MyFile As String
Dim j As Integer
With Application.FileDialog(msoFileDialogFolderPicker)
    .AllowMultiSelect = False
    .InitialFileName = "C:\"  'default start location
    .Title = "Please select a folder"
    If .Show = -1 Then
        MyFolder = .SelectedItems(1)
    Else
        Exit Sub
    End If
End With
MyFile = Dir(MyFolder & "\*.*")
Do While MyFile <> ""
    If MyFile Like "*.doc" Or MyFile Like "*.xls" Or MyFile Like "*.ppt" Then
        j = j + 1
        Sheets("Sheet1").Cells(j, 1).Value = MyFile
    End If
    MyFile = Dir
Loop
End Sub