Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Loopfolders Macro
This macro has been quite handy for me but I still have to manually
click "yes" to save changes on each file after prepping data. 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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Loopfolders Macro
same response as given in your multiple posts in excel.misc. Don't you read
responses to your own posts? -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "simplymidori" wrote in message ups.com... This macro has been quite handy for me but I still have to manually click "yes" to save changes on each file after prepping data. 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macro Help Needed - Excel 2007 - Print Macro with Auto Sort | Excel Worksheet Functions | |||
My excel macro recorder no longer shows up when recording macro | Excel Discussion (Misc queries) | |||
My excel macro recorder no longer shows up when recording macro | Excel Discussion (Misc queries) | |||
using a cell value to control a counter inside a macro and displaying macro value | Excel Worksheet Functions | |||
Macro needed to Paste Values and prevent Macro operation | Excel Discussion (Misc queries) |