LoopFolder Macro Save as
 

Sorry for my duplicate post if any is showing. I am not seeing any
of my
post so I am attempting again...


This macro has been quite handy for me but I still have to manually
click "yes" to save changes for each file after data has been
prepped. What
can I add to eliminate the save as prompt. Thanks for any help in
advance.



Dim aryFiles
Dim oFSO
Sub LoopFolders_Cincinnati()
Dim i As Integer


Set oFSO = CreateObject("Scripting.FileSystemObject")


selectFiles "C:\Documents and Settings\TRACKERS\DATA "


Set oFSO = Nothing


End Sub


'--------------------------------------------------------------------------**-
Sub selectFiles(sPath)
'--------------------------------------------------------------------------**-
Dim Folder As Object
Dim Files As Object
Dim file As Object
Dim fldr


Set Folder = oFSO.GetFolder(sPath)


For Each fldr In Folder.Subfolders
selectFiles fldr.Path
Next fldr


For Each file In Folder.Files
If file.Type = "Microsoft Excel Comma Separated Values File"
Then
Workbooks.Open Filename:=file.Path
Rows("1:1").Select
Selection.Delete Shift:=xlUp
Cells.Select
Selection.Sort Key1:=Range("BW2"), Order1:=xlDescending,
Header:=xlGuess _
, OrderCustom:=1, MatchCase:=False,
Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal


ActiveWorkbook.Save
ActiveWorkbook.Close
End If
Next file


End Sub

LoopFolder Macro Save as
 

I think what you want is this near the End If statement in the
selectFiles(sPath) routine:

Application.DisplayAlerts = False
ActiveWorkbook.Save
ActiveWorkbook.Close
Application.DisplayAlerts = True
End If

"simplymidori" wrote:

Sorry for my duplicate post if any is showing. I am not seeing any
of my
post so I am attempting again...


This macro has been quite handy for me but I still have to manually
click "yes" to save changes for each file after data has been
prepped. What
can I add to eliminate the save as prompt. Thanks for any help in
advance.



Dim aryFiles
Dim oFSO
Sub LoopFolders_Cincinnati()
Dim i As Integer


Set oFSO = CreateObject("Scripting.FileSystemObject")


selectFiles "C:\Documents and Settings\TRACKERS\DATA "


Set oFSO = Nothing


End Sub


'--------------------------------------------------------------------------Â*Â*-
Sub selectFiles(sPath)
'--------------------------------------------------------------------------Â*Â*-
Dim Folder As Object
Dim Files As Object
Dim file As Object
Dim fldr


Set Folder = oFSO.GetFolder(sPath)


For Each fldr In Folder.Subfolders
selectFiles fldr.Path
Next fldr


For Each file In Folder.Files
If file.Type = "Microsoft Excel Comma Separated Values File"
Then
Workbooks.Open Filename:=file.Path
Rows("1:1").Select
Selection.Delete Shift:=xlUp
Cells.Select
Selection.Sort Key1:=Range("BW2"), Order1:=xlDescending,
Header:=xlGuess _
, OrderCustom:=1, MatchCase:=False,
Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal


ActiveWorkbook.Save
ActiveWorkbook.Close
End If
Next file


End Sub