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
 

Some videos you may like

Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes

John_w

MrExcel MVP
Joined
Oct 15, 2007
Messages
6,619
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,818
Messages
5,598,280
Members
414,223
Latest member
Accountant2B

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