--You can use both methods..The point is its better to have this as a
separate function so that your code doesnt look crowded and can be re-used.
It should have been as below...

--(Sorry both ssfWINDOWS is not specified which should be the initial
folder) The below would restrict the user to have access only to the
specified folder or within the folder specified..

Function GetSelectedFolder2() As String
Dim objShell As Object, objTemp As Object
Set objShell = CreateObject("Shell.Application")
Set objTemp = objShell.BrowseForFolder(0, "Select folder", 0, "d:\")
If Not objTemp Is Nothing Then GetSelectedFolder = objTemp.Self.Path
End Function


Or


Function GetSelectedFolder1(Optional strPath As String) As String
Dim objFldr As FileDialog
Set objFldr = Application.FileDialog(msoFileDialogFolderPicker)
With objFldr
.Title = "Select a folder"
.AllowMultiSelect = False
.InitialFileName = strPath
If .Show < -1 Then GetSelectedFolder = "": Exit Function
GetSelectedFolder = .SelectedItems(1)
End With
Set objFldr = Nothing
End Function

You can use at your convenience...

--
Jacob


"OssieMac" wrote:


My method posted below plus a couple of curiosity questions for Jacob.

Why use Shell when there is a built in FileDialog? I have seen it used a lot
and wonder if there is a specific advantage.

What is the variable ssfWINDOWS. It comes up as Compile error: Variable not
defined.

Anyway for the OP this is another option.

Sub LoopOWC()
Dim oFSO
Dim Folder As Object
Dim Files As Object
Dim file As Object
Dim fd As FileDialog
Dim myPath As Variant


Application.ScreenUpdating = False
Set fd = Application.FileDialog(msoFileDialogFolderPicker)

With fd

.AllowMultiSelect = False

'Edit following line to where you want to start
.InitialFileName = "c:\new_files"
If .Show Then
myPath = .SelectedItems(1)
Else
MsgBox "User cancelled" & vbLf & vbLf & _
"Processing terminated"
Exit Sub
End If

End With

Set fd = Nothing

Set oFSO = CreateObject("Scripting.FileSystemObject")

Set Folder = oFSO.GetFolder(myPath)

For Each file In Folder.Files
If file.Type Like "*Microsoft Excel*" Then
Workbooks.Open Filename:=file.Path

UploadData

ActiveWorkbook.Close SaveChanges:=True
End If
Next file

Set oFSO = Nothing
Set Folder = Nothing

Application.ScreenUpdating = True
End Sub

--
Regards,

OssieMac