Scripting changes to multiple excel workbooks

Question

I've trying to make large changes to a number of excel workbooks(over 20). Each workbook contains about 16 separate sheets, and I want to write a script that will loop through each workbook and the sheets contains inside and write/modify the cells that I need. I need to keep all string validation, macros, and formatting. All the workbooks are in 2007 format.

I've already looked at python excel libaries and PHPexcel, but macros, buttons, formulas, string validation, and formatting and not kept when the new workbook is written. Is there an easy way to do this, or will I have to open up each workbook individually and commit the changes. I'm trying to avoid creating a macro in VBscript and having to open up each workbook separately to commit the changes I need.

Answer 1

I avoid working with multiple workbooks like the plague it's a pain, if this is an ongoing requirement then I would suggest looking back to your workbook design and seeing if you can consolidate back to one workbook. I often see workbooks each saved month by month when they should have one workbook with one sheet with raw data where each row represents a month, then another sheet for display which looks up the raw data chosen by the user. Thats a very big generalisation and you could well be in a totally different situation.

If its a once off - and I know its not what you wanted but I think you would be best to loop through the workbooks using VBA. Something like (untested):

Excel 2003:

Sub AdjustMultipleFiles()
Dim lCount As Long
Dim wbLoopBook As Workbook
Dim wsLoopSheet As Worksheet

With Application
    .ScreenUpdating = False: .DisplayAlerts = False: .EnableEvents = False
End With

With Application.FileSearch
        .NewSearch
        '// Change path to suit
        .LookIn = "C:\MyDocuments"
        '// ALL Excel files
        .FileType = msoFileTypeExcelWorkbooks
        '// Uncomment if file naming convention needed
        '.Filename = "Book*.xls"

            '// Check for workbooks
            If .Execute > 0 Then
                '// Loop through all.
                For lCount = 1 To .FoundFiles.Count
                    '// Open Workbook x and Set a Workbook variable to it
                    Set wbLoopBook = Workbooks.Open(Filename:=.FoundFiles(lCount), UpdateLinks:=0)
                        '// Loop through all worksheets
                        For Each wsLoopSheet In wbLoopBook.Worksheets
                            '//Update your worksheets here...



                        Next wsLoopSheet
                    '// Close Workbook & Save
                    wbLoopBook.Close SaveChanges:=True
                    '// Release object variable
                    Set wbLoopBook = Nothing
            Next lCount
        End If

End With

With Application
    .ScreenUpdating = True: .DisplayAlerts = True: .EnableEvents = True
End With

End Sub

For EXCEL 2007+:

Sub AdjustMultipleFiles()
    Dim sFileName As String
    Dim wbLoopBook As Workbook
    Dim wsLoopSheet As Worksheet

    With Application
        .ScreenUpdating = False: .DisplayAlerts = False: .EnableEvents = False
    End With

    '// Change path to suit
    ChDir "C:\Documents"

    '// ALL Excel 2007 files
    sFileName = Dir("*.xlsx")

    Do While sFileName <> ""
        '// Open Workbook x and Set a Workbook variable to it
        Set wbLoopBook = Workbooks.Open(Filename:=sFileName, UpdateLinks:=0)
        '// Loop through all worksheets
        For Each wsLoopSheet In wbLoopBook.Worksheets
        '//Update your worksheets here...


        Next wsLoopSheet
        '// Close Workbook & Save
        wbLoopBook.Close SaveChanges:=True
        '// Release object variable
        Set wbLoopBook = Nothing
        '//Next File
        sFileName = Dir
        '//End Loop
    Loop

    With Application
        .ScreenUpdating = True: .DisplayAlerts = True: .EnableEvents = True
    End With

End Sub

Excel 2007 + (FileSystemObject - LateBinding)

Sub AdjustMultipleFiles()
    Dim wbLoopBook As Workbook
    Dim wsLoopSheet As Worksheet

    With Application
       .ScreenUpdating = False: .DisplayAlerts = False: .EnableEvents = False
    End With

    With CreateObject("Scripting.FileSystemObject")
        '// Change path to suit
        For Each File In .GetFolder("C:\Documents").Files
            '// ALL Excel 2007 files
            If .GetExtensionName(File) = "xlsx" Then
                '// Open Workbook x and Set a Workbook variable to it
                Set wbLoopBook = Workbooks.Open(Filename:=File.Path, UpdateLinks:=0)
                '// Loop through all worksheets
                For Each wsLoopSheet In wbLoopBook.Worksheets
                    '//Update your worksheets here...
                Next wsLoopSheet
                '// Close Workbook & Save
                wbLoopBook.Close SaveChanges:=True
                '// Release object variable
                Set wbLoopBook = Nothing
            End If
        Next File
    End With


    With Application
        .ScreenUpdating = True: .DisplayAlerts = True: .EnableEvents = True
    End With
End Sub

Answer 2

When I need to loop through files, I use some code from a thread on ozgrid which makes use of the Dir command, circumventing version issues while retaining the ability to filter filenames using wildcards.

Function FileList(fldr As String, Optional fltr As String = "*.*") As Variant
    Dim sTemp As String, sHldr As String
    If Right$(fldr, 1) <> "\" Then fldr = fldr & "\" 'append backslash if not already supplied
    sTemp = Dir(fldr & fltr)
    If sTemp = "" Then
        FileList = False
        Exit Function
    End If
    Do
        sHldr = Dir
        If sHldr = "" Then Exit Do
        sTemp = sTemp & "|" & sHldr 'ensures an array is returned
     Loop
    FileList = Split(sTemp, "|")
End Function

together with some code from me to make use of it:

Sub MySub()
    Dim vList As Variant
    Dim myWB As Workbook
    Dim targetDir As String
    targetDir = "C:\path\to\folder\"
    If Right$(targetDir, 1) <> "\" Then targetDir = targetDir & "\"
    vList = FileList(targetDir, "*.xl*")  'all file extensions starting with xl (xls, xlsx, xlsm, xla, xlsb, etc.)
    For n = LBound(vList) To UBound(vList)
        Set myWB = Workbooks.Open(targetDir & vList(n))
            'your code for each workbook here
        myWB.Close
    Next n
End Sub

Answer 3

You can also use the PyWin32 libraries to script this with Python using typical COM techniques. This lets you use Python to do your processing, and still save all of the extra parts of each workbook that other Python Excel libraries may not handle.