How to consolidate Excel Worksheets in Python and VBA?

Below are the steps to consolidate Microsoft Excel worksheets in VBA :-

• Press Windows Logo Key ( ) + R to open Run Dialog box.
  
  
  
• Type Excel and Hit on OK button to open Microsoft Excel Application
  
  
  
• Press ALT + F11 (function key) to Open Visual Basic for Application Editor
  
  
  
• Select Insert from Menu bar then select Module from Pop up Window
  
  
  
• then Copy & Paste Below written code in the module
  
  
  
• Place Cursor any where in the code and click on run button (you can also press F5 to run this code).
  
  
  

VBA Code

Option Explicit

Public Sub ConsolidateSheets()
Dim wrkBkDump, wrkBkNewDump As Workbook
Dim LastRow As Long, i As Long, FileDump

Application.DisplayAlerts = False
Application.ScreenUpdating = False

FileDump = Application.GetOpenFilename("Microsoft Excel workbook(*.xl*),(*.xl*)", MultiSelect:=False)

If FileDump <> False Then
    Set wrkBkDump = Application.Workbooks.Open(FileDump, ReadOnly:=True)

    Set wrkBkNewDump = Excel.Workbooks.Add
    With wrkBkNewDump
        '.Sheets(Array(1, 2)).Delete
        .Sheets(1).Name = "Dump"
    End With
    
    For i = 1 To wrkBkDump.Sheets.Count
        wrkBkDump.Worksheets(i).AutoFilterMode = False
        wrkBkDump.Worksheets(i).Range("A3").CurrentRegion.Copy
        
        With wrkBkNewDump.Worksheets("dump")
            LastRow = .Range("B1048576").End(xlUp).Row
            
            If LastRow <> 1 Then
                .Range("A" & LastRow + 1).PasteSpecial (xlPasteAll)
            Else
                .Range("A1").PasteSpecial (xlPasteAll)
            End If
            Application.CutCopyMode = False
        End With

    Next

    wrkBkNewDump.SaveAs (Replace(FileDump, ".", "_Copy."))
End If

Application.DisplayAlerts = True
Application.ScreenUpdating = True

End Sub

consolidate Excel worksheets in python as well

Python Code

import win32com.client as win32


class xlconsolidator():
    xlapp = None
    xlenum = None

    def __init__(self):
        self.xlapp = win32.gencache.EnsureDispatch("Excel.Application")
        self.xlenum = win32.constants
        self.xlapp.Visible = True

    def getfile(self):
        return self.xlapp.GetOpenFilename("Microsoft Excel workbook(*.xl*),(*.xl*)", MultiSelect=False)

    def openworkbook(self, filename):
        wb = None
        try:
            wb = self.xlapp.Workbooks.Open(filename, ReadOnly=True)
        except Exception as ex:
            print ex.message

        return wb

    def createworkbook(self):
        return  self.xlapp.Workbooks.Add()

    def consolidatesheets(self, xlwbsource, xlwbtarget):

        xlwktarget = xlwbtarget.Worksheets(1)

        for xlwksheet in xlwbsource.Worksheets:
            lastrow = xlwktarget.Range("A" + str(xlwktarget.Rows.Count)).End(self.xlenum.xlUp).Row
            #Remove filter from sheet
            xlwksheet.AutoFilterMode = False
            #by assuming data is in structred format
            xlwksheet.Range("A1").CurrentRegion.Copy
            if lastrow != 1:
                xlwksheet.Range("A1").CurrentRegion.Copy(xlwktarget.Range("A" + lastrow + 1))
            else:
                xlwksheet.Range("A1").CurrentRegion.Copy(xlwktarget.Range("A1"))

            self.xlapp.CutCopyMode = False

        xlwbsource.Close(False)

    def saveworkbook(self, wb, newfilename):
        wb.SaveAs(newfilename)

    def __del__(self):
        self.xlapp.Quit()
        #self.xlapp.DisplayAlerts = True
        #self.xlapp.ScreenUpdating = True

if __name__ == '__main__':
    xl = xlconsolidator()
    filename = xl.getfile()
    wbsrc = xl.openworkbook(filename)
    if wbsrc != None:
        wbtarget =  xl.createworkbook()
        xl.consolidatesheets(wbsrc, wbtarget)
        xl.saveworkbook(wbtarget, 'c:\\consolidatted Workbook.xlsx')
        #filenamewithoutext =  os.path.pardir()