Solved: Looping through all workbooks in a folder?

[tyndale2045]

Is it possible to loop through all the workbooks in a particular folder? Is this something that can be done through VBA?

I'm simply wanting to cycle through all of these workbooks, and switch the C column and B column. I know that after I have one of them opened, the following code will do the column switching:



With wb.Worksheets("Sheet1")
.Columns("C:C").Cut
.Columns("B:B").Insert Shift:=xlToRight
End With


I'm just hoping to avoid having to open every single one of them myself, and then run this macro.

Any ideas?

Thanks,
Jim

[JKwan]

Try this

Sub ProcessAll(Optional sPath As Variant)
Dim WB As Workbook, sFile As String

' This procedure will open all the files in a directory.
' Perhaps process them then close the files

Application.ScreenUpdating = False

If IsMissing(sPath) Then
sPath = "c:\DirectoryPath\"
sFile = Dir(sPath & "*.xls")
End If

'Loop through all .xls-Files in that path
Do While sFile <> ""
Set WB = Workbooks.Open(sPath & sFile)

'Do something with that Workbook, insert whatever you want to do here
Debug.Print WB.Name

'You can save it, if you like, here it's not saved
WB.Close False

sFile = Dir
Loop
Application.ScreenUpdating = True
End Sub

[tyndale2045]

Perfect! Thanks.

Jim

[mdmackillop]

Please remember to mark your threads Solved.

[tyndale2045]

Sorry, I didn't know about that. I'll make it a point to do that.

[EndIfWhat?]

Hello,
I use office 2007 and interested in using this macro. I've made some amendements that work, but they also make my computer sound like it's about to take off into outer space and then completely look up.

thanks in advance for any suggestions that will lessen the load that his macro has on my computer. I would conceivably run this with up to 50 files.

thanks,
Sherif


Sub ProcessAll(Optional sPath As Variant)
Dim WB As Workbook, sFile As String

' This procedure will open all the files in a directory.
' Perhaps process them then close the files

Application.ScreenUpdating = False

If IsMissing(sPath) Then
sPath = "H:\MailTest\"
sFile = Dir(sPath & "*.xls")
End If

'Loop through all .xls-Files in that path
Do While sFile <> ""
Set WB = Workbooks.Open(sPath & sFile)
Range("M65536").Select
Selection.End(xlUp).Select
Range(Selection, Selection.End(xlUp)).Select
Range(Selection, Selection.End(xlUp)).Select
Range(Selection, Selection.End(xlToLeft)).Select
Range(Selection, Selection.End(xlToLeft)).Select
Selection.Copy
Windows("Wamco Allocations.xls").Activate
Range("A65536").Select
Selection.End(xlUp).Select
ActiveCell.Offset(2, 0).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
'Do something with that Workbook, insert whatever you want to do here


'You can save it, if you like, here it's not saved
WB.Close False

sFile = Dir
Loop
Application.ScreenUpdating = True
End Sub