thanks everyone, lots of good solutions provided. this one worked perfectly
for what I'm trying to do.

"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