Select directory not file

Will from London

Board Regular
Joined
Oct 14, 2004
Messages
220
Hi,

I checked the archive and my problem doesn't appear to have been covered before. I have a macro that copies all files with a certain extension from one location to another and changes the extension. It is powered by a User Form that asks for:

1. From directory: (eg G:\work)
2. To directory: (eg H:\random\new_tasks)
3. From extension: (eg .fre)
4. To extension: (eg .ljk)

Ideally I'd like to be able to add a Browse... button for each of the directory input boxes. Please note that the actual file extensions may be different each time so it just the directory browsing I need.

Any help would be much appreciated.
Cheers,

Will
 

Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!
Welcome to the board Will, :)

Here is a sample code for you. If you don't understand what the code says, fell free to ask me.

Code:
Option Explicit

'Place the followings in a Userform module

Private Sub UserForm_Initialize()
    Label1.Caption = "" 'Display From directory
    Label2.Caption = "" 'Display To directory
    TextBox1.Text = "" 'Textbox for From extension
    TextBox2.Text = "" 'Textbox for To extension
End Sub

Private Sub CommandButton1_Click()
'a Browse button for From directory
    Label1.Caption = GetFolder
End Sub

Private Sub CommandButton2_Click()
'a Browse button for To directory
    Label2.Caption = GetFolder
End Sub

Private Sub CommandButton3_Click()
'perform this code
    Dim FSO As Object
    Dim GFol As Object
    Dim SF As Object
    Dim F As Object
    Dim i As Long
    Dim pos As Long
    Dim ext As String
    Dim fn As String

    'Check labels and textboxes
    If Label1.Caption = "" Then GoTo Terminate
    If Label2.Caption = "" Then GoTo Terminate
    If Not TextBox1.Text Like ".*" Then GoTo Terminate
    If Not TextBox2.Text Like ".*" Then GoTo Terminate

    Set FSO = CreateObject("Scripting.FileSystemObject")
    Set GFol = FSO.GetFolder(Label1.Caption)

    i = 2
    If GFol.Files.Count > 0 Then
        For Each F In GFol.Files
            pos = InStrRev(F.Path, ".", -1, vbTextCompare)
            ext = Mid(F.Path, pos)
            If ext = TextBox1.Text Then
                pos = InStrRev(F.Name, ".", -1, vbTextCompare)
                fn = Left(F.Name, pos - 1)
                FileCopy F.Path, _
                         Label2.Caption & Application.PathSeparator & _
                         fn & TextBox2.Text
            End If
            i = i + 1
        Next
    End If
    Set FSO = Nothing
    MsgBox "Done"
    Exit Sub
Terminate:
    MsgBox "Please confirm if the inputed information is correct"
End Sub

Private Function GetFolder() As String
'common UDF for getting a folder name
    Dim ff As Object
    Set ff = CreateObject("Shell.Application"). _
             BrowseForFolder(0, "Please select a folder", 0, "c:\\")
    If Not ff Is Nothing Then
        GetFolder = ff.Items.Item.Path
    Else
        GetFolder = vbNullString
    End If
End Function
 
Upvote 0
Colo,

thanks very much for that. It works perfectly.

I'm also sure that the GetFolder function will be handy for a lot of other bits and pieces that I'm working on.

Regards,

Will
 
Upvote 0

Forum statistics

Threads
1,216,533
Messages
6,131,216
Members
449,636
Latest member
ajdebm

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top