File Loop (Run Macro, Save, Close)

[guybrown]

Hi all

Any assistance greatly appreciated here.

I am struggling to understand why this code isn't working. Any ideas?

Sub RunCodeOnAllXLSFiles()
Dim lCount As Long
Dim wbResults As Workbook
Dim wbCodeBook As Workbook
 
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Application.EnableEvents = False
On Error Resume Next
    Set wbCodeBook = ThisWorkbook
        With Application.FileSearch
            .NewSearch
            'Change path to suit
            .LookIn = "N:\1 Projects\2 Active\003711 RIO Tinto Rail Access RSA, WA\4-Working\Rating Output\POI"
            .FileType = msoFileTypeExcelWorkbooks
            'Optional filter with wildcard
            '.Filename = "Book*.xls"
                If .Execute > 0 Then 'Workbooks in folder
                    For lCount = 1 To .FoundFiles.Count 'Loop through all
                        'Open Workbook x and Set a Workbook variable to it
                        Set wbResults = Workbooks.Open(Filename:=.FoundFiles(lCount), UpdateLinks:=0)
 
                        'DO YOUR CODE HERE
                        Application.Run ("CreatePOI")
 
                        wbResults.Close SaveChanges:=False
                    Next lCount
                End If
        End With
On Error GoTo 0
Application.ScreenUpdating = True
Application.DisplayAlerts = True
Application.EnableEvents = True
End Sub

If it helps at all, the folder location is on a network and the macro I'm calling is located in the same file as this macro.

Cheers.

And the files in the folder that I am looping through have the CSV extension.

 

[Ruddles]

Which version of Excel are you using?

 

[guybrown]

which version of excel are you using?

2007

Is that the wrong answer? I'll have 2010 in a few weeks... maybe it'll work with that...

 

[guybrown]

Could it be something to do with the spaces in the folder location?

 

[guybrown]

Found the answer

http://www.excelforum.com/excel-programming/732030-run-macro-on-all-files-in-folder.html

Can't be done in 2007. Hats off to those that have to re-learn every year!

 

[Ruddles]

Give me a chance - I'm supposed to be working!

See this: http://msdn.microsoft.com/en-us/library/bb242669(v=office.12).aspx. By 'hidden', Microsoft means 'removed'!

 

[guybrown]

Give me a chance - I'm supposed to be working! QUOTE]

My wife thinks I'm crazy for finding Excel interesting...
...but you guys are on another level...
I appreciate your help... but don't get in trouble with your boss

 

[guybrown]

Ok. So I've found the following code which is written to work with Excel 2007.

[B]Sub CopySameSheetFrmWbs()
[/B]Dim wbOpen As Workbook
Dim wbNew As Workbook
[COLOR=#008000]'Change Path[/COLOR]
Const strPath As String = "C:\Excel\"
Dim strExtension As String

[COLOR=#008000]'Comment out the 3 lines below to debug[/COLOR]
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
On Error Resume Next

    ChDir strPath
[COLOR=#008000]'Change extension[/COLOR]
strExtension = Dir(strPath & "*.xls")
    
    Set wbNew = Workbooks.Add
[COLOR=#008000]'Change Path, Name and File Format[/COLOR]
wbNew.SaveAs Filename:="C:\Excel\TemplateCollation", FileFormat:=xlWorkbookNormal
    
        Do While strExtension <> ""
            Set wbOpen = Workbooks.Open(strPath & strExtension)
         
            With wbOpen
                .Sheets("Template").Copy After:=wbNew.Sheets(wbNew.Sheets.Count)
                wbNew.Sheets(wbNew.Sheets.Count).Name = wbNew.Sheets(wbNew.Sheets.Count).Cells(1, 1)
                .Close SaveChanges:=False
            End With
            
            strExtension = Dir
        Loop
        
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
On Error GoTo 0
[B]End Sub[/B]

</PRE>

This example does the following:
</PRE>

copy all Worksheets called "Template" into a new Workbook, then name the sheet using A1 of the same sheet
</PRE>

</PRE>

All I want to do while each file is open is run a macro called CreatePOI which is stored in the same file as this file loop macro.
</PRE>

I've tried replacing 'code while open' from the above macro for Application.Run CreatePOI and Call CreatePOI but neither of these seem to work.
</PRE>

Any ideas?
</PRE>

Regards, Guy
</PRE>