CODEWITHSUNDEEP
vbaexcelexcel-2010
user1960257
user1960257

Reputation: 47

Code not running on all opened Workbooks

Looping through opened Workbooks

Its a code to which runs through all opened workbooks but here's a problem that it runs about 10 to 12 times then stops. Can anybody gimme any idea...

Sub OpenAllWorkbooks()
    Set destWB = ActiveWorkbook

    Dim DestCell As Range

    FileNames = Application.GetOpenFilename( _
        filefilter:="Excel Files (*.csv*),*.csv*", _
        Title:="Select the workbooks to load.", MultiSelect:=True)

    If IsArray(FileNames) = False Then
        If FileNames = False Then
            Exit Sub
        End If
    End If

    For n = LBound(FileNames) To UBound(FileNames)
        Set wb = Workbooks.Open(Filename:=FileNames(n), ReadOnly:=True)
    Next n

    Dim cwb As Workbook

    For Each cwb In Workbooks
        'With Application

            'cwb.AcceptAllChanges
        'End With

        Call donemovementReport

        ActiveWorkbook.Close True
        ActiveWorkbook.Close False
    Next cwb
 End Sub

Upvotes: 0

Views: 258

Answers (2)

Peter L.
Peter L.

Reputation: 7304

The solution I use to cycle many files in 95% of cases - perhaps may be helpful:

Sub CSV_Cycling()

InputFolder = "D:\DOCUMENTS\"

    LoopFileNameExt = Dir(InputFolder & "*.csv")
    Do While LoopFileNameExt <> ""
    'Application.DisplayAlerts = False
    Application.Workbooks.Open (InputFolder & LoopFileNameExt)
    'Application.DisplayAlerts = True
    [..........YOUR CODE..........]
    LoopFileNameExt = Dir
    Loop 'Input Folder Files Cycling

End Sub

Uncomment Application.DisplayAlerts = strings to avoid any warnings - if required (however be careful - files will be opened using "default" options) and change source path, of course).

Upvotes: 0

Siddharth Rout
Siddharth Rout

Reputation: 149335

Why open all workbooks in one go? Open them one by one and then close them as your work is done. For example (Untested)

 Sub OpenAllWorkbooks()
    Dim wb As Workbook, thisWb As Workbook
    Dim DestCell As Range
    Dim FileNames As Variant
    Dim n As Long

    Set thisWb = ThisWorkbook

    FileNames = Application.GetOpenFilename( _
                filefilter:="Excel Files (*.csv*),*.csv*", _
                Title:="Select the workbooks to load.", MultiSelect:=True)

    If IsArray(FileNames) = False Then
        If FileNames = False Then
            Exit Sub
        End If
    End If

    For n = LBound(FileNames) To UBound(FileNames)
        Set wb = Workbooks.Open(Filename:=FileNames(n), ReadOnly:=True)

        Call donemovementReport

        wb.Close SaveChanges:=True
    Next n
 End Sub

Upvotes: 2

Related Questions