prompt for path to loop through files

[VBANovice]

I have the code below and it works fine but I would like it to prompt for the
file path as each month the path changes slightly.

Sub LoopOWC()
Dim oFSO
Dim Folder As Object
Dim Files As Object
Dim file As Object

Application.ScreenUpdating = False
Set oFSO = CreateObject("Scripting.FileSystemObject")

Set Folder = oFSO.GetFolder("c:\new_files\Jan")

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
Application.ScreenUpdating = True
End Sub

 

[Matthew Herbert]

VBANovice,

If you are looking for a way to get the folder path as per the user's
selection, then you'll need some API calls. If this is your intention, then
you'll find SHGetPathFromIDListA and SHBrowseForFolderA useful. Chip has
this laid out on his website at the following link if you don't want to use
MSDN to piece the API calls (and their associated structures) together
yourself: http://www.cpearson.com/Excel/BrowseFolder.aspx. If this is not
what you are looking for, then please be more specific as to how you want to
prompt the user and how you intend on using the user's result in your code.

Best,

Matthew Herbert

 

[Jacob Skaria]

Try out the below

Sub LoopOWC()
Dim oFSO
Dim Folder As Object
Dim Files As Object
Dim file As Object

Application.ScreenUpdating = False
Set oFSO = CreateObject("Scripting.FileSystemObject")

Set Folder = oFSO.GetFolder(GetSelectedFolder)

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
Application.ScreenUpdating = True
End Sub

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

 

[OssieMac]

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

 

[Jacob Skaria]

--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...

 

[VBANovice]

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