run a macro on multiple workbooks 1

[vba317]

I am currently using Microsoft Excel 2002 SP3.
I have a directory that has about ten workbooks in it. Currently I use the same macro on all ten workbooks. What I would like to do is open one workbook and run the macro on the open one and than on the remaining nine and than save the results. Is this possible?

I copied this loop from the help files. The name of my macro is FormatData. I couldn't find any property that would run a macro. Any help would be appreciated.

    For Each w In Workbooks
    If w.Name <> ThisWorkbook.Name Then
        w.Close savechanges:=True
    End If
    Next w

 

[PHV]

Have a look at the Application.Run method.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[averilp]

Not sure if this helps... use this code to run code on all .xls files in a folder:

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 = "C:\Documents and Settings\My Documents\FOLDER"
        .FileType = msoFileTypeExcelWorkbooks
        '.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

                 wbResults.Close SaveChanges:=True
                 Next lCount
            End If
    End With

    On Error GoTo 0
    Application.ScreenUpdating = True
    Application.DisplayAlerts = True
    Application.EnableEvents = True
End Sub

 

[vba317]

This worked out great! Is there a command to minimize the open worksheet?

 

[PHV]

Is there a command to minimize the open worksheet
Typed, untested:

...
Set wbResults = Workbooks.Open(Filename:=.FoundFiles(lCount), UpdateLinks:=0)
ActiveWindow.WindowState = xlMinimized
...

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[vba317]

Thanks for all your help! I am having a problem running the macro thou. If I open a workbook in the folder that this macro is running I get an error message that the file is already open.
I thought what I would do to solve this problem was to store the file in the personal.xls file. But now I get an error message because I think the macro is now running on the emplty sheets in the personal.xls. Is there a way to solve this? Any help would be greatly appreciated.

 

[PHV]

What is YOUR actual code and which line is highlighted when in debug mode ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[SkipVought]

FYI,

For Each w In Workbooks

includes the personal.xls (hidden) workbook (if you have one) and you certainly do not want to run your macro and close this one.

Maybe

For Each w In Workbooks
  select case w.Name
     case "personal.xls",ThisWorkbook.Name
     case else
       'run the macro on everything else...

  end select

Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}

 

[vba317]

I have added the code recommended above. I am getting a compile error. I will admit that I am confused on the loop within the loops. I am trying here, and I humbly ask for more help. Here is my code:

Sub FormatSheets()
' Macro recorded 3/25/2009

Dim lCount As Long
Dim wbResults As Workbook
Dim wbCodeBook As Workbook
Dim R1 As Long
Dim R2 As String
Dim SR As Long
Dim ws As Worksheet

On Error Resume Next

Set wbCodeBook = ThisWorkbook

    With Application.FileSearch
        .NewSearch
         'Change path to suit
        .LookIn = "F:\Investments\Summary"
        .FileType = msoFileTypeExcelWorkbooks
        '.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)
                 ActiveWindow.WindowState = xlMinimized
   
    For Each ws In Worksheets
    Select Case ws.Name
     Case "personal.xls", ThisWorkbook.Name
     Case Else
       'run the macro on everything else...
    End Select
  
        With ws
        .Activate
         
        Select Case .Name
        
      Case "BulkQuotesXL Settings"
       Columns("A").Select
        With Selection
        .Columns.AutoFit
        .HorizontalAlignment = xlLeft
        .VerticalAlignment = xlCenter
        End With
       Columns("B:C").Select
        With Selection
        .NumberFormat = "mm/dd/yyyy"
        .Columns.AutoFit
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlCenter
        End With
        Columns("F").Select
         With Selection
         .NumberFormat = "text"
         .Columns.AutoFit
         .VerticalAlignment = xlCenter
         .HorizontalAlignment = xlRight
         End With
         Range("A3").Select
       
    Case Else
    Rows("2:2").Select
    ActiveWindow.FreezePanes = True
        Columns("A").Select
        With Selection
        .NumberFormat = "mm/dd/yyyy"
        .Columns.AutoFit
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlCenter
        End With
    Columns("B:E").Select
        With Selection
        .NumberFormat = "0.00"
        .Columns.AutoFit
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlCenter
        End With
        
    Columns("F").Select
        With Selection
        .NumberFormat = "#,##0"
        .Columns.AutoFit
        .VerticalAlignment = xlCenter
        .HorizontalAlignment = xlRight
         End With
         
    R1 = ActiveSheet.UsedRange.Rows.Count
    R2 = "F" & R1
    SR = R1 - 22

 [highlight]   ActiveWindow.ScrollRow = SR [/highlight]
' This is where the original error is
    Range(R2).Select
              
        End Select
     
        End With
        'Next

        wbResults.Close SaveChanges:=True
     
   [COLOR=#ff0000]   Next lCount[/color]
'This is where the complie error is

        End If
        
    End With

    On Error GoTo 0
    Application.ScreenUpdating = True
    Application.DisplayAlerts = True
    Application.EnableEvents = True

End Sub

 

[PHV]

'This is where the complie error is
Why have you commented out the Next instruction corresponding to For Each ws In Worksheets ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[vba317]

I'm not sure so I uncommented it.
After this I could compile the macro.

Now I get an runtime error '1004'
Unable to set the scrollrow property of the window class

When I hit the debug button this is the row that is highlighted

ActiveWindow.ScrollRow = SR [/color red]

When I hover over the highlighted row SR= False