Folder Picker

EMONTES149

New Member
Joined
Dec 20, 2016
Messages
8
I am having trouble with the following code. It appears to be a very simple answer that eludes me. There is a Master Director that certain files will be copied from to a folder of the users choosing. I used the folder selector dialogue box for the selection but no matter which folder the user selects it always goes to the desktop. I cannot figure it out. Can some one help me with this apparently easy solution that apparently is not easy for me. Here is the code:

Sub Test()
Dim R As Range
Dim SourcePath As String
Dim DestPath As String
Dim FName As String


'THIS IS THE ONE TO USE TO SELECT THE SPEC SECTIONS BASED ON THE CHECKLIST. THIS WORKS WELL AND WITH HIDDEN ROWS

'Setup source and dest path (Note: must have a trailing backslash!)
SourcePath = "O:\SYS2\RENOVATIONS\Design\ADMINISTRATION\Specifications\Master Specification Sections"
'DestPath = "C:\Users\EMONT3\Desktop\test" (this is what I am trying to avoid - hard coding to a path)


MsgBox "On Next Screen Select Project Folder"

With Application.FileDialog(msoFileDialogFolderPicker)
.AllowMultiSelect = False
.Show
On Error Resume Next
FolderName = .SelectedItems(1)
Err.Clear
On Error GoTo 0
End With


'Visit each used cell in column A
For Each R In Range("A3", Range("A" & Rows.Count).End(xlUp))

If R.EntireRow.Hidden = False Then
R.EntireRow.Offset(1, 0).Select

'Search the file with the file mask from the cell (Note: can contain wildcards like *.xls)
FName = Dir(SourcePath & R)

'Loop while files found
Do While FName <> ""

'Copy the file
FileCopy SourcePath & FName, DestPath & FName

'Search the next file
FName = Dir()

Loop
End If


Next


End Sub
 

Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!

John_w

MrExcel MVP
Joined
Oct 15, 2007
Messages
7,332
It's because you're using different variables:
Code:
[B]FolderName[/B] = .SelectedItems(1)
Code:
FileCopy SourcePath & FName, [B]DestPath [/B]& FName
Change the first line to
Code:
DestPath = .SelectedItems(1) & "\"
You should put Option Explicit at the top of the module and recompile to detect these kinds of programming errors.

PS please use CODE tags - the # icon in the message editor.
 
Last edited:

EMONTES149

New Member
Joined
Dec 20, 2016
Messages
8
Thank you very much......I am new to this so I am learning protocols. I am not a programmer, but after this am considering taking a course or two.
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,025
Messages
5,835,010
Members
430,332
Latest member
Charly_Moon

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
Top