Workbooks array?

[John Davis]

Hello All:

I have the following code:

Sub ReplaceZeros()
Dim i As Long
Dim lr As Long

ChDir "I:\nafta\RFP\DataK\CM\Traffic\Excel"
Workbooks.Open Filename:="I:\nafta\RFP\DataK\CM\Traffic\Excel\AGFA_HC.xls"
Workbooks.Open Filename:="I:\nafta\RFP\DataK\CM\Traffic\Excel\AgfaParts1.xls"
Workbooks.Open Filename:="I:\nafta\RFP\DataK\CM\Traffic\Excel\AgfaParts2.xls"

lr = Cells(Rows.Count, 1).End(3).Row

For i = lr To 2 Step -1

    If Len(Range("A" & i)) = 8 Then
    
        Range("A" & i).Replace What:="000", Replacement:="", LookAt:=xlPart
        
    End If
    
Next i


End Sub

What I need is the actions in the procedure to run on each of the workbooks and then close with the changes. My mind is blank at the moment, any help or advice on how too achieve this would be appreciated.

 

[GTO]

By simple example, you can loop thru the wb's in the folder...

Sub exa()
Dim fil As Variant
Dim wb As Workbook
    
    For Each fil In CreateObject("Scripting.FileSystemObject").GetFolder(ThisWorkbook.Path & "\").Files
        If Mid(fil.Name, InStrRev(fil.Name, ".") + 1) = "xls" _
        And Not fil.Path = ThisWorkbook.FullName Then
            Set wb = Workbooks.Open(ThisWorkbook.Path & "\" & fil.Name)
            wb.Worksheets(1).Range("A1").Value = "Test"
            wb.Close True
        End If
    Next
End Sub

 

[Darren Bartrup]

Or maybe something like:

Sub ReplaceZeros()

    Dim aWorkbook As Variant
    Dim wrkBk As Workbook
    Dim x As Integer
    
    aWorkbook = Array("AGFA_HC.xls", "AgfaParts1.xls", "AgfaParts2.xls")
    
    For x = 0 To 2
        Set wrkBk = Workbooks.Open("I:\nafta\RFP\DataK\CM\Traffic\Excel\" And aWorkbook(x))
        With wrkBk
            .Worksheets("Sheet1").Columns(1).Replace What:="000", Replacement:="", LookAt:=xlPart, SearchOrder:=xlByColumns
            .Save
            .Close
        End With
    Next x
    
End Sub

 

[John Davis]

Or maybe something like:

Sub ReplaceZeros()

    Dim aWorkbook As Variant
    Dim wrkBk As Workbook
    Dim x As Integer
    
    aWorkbook = Array("AGFA_HC.xls", "AgfaParts1.xls", "AgfaParts2.xls")
    
    For x = 0 To 2
        Set wrkBk = Workbooks.Open("I:\nafta\RFP\DataK\CM\Traffic\Excel\" And aWorkbook(x))
        With wrkBk
            .Worksheets("Sheet1").Columns(1).Replace What:="000", Replacement:="", LookAt:=xlPart, SearchOrder:=xlByColumns
            .Save
            .Close
        End With
    Next x
    
End Sub

Thanks Darren and GTO for your replies. I modified Darren's version too suite my needs and it works perfectly. I needed the Len portions. Excellent, Thank You.

 

[Darren Bartrup]

Just noticed I wrote:

Set wrkBk = Workbooks.Open("I:\nafta\RFP\DataK\CM\Traffic\Excel\" And aWorkbook(x))

Should be:

Set wrkBk = Workbooks.Open("I:\nafta\RFP\DataK\CM\Traffic\Excel\" & aWorkbook(x))

 

[John Davis]

Just noticed I wrote:

Set wrkBk = Workbooks.Open("I:\nafta\RFP\DataK\CM\Traffic\Excel\" And aWorkbook(x))

Should be:

Set wrkBk = Workbooks.Open("I:\nafta\RFP\DataK\CM\Traffic\Excel\" & aWorkbook(x))

I caught that, not a problem.