My Loopfolder Macro

S

simplymidori

This macro has been quite handy for me but I still have to manually
click "yes" to save changes on each file after it has prepped (see
below). What
can I add to eliminate from clicking "yes". 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
 
L

Larry Daugherty

There was no clear question but your answer is to look in Excel's VBA
Help for saving workbooks & options thereof

HTH
--
-Larry-
--

This macro has been quite handy for me but I still have to manually
click "yes" to save changes on each file after it has prepped (see
below). What
can I add to eliminate from clicking "yes". 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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top