Python Forum

I have to copy data from 6 workbooks and paste it into a master workbook. All the workbooks are located in a folder on my desktop: C:\Users\f6565\Desktop\data

The workbooks contain a sheet named 'Main Data', I have to open each workbook, go to sheet 'Main Data’, select columns range A to GJ starting from row 5 to row 'x' (end of the rows), then copy and paste the data range into the master worksheet. In the master worksheet (named MAIN DATA), I paste the data into Column A row 5 till the end and continue pasting/appending the data as I copy data from more workbooks. Eventually, the master workbook has the data in columns A row 5 to GJ from every workbook in one sheet.

The columns range A to GJ and starting from row 5 always remains constant in all the sheets (Main Data) of every workbook. Each workbook contains several sheets, but I am only interested in sheet ‘Main Data’. I have to repeat the same steps for the 6 workbooks and continue pasting/appending the data into master sheet. So, I was wondering if someone could please help me to create a python code for this?
I was able to do this with VB and it works. However I need to be able to do this with python entirely!. Here is my VBA code:

Sub CopyRange()
    Application.ScreenUpdating = False
    Dim wkbDest As Workbook
    Dim wkbSource As Workbook
    Set wkbDest = ThisWorkbook
    Dim LastRow As Long
    Const strPath As String = "C:\Users\f6565\Desktop\data\"
    ChDir strPath
    strExtension = Dir("*.xlsx*")
    Do While strExtension <> ""
        Set wkbSource = Workbooks.Open(strPath & strExtension)
        With wkbSource
            LastRow = .Sheets("Main Data").Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
            .Sheets("Main Data").Range("A5:GJ" & LastRow).Copy wkbDest.Sheets("MAIN DATA").Cells(Rows.Count, "A").End(xlUp).Offset(1, 0)
            .Close savechanges:=False
        End With
        strExtension = Dir
    Loop
    Application.ScreenUpdating = True
End Sub

I'm not so good with python and would really appreciate your help!

Please let me know if you require any clarification.

Many thanks!

pandas seems good choice - read data from files into dataframe with .read_excel method (defining sheet_name, usecols, skiprows) and write into Excel file using .to_excel method.

(Aug-21-2019, 11:14 AM)perfringo Wrote: [ -> ]pandas seems good choice - read data from files into dataframe with .read_excel method (defining sheet_name, usecols, skiprows) and write into Excel file using .to_excel method.

Pandas ruins the formatting and I want the format to still be present in the Master sheet