Run Macro on all files in folder

Hello guys,

The macro below that I have adapted from the net was used to run a set of macro on all files located in a specified folder.

It used to work beautifully when I was still using Windows XP and Excel 2003. However, I have recently upgraded to Windows 7 64 bits and Excel 2007 and the macro no longer works.

It simply does not run the macro on all the files as specified in the .LookIn field.

I also read somewhere that the .FileSearch function no longer works in Excel 2007 too.

I would be greatly appreciated if someone could modify the code below to make it work on Windows 7 and Excel 2007 again.

Preferably, I would like to add another feature that allows me to specific which folder to run the macro on.

Many thanks all!

Temjin

Please Login or Register  to view this content.

Thanks a lot Stan, however, I am still having trouble executing the code. It doesn't do anything.

' DateTime : 09/05/2007 08:43
' Author : Roy Cox (royUK)'
' Purpose : Open all worksheets in a specific folder' Disclaimer; This code is offered as is with no guarantees. You may use it in your' projects but please leave this header intact.
'---------------------------------------------------------------------------------------
Option Explicit
Sub Open_All_Files()
Dim oWbk As Workbook
Dim sFil As String
Dim sPath As String

sPath = "C:\Documents and Settings\Roy Cox\My Documents\" 'location of files
ChDir sPath
sFil = Dir("*.xlsx") 'change or add formats
Do While sFil <> "" 'will start LOOP until all files in folder sPath have been looped through
Set oWbk = Workbooks.Open(sPath & "\" & sFil) 'opens the file

' do something
oWbk.Close True 'close the workbook, saving changes
sFil = Dir
Loop ' End of LOOP
End Sub

I'm pretty sure I've entered the path correctly, and the extension of the excel files there matches the format specified in the code.

I've done a bit of googling online as well and I'm trying to play with some of the codes in this thread.

http://social.msdn.microsoft.com/for...-03f7994369d6/

My preference would be for the ability to execute the "line of codes" through subfolders as well. But my first priority is to get it to work initially.

And I still don't know why it doesn't. More help would be appreciated for a newbie like me.

Many thanks!

Temjin

Would be great if someone could help me out on this. Really need to get this working.

Many thanks

Hi,

I had a similar issue with XL 2007 under Vista. You might want to check out the solution at http://www.excelforum.com/excel-prog...not-found.html. It might help if you run into a similar problem looping through the files.

abousetta

Hello,

I just tried Robert's code in 2007 and it works perfectly. Thanks Robert!

Hi

I'm having the same problem, I have a similar code to the one you wrote but my problem is that am not getting the code to copy all ranges to the workbook.
It goes through all files with a specifik name, but for each file i want to copy a specifik range from the closed workbook to my active workbook. How can I do it?